Search Results

Search found 22358 results on 895 pages for 'django raw query'.

Page 223/895 | < Previous Page | 219 220 221 222 223 224 225 226 227 228 229 230  | Next Page >

• Vmware Workstation 10 connect remote server (Debian, Guest-Windows XP) Does not allow raw disk access nor shared folders

  - by Alex

  The setup: Ubuntu with local Vmware Workstation 10 (everything works locally) Connects(File- Connect to Server) Debian server with the same Vmware Workstation 10 (Windows XP Guest) Debian setup does not allow raw disk access nor shared folders (most options does not exist) No shared folder No physical disk option I use root user for this machine. Default install. I've tried to add shared folder from command line - it does not work. How to enable shared folders or raw disk access? I have created new Windows 8 64 bit template from scratch - I cannot use physical HDD either, and no SharedFolder option. I think this is something about security policy of remote server.

  Read the article

• What is causing a Hibernate SQL query exception?

  - by Dark Star1

  Hi all and sorry in advance for this post but I've spent way too much time going around in circles so I'm hoping someone could shed a light on it here for me. I updated a webapp on Tomcat and I'm getting the following error which didn't exist on the previous version. Though I am quite confident that the part of code I modifed isn't to blame as I have tested the app on two different dev servers. The production server is configured thus: CentOS 5.4 virtual server with tomcat 5.5.23 running mysql 5.0.77. The two dev servers are: Windows XP SP2 running tomcat 5.5.23 with mysql 5.1.49 Mac OSX 10.6.6 Running tomcat 6 with mysql 5.1.51 The application was developed using struts (1.1 as far as I can gather) with hibernate 3 as the peristence layer. It only fails on the production server for some reason I can't fathom. I'd like to draw your attention to the java.sql.SQLException near the bottom. After some long searching I found this but because it was posted years ago (about 1 year before development started on this app I'm sure Hibernate has evolved from that version. as I can't find a way of implementing his solution. I use Eclipse Helios as an IDE. Thanks in advance for taking your time to read this, to all who manage to reply. javax.servlet.ServletException: org.hibernate.exception.SQLGrammarException: could not execute query at fr.company.action.login.LoginAction.execute(LoginAction.java:219) at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484) at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274) at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482) at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525) at javax.servlet.http.HttpServlet.service(HttpServlet.java:710) at javax.servlet.http.HttpServlet.service(HttpServlet.java:803) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at fr.company.util.EncodingFilter.doFilter(EncodingFilter.java:37) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:210) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:172) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:525) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:875) at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665) at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528) at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:113) at java.lang.Thread.run(Thread.java:636) javax.servlet.ServletException: org.hibernate.exception.SQLGrammarException: could not execute query at fr.company.action.login.LoginAction.execute(LoginAction.java:219) at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484) at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274) at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482) at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525) at javax.servlet.http.HttpServlet.service(HttpServlet.java:710) at javax.servlet.http.HttpServlet.service(HttpServlet.java:803) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at fr.company.util.EncodingFilter.doFilter(EncodingFilter.java:37) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:210) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:172) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:525) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:875) at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665) at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528) at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:113) at java.lang.Thread.run(Thread.java:636) java.lang.Exception: org.hibernate.exception.SQLGrammarException: could not execute query at fr.company.dao.GenericDAO.findOne(GenericDAO.java:204) at fr.company.dao.UserDAO.findOneUser(UserDAO.java:146) at fr.company.service.UserPeer.logUser(UserPeer.java:72) at fr.company.action.login.LoginAction.execute(LoginAction.java:127) at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484) at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274) at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482) at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525) at javax.servlet.http.HttpServlet.service(HttpServlet.java:710) at javax.servlet.http.HttpServlet.service(HttpServlet.java:803) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at fr.company.util.EncodingFilter.doFilter(EncodingFilter.java:37) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:210) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:172) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:525) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:875) at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665) at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528) at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:113) at java.lang.Thread.run(Thread.java:636) Caused by: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:2153) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029) at org.hibernate.loader.Loader.list(Loader.java:2024) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:369) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:300) at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:153) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1128) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79) at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:749) at fr.company.dao.GenericDAO.findOne(GenericDAO.java:198) ... 26 more Caused by: java.sql.SQLException: Unknown column 'user0_1_.poloSize' in 'field list' at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666) at com.mysql.jdbc.Connection.execSQL(Connection.java:2994) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:936) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1030) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139) at org.hibernate.loader.Loader.getResultSet(Loader.java:1669) at org.hibernate.loader.Loader.doQuery(Loader.java:662) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224) at org.hibernate.loader.Loader.doList(Loader.java:2150) ... 35 more

  Read the article

• Named query not known error trying to call a stored proc using Fluent NHibernate

  - by Hamman359

  I'm working on setting up NHibernate for a project and I have a few queries that, due to their complexity, we will be leaving as stored procedures. I'd like to be able to use NHibernate to call the sprocs, but have run into an error I can't figure out. Since I'm using Fluent NHibernate I'm using mixed mode mapping as recommended here. However, when I run the app I get a "Named query not known: AccountsGetSingle" exception and I can't figure out why. I think I might have a problem with my HBM mapping since I'm not very familiar with using them but I'm not sure. My NHibernate configuration code is: private ISessionFactory CreateSessionFactory() { return Fluently.Configure() .Database(MsSqlConfiguration.MsSql2005 .ConnectionString((conn => conn.FromConnectionStringWithKey("CIDB"))) .ShowSql()) .Mappings(m => { m.HbmMappings.AddFromAssemblyOf<Account>(); m.FluentMappings.AddFromAssemblyOf<Account>(); }) .BuildSessionFactory(); } My hbm.xml file is: <?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"> <sql-query name="AccountsGetSingle"> <return alias="Account" class="Core, Account"></return> exec AccountsGetSingle </sql-query> </hibernate-mapping> And the code where I am calling the sproc looks like this: public Account Get() { return _conversation.Session .GetNamedQuery("AccountsGetSingle") .UniqueResult<Account>(); } Any thoughts or ideas would be appreciated. Thanks.

  Read the article

• WCF Ria Services Error : Load operation failed for query 'GetTranslationProgress'

  - by Manoj

  Hello, I am using WCF Ria Services beta in my silverlight application. I have a long running task on the server which keeps writing its status to a database table. I have a "GetTranslationProgress" Ria Service Query method which queries the state to get the status. This query method is called continuously at a regular interval of 5 seconds until a status = Finished or Error is retrieved. In some cases if the task on the server is running for a long duration 2-3 minutes then I receives this error:- Load operation failed for query 'GetTranslationProgress'. The server did not provide a meaningful reply; this might be caused by a contract mismatch, a premature session shutdown or an internal server error. at System.Windows.Ria.OperationBase.Complete(Exception error) at System.Windows.Ria.LoadOperation.Complete(Exception error) at System.Windows.Ria.DomainContext.CompleteLoad(IAsyncResult asyncResult) at System.Windows.Ria.DomainContext.<c_DisplayClass17.b_13(Object ) The error occurs intermittently and I have no idea what could be causing this error. I have checked most of the forums and sites but I have not been able to find out any solution to this issue. Please help.

  Read the article

• Building a generic page "Query Analyzer" like in Asp.net with SMO

  - by Rodrigo

  Hello, I'm build a web page in ASP.net is supposed to work just like Query Analyzer. It has basically a textarea to input the sql command, and an Execute button. I'm using SMO to execute the command in the database, as follows: //Create object SMO Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(new Microsoft.SqlServer.Management.Common.ServerConnection(oConn)); //To execute the command server.ConnectionContext.ExecuteNonQuery(tbx_cmd.Text); //OR myDataset = server.ConnectionContext.ExecuteWithResults(tbx_cmd.Text); The problem is that the textarea command can contain anything, from a stored procedure (with GO's statements) and any select command that return resultsets, just like Query Analyzer. But, if I have GO statements, I only can perform the query using ExecuteNonQuery method. If I use the ExecuteWithResults method, it raises errors because of the GO statements. And if I want the resultsets to be available, of course I can only use the ExecuteWithResults method. Does anybody knows how can I do to execute the same command texts at the same time? Thank you!

  Read the article

• Generating the query plan takes 5 minutes, the query itself runs in milliseconds. What's up?

  - by TheImirOfGroofunkistan

  I have a fairly complex (or ugly depending on how you look at it) stored procedure running on SQL Server 2008. It bases a lot of the logic on a view that has a pk table and a fk table. The fk table is left joined to the pk table slightly more than 30 times (the fk table has a poor design - it uses name value pairs that I need to flatten out. Unfortunately, it's 3rd party and I cannot change it). Anyway, it had been running fine for weeks until I periodically noticed a run that would take 3-5 minutes. It turns out that this is the time it takes to generate the query plan. Once the query plan exists and is cached, the stored procedure itself runs very efficiently. Things run smoothly until there is a reason to regenerate and cache the query plan again. Has anyone seen this? Why does it take so long to generate the plan? Are there ways to make it come up with a plan faster?

  Read the article

• MySql - Get row number on select

  - by George

  Can I run a select statement and get the row number if the items are sorted? I have a table like this: mysql> describe orders; +-------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------------+------+-----+---------+----------------+ | orderID | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | itemID | bigint(20) unsigned | NO | | NULL | | +-------------+---------------------+------+-----+---------+----------------+ I can then run this query to get the number of orders by ID: SELECT itemID, COUNT(*) as ordercount FROM orders GROUP BY itemID ORDER BY ordercount DESC; This gives me a count of each itemID in the table like this: +--------+------------+ | itemID | ordercount | +--------+------------+ | 388 | 3 | | 234 | 2 | | 3432 | 1 | | 693 | 1 | | 3459 | 1 | +--------+------------+ I want to get the row number as well, so I could tell that itemID 388 is the first row, 234 is second, etc (essentially the ranking of the orders, not just a raw count). I know I can do this in java when I get the result set back, but I was wondering if there was a way to handle it purely in SQL.

  Read the article

• SQL MIN in Sub query causes huge delay

  - by Spencer

  I have a SQL query that I'm trying to debug. It works fine for small sets of data, but in large sets of data, this particular part of it causes it to take 45-50 seconds instead of being sub second in speed. This subquery is one of the select items in a larger query. I'm basically trying to figure out when the earliest work date is that fits in the same category as the current row we are looking at (from table dr) ISNULL(CONVERT(varchar(25),(SELECT MIN(drsd.DateWorked) FROM [TableName] drsd WHERE drsd.UserID = dr.UserID AND drsd.Val1 = dr.Val1 OR (((drsd.Val2 = dr.Val2 AND LEN(dr.Val2) > 0) AND (drsd.Val3 = dr.Val3 AND LEN(dr.Val3) > 0) AND (drsd.Val4 = dr.Val4 AND LEN(dr.Val4) > 0)) OR (drsd.Val5 = dr.Val5 AND LEN(dr.Val5) > 0) OR ((drsd.Val6 = dr.Val6 AND LEN(dr.Val6) > 0) AND (drsd.Val7 = dr.Val7 AND LEN(dr.Val2) > 0))))), '') AS WorkStartDate, This winds up executing a key lookup some 18 million times on a table that has 346,000 records. I've tried creating an index on it, but haven't had any success. Also, selecting a max value in this same query is sub second in time, as it doesn't have to execute very many times at all. Any suggestions of a different approach to try? Thanks!

  Read the article

• Oracle command hangs when using view for "WHERE x IN..." subquery

  - by Calvin Fisher

  I'm working on a web service that fetches data from an oracle data source in chunks and passes it back to an indexing/search tool in XML format. I'm the C#/.NET guy, and am kind of fuzzy on parts of Oracle. Our Oracle team gave us the following script to run, and it works well: SELECT ROWID, [columns] FROM [table] WHERE ROWID IN ( SELECT ROWID FROM ( SELECT ROWID FROM [table] WHERE ROWID > '[previous_batch_last_rowid]' ORDER BY ROWID ) WHERE ROWNUM <= 10000 ) ORDER BY ROWID 10,000 rows is an arbitrary but reasonable chunk size and ROWID is sufficiently unique for our purposes to use as a UID since each indexing run hits only one table at a time. Bracketed values are filled in programmatically by the web service. Now we're going to start adding views to the indexing, each of which will union a few separate tables. Since ROWID would no longer function as a unique identifier, they added a column to the views (VIEW_UNIQUE_ID) that concatenates the ROWIDs from the component tables to construct a UID for each union. But this script does not work, even though it follows the same form as the previous one: SELECT VIEW_UNIQUE_ID, [columns] FROM [view] WHERE VIEW_UNIQUE_ID IN ( SELECT VIEW_UNIQUE_ID FROM ( SELECT VIEW_UNIQUE_ID FROM [view] WHERE ROWID > '[previous_batch_last_view_unique_id]' ORDER BY VIEW_UNIQUE_ID ) WHERE ROWNUM <= 10000 ) ORDER BY VIEW_UNIQUE_ID It hangs indefinitely with no response from the Oracle server. I've waited 20+ minutes and the SQLTools dialog box indicating a running query remains the same, with no progress or updates. I've tested each subquery independently and each works fine and takes a very short amount of time (<= 1 second), so the view itself is sound. But as soon as the inner two SELECT queries are added with "WHERE VIEW_UNIQUE_ID IN...", it hangs. Why doesn't this query work for views? In what important way are they not interchangeable here?

  Read the article

• Date query with Hibernate on Timestamp Column in PostgreSQL

  - by Shashikant Kore

  A table has timestamp column. A sample value in that could be 2010-03-30 13:42:42. With Hibernate, I am doing a range query Restrictions.between("column-name", fromDate, toDate). The Hibernate mapping for this column is as follows. <property name="orderTimestamp" column="order_timestamp" type="java.util.Date" /> Let's say, I want to find out all the records that have the date 30th March 2010 and 31st March 2010. A range query on this field is done as follows. Date fromDate = new SimpleDateFormat("yyyy-MM-dd").parse("2010-03-30"); Date toDate = new SimpleDateFormat("yyyy-MM-dd").parse("2008-03-31"); Expression.between("orderTimestamp", fromDate, toDate); This doesn't work. The query is converted to respective timestamps as "2010-03-30 00:00:00" and "2010-03-31 00:00:00". So, all the records for the 31st March 2010 are ignored. A simple solution to this problem could be to have the end date as "2010-03-31 23:59:59." But, I would like to know if there is way to match only the date part of the timestamp column. Also, is Expression.between() inclusive of both limits? Documentation doesn't throw any light on this.

  Read the article

• CQRS - The query side

  - by mattcodes

  A lot of the blogsphere articles related to CQRS (command query repsonsibility) seperation seem to imply that all screens/viewmodels are flat. e.g. Name, Age, Location Of Birth etc.. and thus the suggestion that implementation wise we stick them into fast read source etc.. single table per view mySQL etc.. and pull them out with something like primitive SqlDataReader, kick that nasty nhibernate ORM etc.. However, whilst I agree that domain models dont mapped well to most screens, many of the screens that I work with are more dimensional, and Im sure this is pretty common in LOB apps. So my question is how are people handling screen where by for example it displays a summary of customer details and then a list of their orders with a [more detail] link etc.... I thought about keeping with the straight forward SQL query to the Query Database breaking off the outer join so can build a suitable ViewModel to View but it seems like overkill? Alternatively (this is starting to feel yuck) in CustomerSummaryView table have a text/big (whatever the type is in your DB) column called Orders, and the columns for the Order summary screen grid are seperated by , and rows by |. Even with XML datatype it still feeel dirty. Any thoughts on an optimal practice?

  Read the article

• Rails add a param to the end of a search query

  - by bob

  Hello, I am trying to implement a sort on the returned results of a search with rails. Filter by: 'recent' % | 'lowest_price' % | 'highest_price' % here are my links. I would like to add a filter to the end of my search query. So for example, I submit a search and I get back results and in my url I have http://localhost:3000/junks?search=handbag&condition=&category=&main_submit=Go! I would then like the user to be able to click one of the above links and have the page come back with a new query (i have this set up in the controller like such) con_id = params[:condition] if params[:category].nil? || params[:category].empty? cat_id = params[:category] results = Junk.search params[:search], :with => { :category_id => cat_id, :condition_id => con_id } case params[:filter] when "lowest_price" @junks = results.lowest_price.paginate :page => params[:page], :per_page => 12 when "highest_price" @junks = results.highest_price.paginate :page => params[:page], :per_page => 12 else @junks = results.paginate :page => params[:page], :per_page => 12 end else I would like the user to be able to click on of the above links and append a filter param to the end of the search query so that my controller can pick it up and call the correct database queries as seen in the case statement above. I'm guessing the url will look like this http://localhost:3000/junks?search=handbag&condition=&category=&main_submit=Go!&filter="lowest_price" How can I do this? Is there a better way?

  Read the article

• Complex SQL query... names of returned variables

  - by Andrew P.

  Excuse me for what I'm sure is an elementary question for most of you, but I have an issue with table columns from separate tables having the same name as one another, and trying to select from both tables in the same query. Okay, so this is my code: $q_value = $mdb2->quote($_POST['query']); $field = $_POST['field']; $sql = "SELECT m.*, l.name FROM memberlist m, mail_lists l WHERE m.$field=$q_value AND l.id = m.list ORDER BY m.id"; $l_list = $mdb2->queryAll($sql, '', 'MDB2_FETCHMODE_ASSOC'); The table memberlist has the following columns: id, email, list, sex, name and the table mail_lists has the following columns: id, name After running the query, I later loop through the results with a foreach like so: foreach ($l_list as $l){ //blahblah } The problem is that the column 'name' in mail_lists refers to the names of the list, while the column 'name' in memberlist refers to the name of the member. When I later access $l-name (within the foreach), will I get m.name, or l.name? Furthermore, how do I get access to the other? Or will I just have to do two separate queries?

  Read the article

• MySQL stuck on "using filesort" when doing an "order by"

  - by noko

  I can't seem to get my query to stop using filesort. This is my query: SELECT s.`pilot`, p.`name`, s.`sector`, s.`hull` FROM `pilots` p LEFT JOIN `ships` s ON ( (s.`game` = p.`game`) AND (s.`pilot` = p.`id`) ) WHERE p.`game` = 1 AND p.`id` <> 2 AND s.`sector` = 43 AND s.`hull` > 0 ORDER BY p.`last_move` DESC Table structures: CREATE TABLE IF NOT EXISTS `pilots` ( `id` mediumint(5) unsigned NOT NULL AUTO_INCREMENT, `game` tinyint(3) unsigned NOT NULL DEFAULT '0', `last_move` int(10) NOT NULL DEFAULT '0', UNIQUE KEY `id` (`id`), KEY `last_move` (`last_move`), KEY `game_id_lastmove` (`game`,`id`,`last_move`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ; CREATE TABLE IF NOT EXISTS `ships` ( `id` mediumint(5) unsigned NOT NULL AUTO_INCREMENT, `game` tinyint(3) unsigned NOT NULL DEFAULT '0', `pilot` mediumint(5) unsigned NOT NULL DEFAULT '0', `sector` smallint(5) unsigned NOT NULL DEFAULT '0', `hull` smallint(4) unsigned NOT NULL DEFAULT '50', UNIQUE KEY `id` (`id`), KEY `game` (`game`), KEY `pilot` (`pilot`), KEY `sector` (`sector`), KEY `hull` (`hull`), KEY `game_2` (`game`,`pilot`,`sector`,`hull`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ; The explain: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE p ref id,game_id_lastmove game_id_lastmove 1 const 7 Using where; Using filesort 1 SIMPLE s ref game,pilot,sector... game_2 6 const,fightclub_alpha.p.id,const 1 Using where; Using index edit: I cut some of the unnecessary pieces out of my queries/table structure. Anybody have any ideas?

  Read the article

• Linq Query Performance , comparing Compiled query vs Non-Compiled.

  - by AG.

  Hello Guys, I was wondering if i extract the common where clause query into a common expression would it make my query much faster, if i have say something like 10 linq queries on a collection with exact same 1st part of the where clause. I have done a small example to explain a bit more . public class Person { public string First { get; set; } public string Last { get; set; } public int Age { get; set; } public String Born { get; set; } public string Living { get; set; } } public sealed class PersonDetails : List<Person> { } PersonDetails d = new PersonDetails(); d.Add(new Person() {Age = 29, Born = "Timbuk Tu", First = "Joe", Last = "Bloggs", Living = "London"}); d.Add(new Person() { Age = 29, Born = "Timbuk Tu", First = "Foo", Last = "Bar", Living = "NewYork" }); Expression<Func<Person, bool>> exp = (a) => a.Age == 29; Func<Person, bool> commonQuery = exp.Compile(); var lx = from y in d where commonQuery.Invoke(y) && y.Living == "London" select y; var bx = from y in d where y.Age == 29 && y.Living == "NewYork" select y; Console.WriteLine("All Details {0}, {1}, {2}, {3}, {4}", lx.Single().Age, lx.Single().First , lx.Single().Last, lx.Single().Living, lx.Single().Born ); Console.WriteLine("All Details {0}, {1}, {2}, {3}, {4}", bx.Single().Age, bx.Single().First, bx.Single().Last, bx.Single().Living, bx.Single().Born); So can some of the guru's here give me some advice if it would be a good practice to write query like var lx = "Linq Expression " or var bx = "Linq Expression" ? Any inputs would be highly appreciated. Thanks, AG

  Read the article

• Generating the SQL query plan takes 5 minutes, the query itself runs in milliseconds. What's up?

  - by TheImirOfGroofunkistan

  I have a fairly complex (or ugly depending on how you look at it) stored procedure running on SQL Server 2008. It bases a lot of the logic on a view that has a pk table and a fk table. The fk table is left joined to the pk table slightly more than 30 times (the fk table has a poor design - it uses name value pairs that I need to flatten out. Unfortunately, it's 3rd party and I cannot change it). Anyway, it had been running fine for weeks until I periodically noticed a run that would take 3-5 minutes. It turns out that this is the time it takes to generate the query plan. Once the query plan exists and is cached, the stored procedure itself runs very efficiently. Things run smoothly until there is a reason to regenerate and cache the query plan again. Has anyone seen this? Why does it take so long to generate the plan? Are there ways to make it come up with a plan faster?

  Read the article

• Is possible to reuse subqueries?

  - by Gothmog

  Hello, I'm having some problems trying to perform a query. I have two tables, one with elements information, and another one with records related with the elements of the first table. The idea is to get in the same row the element information plus several records information. Structure could be explain like this: table [ id, name ] [1, '1'], [2, '2'] table2 [ id, type, value ] [1, 1, '2009-12-02'] [1, 2, '2010-01-03'] [1, 4, '2010-01-03'] [2, 1, '2010-01-02'] [2, 2, '2010-01-02'] [2, 2, '2010-01-03'] [2, 3, '2010-01-07'] [2, 4, '2010-01-07'] And this is want I would like to achieve: result [id, name, Column1, Column2, Column3, Column4] [1, '1', '2009-12-02', '2010-01-03', , '2010-01-03'] [2, '2', '2010-01-02', '2010-01-02', '2010-01-07', '2010-01-07'] The following query gets the proper result, but it seems to me extremely inefficient, having to iterate table2 for each column. Would be possible in anyway to do a subquery and reuse it? SELECT a.id, a.name, (select min(value) from table2 t where t.id = subquery.id and t.type = 1 group by t.type) as Column1, (select min(value) from table2 t where t.id = subquery.id and t.type = 2 group by t.type) as Column2, (select min(value) from table2 t where t.id = subquery.id and t.type = 3 group by t.type) as Column3, (select min(value) from table2 t where t.id = subquery.id and t.type = 4 group by t.type) as Column4 FROM (SELECT distinct id FROM table2 t WHERE (t.type in (1, 2, 3, 4)) AND t.value between '2010-01-01' and '2010-01-07') as subquery LEFT JOIN table a ON a.id = subquery.id

  Read the article

• SqlDataAdaptor why get a different query

  - by Murat

  Hi All, I have a database reader class. But i want to use this class, sometimes SqlDataAdaptor.Fill() method get another query to Datatable. I look at Query in SqlCommand Instance and my query is correct but returning values from different table? What is the problem? This My Code public static DataTable GetLatestRecords(string TableName, string FilterFieldName, int RecordCount, params DBFieldAndValue[] FilterFields) { DataTable DT = new DataTable(); Type ClassType = typeof(T); string SelectString = string.Format("SELECT TOP ({0}) * FROM {1}", RecordCount, TableName); if (FilterFields.Length > 0) { SelectString += " WHERE 1 = 1 "; for (int index = 0; index < FilterFields.Length; index++) SelectString += FilterFields[index].ToString(index); } SelectString += string.Format(" ORDER BY {0} DESC", FilterFieldName); try { SqlConnection Connection = GetConnection<T>(); SqlCommand Command = new SqlCommand(SelectString, Connection); for (int index = 0; index < FilterFields.Length; index++) Command.Parameters.AddWithValue("@" + FilterFields[index].Field + "_" + index, FilterFields[index].Value); if (Connection.State == ConnectionState.Closed) Connection.Open(); Command.CommandText = SelectString; SqlDataAdapter DA = new SqlDataAdapter(Command); DT.Dispose(); DT = new DataTable(); DA.Fill(DT); if (Connection.State == ConnectionState.Open) Connection.Close(); } catch (Exception ex) { WriteLogStatic(ex.Message); throw new Exception(@"Veritabani islemleri sirasinda hata olustu!\nAyrintilar 'C:\Temp' dizini altindadir.\n" + ex.Message); } return DT; }

  Read the article

• Media query from a specific folder

  - by sensei

  I would like to understand how I can use a cursor to jpg files in a folder specified in the sdcard. I'm trying to select with a cursor the jpg files in a specific folder, and I tried this: This is the code: public static Uri getRandomImage(ContentResolver resolver) { String[] projection = new String[] { BaseColumns._ID, }; String folder = "/sdcard/DCIM/Wallpaper/"; folder = folder + "%"; Uri uri = Media.EXTERNAL_CONTENT_URI; String[] whereArgs = new String[]{folder}; Cursor cursor = resolver.query(uri, projection, null, whereArgs, MediaColumns._ID); if (cursor == null || cursor.getCount() <= 0) { return null; } cursor.moveToPosition(new Random().nextInt(cursor.getCount())); return Uri.withAppendedPath(uri, cursor.getString(0)); } but this code gives me error here is the logcat: E/AndroidRuntime(11986): FATAL EXCEPTION: main E/AndroidRuntime(11986): android.database.sqlite.SQLiteException: bind or column index out of range: handle 0x26a490 E/AndroidRuntime(11986): at android.database.DatabaseUtils.readExceptionFromParcel(DatabaseUtils.java:158) E/AndroidRuntime(11986): at android.database.DatabaseUtils.readExceptionFromParcel(DatabaseUtils.java:114) E/AndroidRuntime(11986): at android.content.ContentProviderProxy.bulkQueryInternal(ContentProviderNative.java:330) E/AndroidRuntime(11986): at android.content.ContentProviderProxy.query(ContentProviderNative.java:366) E/AndroidRuntime(11986): at android.content.ContentResolver.query(ContentResolver.java:245) E/AndroidRuntime(11986): at it.bisemanuDEV.slidepuzzle.SelectImagePreference.getRandomImage(SelectImagePreference.java:126) E/AndroidRuntime(11986): at it.bisemanuDEV.slidepuzzle.TileView.newGame(TileView.java:156) E/AndroidRuntime(11986): at it.bisemanuDEV.slidepuzzle.SlidePuzzleActivity.onOptionsItemSelected(SlidePuzzleActivity.java:377) E/AndroidRuntime(11986): at android.app.Activity.onMenuItemSelected(Activity.java:2762) E/AndroidRuntime(11986): at com.android.internal.policy.impl.PhoneWindow.onMenuItemSelected(PhoneWindow.java:730) E/AndroidRuntime(11986): at com.android.internal.view.menu.MenuItemImpl.invoke(MenuItemImpl.java:143) E/AndroidRuntime(11986): at com.android.internal.view.menu.MenuBuilder.performItemAction(MenuBuilder.java:855) E/AndroidRuntime(11986): at com.android.internal.view.menu.IconMenuView.invokeItem(IconMenuView.java:532) E/AndroidRuntime(11986): at com.android.internal.view.menu.IconMenuItemView.performClick(IconMenuItemView.java:122) E/AndroidRuntime(11986): at android.view.View$PerformClick.run(View.java:8819) E/AndroidRuntime(11986): at android.os.Handler.handleCallback(Handler.java:603) E/AndroidRuntime(11986): at android.os.Handler.dispatchMessage(Handler.java:92) E/AndroidRuntime(11986): at android.os.Looper.loop(Looper.java:123) E/AndroidRuntime(11986): at android.app.ActivityThread.main(ActivityThread.java:4627) E/AndroidRuntime(11986): at java.lang.reflect.Method.invokeNative(Native Method) E/AndroidRuntime(11986): at java.lang.reflect.Method.invoke(Method.java:521) E/AndroidRuntime(11986): at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868) E/AndroidRuntime(11986): at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626) E/AndroidRuntime(11986): at dalvik.system.NativeStart.main(Native Method)

  Read the article

• CQRS - The query side

  - by mattcodes

  A lot of the blogsphere articles related to CQRS (command query repsonsibility) seperation seem to imply that all screens/viewmodels are flat. e.g. Name, Age, Location Of Birth etc.. and thus the suggestion that implementation wise we stick them into fast read source etc.. single table per view mySQL etc.. and pull them out with something like primitive SqlDataReader, kick that nasty nhibernate ORM etc.. However, whilst I agree that domain models dont mapped well to most screens, many of the screens that I work with are more dimensional, and Im sure this is pretty common in LOB apps. So my question is how are people handling screen where by for example it displays a summary of customer details and then a list of their orders with a [more detail] link etc.... I thought about keeping with the straight forward SQL query to the Query Database breaking off the outer join so can build a suitable ViewModel to View but it seems like overkill? Alternatively (this is starting to feel yuck) in CustomerSummaryView table have a text/big (whatever the type is in your DB) column called Orders, and the columns for the Order summary screen grid are seperated by , and rows by |. Even with XML datatype it still feeel dirty. Any thoughts on an optimal practice?

  Read the article

• Place Query Results into Array then Implode?

  - by jason

  Basically I pull an Id from table1, use that id to find a site id in table2, then need to use the site ids in an array, implode, and query table3 for site names. I cannot implode the array correctly first I got an error, then used a while loop. With the while loop the output simply says: Array $mysqli = mysqli_connect("server", "login", "pass", "db"); $sql = "SELECT MarketID FROM marketdates WHERE Date = '2010-04-04 00:00:00' AND VenueID = '2'"; $result = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli)); $dates_id = mysqli_fetch_assoc ( $result ); $comma_separated = implode(",", $dates_id); echo $comma_separated; //This Returns 79, which is correct. $sql = "SELECT SIteID FROM bookings WHERE BSH_ID = '1' AND MarketID = '$comma_separated'"; $result = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli)); // This is where my problems start $SIteID = array(); while ($newArray = mysqli_fetch_array($result, MYSQLI_ASSOC)) { $SIteID[] = $newArray[SIteID]; } $locationList = implode(",",$SIteID); ?> Basically what I need to do is correctly move the query results to an array that I can implode and use in a 3rd query to pull names from table3.

  Read the article

• How to parse xml in sql server to process NULL value in DateTime DataType.

  - by Shantanu Gupta

  I have created a sample query in sql server to parse data from xml and to display it right now. Although I will be inserting this data in my table but before that I am facing a simple problem. I want to insert NULL in datetime field ADDED_DATE="NULL" as shown in xml given below. But when I executes this query. It gives me error Conversion failed when converting datetime from character string. What mistake am i doing. Please highlight my mistake. declare @xml varchar(1000) set @xml= ' <ROOT> <TX_MAP FK_GUEST_ID="1" FK_CATEGORY_ID="2" ATTRIBUTE="Test" DESCRIPTION="TestDesc" IS_ACTIVE="1" ADDED_BY="NULL" ADDED_DATE="NULL" MODIFIED_BY="NULL" MODIFIED_DATE="NULL"></TX_MAP> <TX_MAP FK_GUEST_ID="2" FK_CATEGORY_ID="1" ATTRIBUTE="Test2" DESCRIPTION="TestDesc2" IS_ACTIVE="1" ADDED_BY="NULL" ADDED_DATE="NULL" MODIFIED_BY="NULL" MODIFIED_DATE="NULL"></TX_MAP> </ROOT> ' declare @handle int exec sp_xml_preparedocument @handle output, @xml select * from OPENXML(@handle,'/ROOT/TX_MAP',1) with ( FK_GUEST_ID INT ,FK_CATEGORY_ID VARCHAR(10) ,ATTRIBUTE VARCHAR(100) ,[DESCRIPTION] VARCHAR(100) ,IS_ACTIVE VARCHAR(10) ,ADDED_BY VARCHAR(100) ,ADDED_DATE DATETIME NULL ,MODIFIED_BY VARCHAR(100) ,MODIFIED_DATE DATETIME NULL ) I am using Sql Server 2005.

  Read the article

• MS Query Analyzer / Management Studio replacement?

  - by kprobst

  I've been using SQL Server since version 6.5 and I've always been a bit amazed at the fact that the tools seem to be targeted to DBAs rather than developers. I liked the simplicity and speed of the Query Analyzer for example, but hated the built-in editor, which was really no better than a syntax coloring-capable Notepad. Now that we have Management Studio the management part seems a bit better but from a developer standpoint the tools is even worse. Visual Studio's excellent text editor... without a way to customize keyboard bindings!? Don't get me started on how unusable is the tree-based management hierarchy. Why can't I re-root the tree on a list of stored procs for example the way the Enterprise Manager used to allow? Now I have a treeview that needs to be scrolled horizontally, which makes it eminently useless. The SQL server support in Visual Studio is fantastic for working with stored procedures and functions, but it's terrible as a general ad hoc data query tool. I've tried various tools over the years but invariably they seem to focus on the management side and shortchange the developer in me. I just want something with basic admin capabilities, good keyboard support and requisite DDL functionality (ideally something like the Query Analyzer). At this point I'm seriously thinking of using vim+sqlcmd and a console... I'm that desperate :) Those of you who work day in and day out with SQL Server and Visual Studio... do you find the tools to be adequate? Have you ever wished they were better and if you have found something better, could you share please? Thanks!

  Read the article

• #Error showing up in multiple LEFT JOIN statement Access query when value should be NULL

  - by lar

  I'm trying to return an ID's last 4 years of data, if existing. The table (call it A_TABLE) looks like this: ID, Year, Val The idea behind the query is this: for each ID/Year in the table, LEFT JOIN with Year-1, Year-2, and Year-3 (to get 4 years of data) and then return Val for each year. Here's the SQL: SELECT a.ID, a.year AS [Year], a.Val AS VAL, a1.year AS [Year-1], a1.Val AS [VAL-1], a2.year AS [Year-2], a2.Val AS [VAL-2], a3.year AS [Year-3], a3.Val AS [VAL-3] FROM ( ([A_TABLE] AS a LEFT JOIN [A_TABLE] AS a1 ON (a.ID = a1.ID) AND (a.year = a1.year+1)) LEFT JOIN [A_TABLE] AS a2 ON (a.ID = a2.ID) AND (a.year = a2.year+2)) LEFT JOIN [A_TABLE] AS a3 ON (a.ID = a3.ID) AND (a.year = a3.year+3) The problem is that, for past years where there is no data (eg, Year-1), I see "#Error" in the appropriate VAL column (eg, [VAL-1]). The weird thing is, I see the expected "null" in the Year column (eg, [YEAR-1]). Some sample data: ID YEAR VAL Dave 2004 1 Dave 2006 2 Dave 2007 3 Dave 2008 5 Dave 2009 0 outputs like this: ID YEAR VAL YEAR-1 VAL-1 YEAR-2 VAL-2 YEAR-3 VAL-3 Dave 2004 1 #Error #Error #Error Dave 2006 2 #Error 2004 1 #Error Dave 2007 3 2006 2 #Error 2004 1 Dave 2008 5 2007 3 2006 2 #Error Dave 2009 0 2008 5 2007 3 2006 2 Does that make sense? Why am I getting the appropriate NULL val for the non-existent YEARs, but an #Error for the non-existent VALs? (This is Access 2000. Conditional statements like "IIf(a1.val is null, -999, a1.val)" do not seem to do anything.) EDIT: It turns out that the errors are somehow caused by the fact that A_TABLE is actually a query. When I put all the data into an actual table and run the same query, everything shows up as it should. Thanks for the help, everyone.

  Read the article

• Ruby on Rails ActiveRecord/Include/Associations can't get my query to work

  - by Cypher

  I just started learning Rails and I'm just trying to set up query via associations. All the queries I try to write seem to be doing bizzare things and end up trying to query two tables parsed together with an '_' as one table. I have no clue why this would ever happen My tables are as follows: schools: id name variables: id name type var_entries: id variable_id entry school_entries: id school_id var_entry_id my rails association tables are $local = { :adapter => "mysql", :host => "localhost", :port => "3306".to_i, :database => "spy_2", :username =>"root", :password => "vertrigo" } class School < ActiveRecord::Base establish_connection $local has_many :school_entries has_many :var_entries, :through => school_entries end class Variable < ActiveRecord::Base establish_connection $local has_many :var_entries has_many :school_entries, :through => :var_entries end class VarEntry < ActiveRecord::Base establish_connection $local has_many_and_belongs_to :school_entries belongs_to :variables end class SchoolEntry < ActiveRecord::Base establish_connection $local belongs_to :school has_many :var_entries end I want to do this sql query: SELECT school_id, variable_id,rank FROM school_entries, variables, var_entries, schools WHERE var_entries.variable_id = variables.id AND school_entries.var_entry_id = var_entries.id AND schools.id = school_entries.school_id AND variables.type = 'number'; and put it into Rails notation: here is one of my many failed attempts schools = VarEntry.all(:include => [:school_entries, :variables], :conditions => "variables.type = 'number'") the error: 'const_missing': uninitialized constant VarEntry::Variables (NameError) if i remove variables schools = VarEntry.all(:include => [:school_entries, :variables], :conditions => "type = 'number'") the error is: Mysql::Error: Unkown column 'type' in 'where clause': SELECT * FROM 'var_entries' WHERE (type=number) (ActiveRecord::StatementInvalid) Can anyone tell me where I'm going horribly wrong?

  Read the article

< Previous Page | 219 220 221 222 223 224 225 226 227 228 229 230  | Next Page >