Search Results

Search found 17401 results on 697 pages for 'query optimizer'.

Page 95/697 | < Previous Page | 91 92 93 94 95 96 97 98 99 100 101 102  | Next Page >

• HQL Query Not Running

  - by Sarang

  select new UpdateCountDataBean(count(elements(am.actionId)) as noOfUpdates, am.pname as name) from ActivityMaster am group by am.pname The UpdateCountDataBean is created by me while the second class i.e. ActivityMaster is POJO class. java.lang.NullPointerException at org.hibernate.hql.ast.tree.MethodNode.handleElements(MethodNode.java:158) at org.hibernate.hql.ast.tree.MethodNode.resolveCollectionProperty(MethodNode.java:109) at org.hibernate.hql.ast.tree.CollectionFunction.resolve(CollectionFunction.java:22) at org.hibernate.hql.ast.HqlSqlWalker.processFunction(HqlSqlWalker.java:835) at org.hibernate.hql.antlr.HqlSqlBaseWalker.collectionFunction(HqlSqlBaseWalker.java:2558) at org.hibernate.hql.antlr.HqlSqlBaseWalker.aggregateExpr(HqlSqlBaseWalker.java:2907) at org.hibernate.hql.antlr.HqlSqlBaseWalker.count(HqlSqlBaseWalker.java:2483) at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectExpr(HqlSqlBaseWalker.java:1971) at org.hibernate.hql.antlr.HqlSqlBaseWalker.aliasedSelectExpr(HqlSqlBaseWalker.java:2057) at org.hibernate.hql.antlr.HqlSqlBaseWalker.constructor(HqlSqlBaseWalker.java:2226) at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectExpr(HqlSqlBaseWalker.java:1952) at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectExprList(HqlSqlBaseWalker.java:1825) at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectClause(HqlSqlBaseWalker.java:1394) at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:553) at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281) at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229) at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:228) at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:160) at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111) at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77) at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56) at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72) at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133) at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112) at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)

  Read the article

• LINQ self referencing query

  - by Chris

  I have the following SQL query: select p1.[id], p1.[useraccountid], p1.[subject], p1.[message], p1.[views], p1.[parentid], case when p2.[created] is null then p1.[created] else p2.[created] end as LastUpdate from forumposts p1 left join ( select parentid, max(created) as [created] from forumposts group by parentid ) p2 on p2.parentid = p1.id where p1.[parentid] is null order by LastUpdate desc Using the following class: public class ForumPost : PersistedObject { public int Views { get; set; } public string Message { get; set; } public string Subject { get; set; } public ForumPost Parent { get; set; } public UserAccount UserAccount { get; set; } public IList<ForumPost> Replies { get; set; } } How would I replicate such a query in LINQ? I've tried several variations, but I seem unable to get the correct join syntax. Is this simply a case of a query that is too complicated for LINQ? Can it be done using nested queries some how? The purpose of the query is to find the most recently updated posts i.e. replying to a post would bump it to the top of the list. Replies are defined by the ParentID column, which is self-referencing.

  Read the article

• Whats wrong with this HQL query?

  - by ManBugra

  did i encounter a hibernate bug or do i have an error i dont see: select enty.number from EntityAliasName enty where enty.myId in ( select cons.myId from Consens cons where cons.number in ( select ord.number from Orders ord where ord.customer = :customer and ord.creationDate < ( select max(ord.creationDate) from Orders ord where ord.customer = :customer ) ) ) what i do get is the following: org.hibernate.util.StringHelper.root(StringHelper.java:257) Caused by: java.lang.NullPointerException at org.hibernate.util.StringHelper.root(StringHelper.java:257) at org.hibernate.persister.entity.AbstractEntityPersister.getSubclassPropertyTableNumber(AbstractEntityPersister.java:1391) at org.hibernate.persister.entity.BasicEntityPropertyMapping.toColumns(BasicEntityPropertyMapping.java:54) at org.hibernate.persister.entity.AbstractEntityPersister.toColumns(AbstractEntityPersister.java:1367) at org.hibernate.hql.ast.tree.FromElement.getIdentityColumn(FromElement.java:320) at org.hibernate.hql.ast.tree.IdentNode.resolveAsAlias(IdentNode.java:154) at org.hibernate.hql.ast.tree.IdentNode.resolve(IdentNode.java:100) at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:117) at org.hibernate.hql.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:113) at org.hibernate.hql.ast.HqlSqlWalker.resolve(HqlSqlWalker.java:854) at org.hibernate.hql.antlr.HqlSqlBaseWalker.propertyRef(HqlSqlBaseWalker.java:1172) at org.hibernate.hql.antlr.HqlSqlBaseWalker.propertyRefLhs(HqlSqlBaseWalker.java:5167) at org.hibernate.hql.antlr.HqlSqlBaseWalker.propertyRef(HqlSqlBaseWalker.java:1133) at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectExpr(HqlSqlBaseWalker.java:1993) at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectExprList(HqlSqlBaseWalker.java:1932) at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectClause(HqlSqlBaseWalker.java:1476) at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:580) at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:288) at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:231) at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:254) at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:185) at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136) at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101) at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80) at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:94) at org.hibernate.impl.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:484) at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:394) at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1341) using: Hibernate 3.3.2.GA / postgresql

  Read the article

• I'm trying to populate a MySQL table with some data, but, mysqli won't let me insert every 10th stat

  - by Tunji Gbadamosi

  I want to initialise a 'ticket' table with some ticket IDs. To do this, I want to insert 120 ticket IDs into the table. However, at every 10th statement, MySQL tells me that the ID already exists and thus won't let me insert it. Here's my code: //make a query $insert_ticket_query = "INSERT INTO ticket (id) VALUES (?)"; $insert_ticket_stmt = $mysqli->stmt_init(); $insert_ticket_stmt->prepare($insert_ticket_query); $insert_ticket_stmt->bind_param('s', $ticket_id); $mysqli->autocommit(FALSE); //start transaction for($i=0;$i<NO_GUESTS;$i++){ $id = generate_id($i); $ticket_id = format_id($id, $prefix['ticket'], $suffix['ticket']); $t_id = $ticket_id; //echo '<p>'.$ticket_id.'</p>'; //$result = $mysqli->query("SELECT * FROM ticket WHERE id='".$ticket_id."'"); //$row_count = $result->num_rows; if(($result = $mysqli->query("SELECT * FROM ticket WHERE id='".$t_id."'")) == FALSE){ $result->close(); if($insert_ticket_stmt->execute()){ $mysqli->commit(); echo "<p>".$t_id."added to the ticket table!</p>"; } else{ $mysqli->rollback(); echo "problem inserting'".$t_id."' to the ticket table"; } } else{ echo "<p>".$t_id."already exists, so not adding it!</p>"; $result->close(); } } $mysqli->autocommit(TRUE); $insert_ticket_stmt->close(); ?>

  Read the article

• Is this possible to join tables in doctrine ORM without using relations?

  - by piemesons

  Suppose there are two tables. Table X-- Columns: id x_value Table Y-- Columns: id x_id y_value Now I dont want to define relationship in doctrine classes and i want to retrieve some records using these two tables using a query like this: Select x_value from x, y where y.id="variable_z" and x.id=y.x_id; I m not able to figure out how to write query like this in doctrine orm EDIT: Table structures: Table 1: CREATE TABLE IF NOT EXISTS `image` ( `id` int(11) NOT NULL AUTO_INCREMENT, `random_name` varchar(255) NOT NULL, `user_id` int(11) NOT NULL, `community_id` int(11) NOT NULL, `published` varchar(1) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=259 ; Table 2: CREATE TABLE IF NOT EXISTS `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `city` varchar(20) DEFAULT NULL, `state` varchar(20) DEFAULT NULL, `school` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ; Query I am using: $q = new Doctrine_RawSql(); $q ->select('{u.*}, {img.*}') ->from('users u LEFT JOIN image img ON u.id = img.user_id') ->addComponent('u', 'Users u') ->addComponent('img', 'u.Image img') ->where("img.community_id='$community_id' AND img.published='y' AND u.state='$state' AND u.city='$city ->orderBy('img.id DESC') ->limit($count+12) ->execute(); Error I am getting: Fatal error: Uncaught exception 'Doctrine_Exception' with message 'Couldn't find class u' in C:\xampp\htdocs\fanyer\doctrine\lib\Doctrine\Table.php:290 Stack trace: #0 C:\xampp\htdocs\fanyer\doctrine\lib\Doctrine\Table.php(240): Doctrine_Table- >initDefinition() #1 C:\xampp\htdocs\fanyer\doctrine\lib\Doctrine\Connection.php(1127): Doctrine_Table->__construct('u', Object(Doctrine_Connection_Mysql), true) #2 C:\xampp\htdocs\fanyer\doctrine\lib\Doctrine\RawSql.php(425): Doctrine_Connection- >getTable('u') #3 C:\xampp\htdocs\fanyer\doctrine\models\Image.php(33): Doctrine_RawSql- >addComponent('img', 'u.Image imga') #4 C:\xampp\htdocs\fanyer\community_images.php(31): Image->get_community_images_gallery_filter(4, 0, 'AL', 'ALBERTVILLE') #5 {main} thrown in C:\xampp\htdocs\fanyer\doctrine\lib\Doctrine\Table.php on line 290

  Read the article

• Does replace into have a where clause?

  - by Lajos Arpad

  I'm writing an application and I'm using MySQL as DBMS, we are downloading property offers and there were some performance issues. The old architecture looked like this: A property is updated. If the number of affected rows is not 1, then the update is not considered successful, elseway the update query solves our problem. If the update was not successful, and the number of affected rows is more than 1, we have duplicates and we delete all of them. After we deleted duplicates if needed if the update was not successful, an insert happens. This architecture was working well, but there were some speed issues, because properties are deleted if they were not updated for 15 days. Theoretically the main problem is deleting properties, because some properties are alive for months and the indexes are very far from each other (we are talking about 500, 000+ properties). Our host told me to use replace into instead of deleting properties and all deprecated properties should be considered as DEAD. I've done this, but problems started to occur because of syntax error and I couldn't find anywhere an example of replace into with a where clause (I'd like to replace a DEAD property with the new property instead of deleting the old property and insert a new to assure optimization). My query looked like this: replace into table_name(column1, ..., columnn) values(value1, ..., valuen) where ID = idValue Of course, I've calculated idValue and handled everything but I had a syntax error. I would like to know if I'm wrong and there is a where clause for replace into. I've found an alternative solution, which is even better than replace into (using simply an update query) because deletes are happening behind the curtains if I use replace into, but I would like to know if I'm wrong when I say that replace into doesn't have a where clause. For more reference, see this link: http://dev.mysql.com/doc/refman/5.0/en/replace.html Thank you for your answers in advance, Lajos Árpád

  Read the article

• XPATH query, HtmlAgilityPack and Extracting Text

  - by Soham

  I had been trying to extract links from a class called "tim_new" . I have been given a solution as well. Both the solution, snippet and necessary information is given here The said XPATH query was "//a[@class='tim_new'], my question is, how did this query differentiate between the first line of the snippet (given in the link above and the second line of the snippet). More specifically, what is the literal translation (in English) of this XPATH query. Furthermore, I want to write a few lines of code to extract the text written against NSE: <div class="FL gL_12 PL10 PT15">BSE: 523395 &nbsp;&nbsp;|&nbsp;&nbsp; NSE: 3MINDIA &nbsp;&nbsp;|&nbsp;&nbsp; ISIN: INE470A01017</div> Would appreciate help in forming the necessary selection query. My code is written as: IEnumerable<string> NSECODE = doc.DocumentNode.SelectSingleNode("//div[@NSE:]"); But this doesnt look right. Would appreciate some help.

  Read the article

• PHP MYSQLI query error?

  - by Chris Leah

  Hey This is my login script, using PHP5 and MYSQLi, I just had help spotting errors in my query, but still it will not let me login, even though the username and password are correct and in the database, it just keeps returning the error: your username and password do not match any in our db. But I know they do lol...could any body spot the problem? //Check if the form has been submitted if (isset($_POST['login'])) { //Check if username and password are empty if ($_POST['username']!='' && $_POST['password']!='') { //Create query to check username and password to database $validate_user = $mysqli->query('SELECT id, username, password, active FROM users WHERE = username = "'.$mysqli->real_escape_string($_POST['username']).'" AND password = "'.$mysqli->real_escape_string(md5($_POST['password'])).'"'); //We check if the query returns true if ($validate_user->num_rows == 1) { $row = $validate_user->fetch_assoc(); //Check if the user has activated there account if ($row['activated'] == 1) { $_SESSION['id'] = $row['id']; $_SESSION['logged_in'] = true; Header('Location: ../main/index.php'); } //Show this error if activation returns as 0 else { $error = '<p class="error">Please activate your account.</p>'; } } //Show this error if the details matched any in the db else { $error = '<p class="error">Your username and password are not in our database!</p>'; } } //Show this error if the username and password field have not been entered else { $error = '<p class="error">Please enter your username and password.</p>'; } }

  Read the article

• Retrieve 2 last posts for each category.

  - by Savageman

  Hello, Lets say I have 2 tables: blog_posts and categories. Each blog post belongs to only ONE category, so there is basically a foreign key between the 2 tables here. I would like to retrieve the 2 lasts posts from each category, is it possible to achieve this in a single request? GROUP BY would group everything and leave me with only one row in each category. But I want 2 of them. It would be easy to perform 1 + N query (N = number of category). First retrieve the categories. And then retrieve 2 posts from each category. I believe it would also be quite easy to perform M queries (M = number of posts I want from each category). First query selects the first post for each category (with a group by). Second query retrieves the second post for each category. etc. I'm just wondering if someone has a better solution for this. I don't really mind doing 1+N queries for that, but for curiosity and general SQL knowledge, it would be appreciated! Thanks in advance to whom can help me with this.

  Read the article

• Manipulate score/rank on query results from NHibernate.Search

  - by Fernando Figueiredo

  I've been working with NHibernate, NHibernate.Search and Lucene.Net to improve the search engine used on the website I develop. Basically, I use it to search contents of corporations specification documents. This is not to be confused with Lucene's notion of documents: in my case, a specification document (which I'll hereafter call a "specdoc") can contain many pages, and the content of these pages are the ones that are actually indexed (thus, the pages themselves are the ones that fall into Lucene's concept of documents). So, the pages belong to a specdoc, that in turn belong to a corporation (so, a corporation can have many specdocs). I'm using NHibernate.Search "IndexEmbedded" and "ContainedIn" attributes to associate the pages with their specdoc and the specdocs to their corporations, so I can query for terms in specdoc pages and have Lucene/NH.Search return either the pages themselves, the specdocs, or the corporations that match the query on the pages. I can query this way and get ranked results, thus presenting results (that is, corporations, specdocs or pages) by relevance, which is great. But now I need something more. Specifically in the case where I query terms and have NH.Search return the corporations that match, I need to manually/artificially tune the score of some of the results, because there are corporations that I want to show up on the top of the result set - think of "sponsored results". I'm thinking of doing it on my application, maybe creating an entity/database table that contain an association to the corporation entity, and a score boost value. But I don't know how to feed this to Lucene and have it boost the results accordingly at search time. Initially I thought about deriving a Similarity class to do this, but it doesn't look like Similarity can be used to modify result sets at search time. As per this page, it looks like what I need is to mess around with weight or scoring. But the docs are a little superficial in that there are no examples on how to implement a custom scoring, let alone integrate it with NH.Search. So, does anyone know how to do this, or point me to some documentation or working example on how to do something similar? Thanks!

  Read the article

• SQL Server, how to join a table in a "rotated" format (returning columns instead of rows)?

  - by Joshua Carmody

  Sorry for the lame title, my descriptive skills are poor today. In a nutshell, I have a query similar to the following: SELECT P.LAST_NAME, P.FIRST_NAME, D.DEMO_GROUP FROM PERSON P JOIN PERSON_DEMOGRAPHIC PD ON PD.PERSON_ID = P.PERSON_ID JOIN DEMOGRAPHIC D ON D.DEMOGRAPHIC_ID = PD.DEMOGRAPHIC_ID This returns output like this: LAST_NAME FIRST_NAME DEMO_GROUP --------------------------------------------- Johnson Bob Male Smith Jane Female Smith Jane Teacher Beeblebrox Zaphod Male Beeblebrox Zaphod Alien Beeblebrox Zaphid Politician I would prefer the output be similar to the following: LAST_NAME FIRST_NAME Male Female Teacher Alien Politician --------------------------------------------------------------------------------------------------------- Johnson Bob 1 0 0 0 0 Smith Jane 0 1 1 0 0 Beeblebrox Zaphod 1 0 0 1 1 The number of rows in the DEMOGRAPHIC table varies, so I can't say with certainty how many columns I need. The query needs to be flexible. Yes, it would be trivial to do this in code. But this query is one piece of a complicated set of stored procedures, views, and reporting services, many of which are outside my sphere of influence. I need to produce this output inside the database to avoid breaking the system. Any ideas? This is MS SQL Server 2005, by the way. Thanks.

  Read the article

• MySql: Problem when using a temporary table

  - by Alex

  Hi, I'm trying to use a temporary tables to store some values I need for a query. The reason of using a temporary table is that I don't want to store the data permanently so different users can modify it at the same time. That data is just stored for a second, so I think a temporary table is the best approach for this. The thing is that it seems that the way I'm trying to use it is not right (the query works if I use a permanent one). This is an example of query: CREATE TEMPORARY TABLE SearchMatches (PatternID int not null primary key, Matches int not null) INSERT INTO SearchMatches (PatternID, Matches) VALUES ('12605','1'),('12503','1'),('12587','2'),('12456','1'), ('12457','2'),('12486','2'),('12704','1'),(' 12686','1'), ('12531','2'),('12549','1'),('12604','1'),('12504','1'), ('12586','1'),('12548','1'),('12 530','1'),('12687','2'), ('12485','1'),('12705','1') SELECT pat.id, signatures.signature, products.product, versions.version, builds.build, pat.log_file, sig_types.sig_type, pat.notes, pat.kb FROM patterns AS pat INNER JOIN signatures ON pat.signature = signatures.id INNER JOIN products ON pat.product = products.id INNER JOIN versions ON pat.version = versions.id INNER JOIN builds ON pat.build = builds.id INNER JOIN sig_types ON pat.sig_type = sig_types.id, SearchMatches AS sm INNER JOIN patterns ON patterns.id = sm.PatternID WHERE sm.Matches <> 0 ORDER BY sm.Matches DESC, products.product, versions.version, builds.build LIMIT 0 , 50 Any suggestion? Thanks.

  Read the article

• Active Directory - Query Group for all machines

  - by Ben Cawley

  Hi, I'm trying to obtain a list of all Machines that are members of a known group. I have the group GUID and am constructing a query using the "memberof=" format and filtering by ObjectClass. This works fine but doesn't return machines if the PrimaryGroup attribute of a machine is set to be the known group. In this case, that machine won't be returned. I've found the explanation of why this is in the following link (See Joe Kaplan's response) http://www.eggheadcafe.com/software/aspnet/29773581/active-directory-query-c.aspx Unfortunately the outlined answer is how to obtain the list of groups from a given user. I'd like to do the reverse and from a given group obtain the list of machines. It seems that the PrimaryGroup information is stored on the Machine/User side so I'm not sure if what I want to do is even possible. I had thought I would be able to query the TokenGroup attribute of the known group and then construct a query to return all machines that have the TokenGroup attribute set but it seems that not all groups have this attribute. Does anyone have any ideas or suggestions? If any clarification is needed let me know! Cheers, Ben

  Read the article

• Mysqli query only works on localhost, not webserver

  - by whamo

  Hello. I have changed some of my old queries to the Mysqli framework to improve performance. Everything works fine on localhost but when i upload it to the webserver it outputs nothing. After connecting I check for errors and there are none. I also checked the php modules installed and mysqli is enabled. I am certain that it creates a connection to the database as no errors are displayed. (when i changed the database name string it gave the error) There is no output from the query on the webserver, which looks like this: $mysqli = new mysqli("server", "user", "password"); if (mysqli_connect_errno()) { printf("Can't connect Errorcode: %s\n", mysqli_connect_error()); exit; } // Query used $query = "SELECT name FROM users WHERE id = ?"; if ($stmt = $mysqli->prepare("$query")) { // Specify parameters to replace '?' $stmt->bind_param("d", $id); $stmt->execute(); // bind variables to prepared statement $stmt->bind_result($_userName); while ($stmt->fetch()) { echo $_userName; } $stmt-close(); } } //close connection $mysqli-close(); As I said this code works perfectly on my localserver just not online. Checked the error logs and there is nothing so everything points to a good connection. All the tables exists as well etc. Anyone any ideas because this one has me stuck! Also, if i get this working, will all my other queries still work? Or will i need to make them use the mysqli framework as well? Thanks in advance.

  Read the article

• Query eror handling in CodeIgniter

  - by Sajith S Narayanan

  Hi All, I am trying to execute an MySql query using the CI Active methods. If the query is malformed, then CI invokes internal server error 500 and quits without processing the next steps.. I need to roll back all the other queries processed before that error statement, and the roll back is also not happening.. can you help pls. The code snippets is as below: function dbInsertInformationToDB($data_array) { $returnID = ""; $uniqueDataArray = array(); // I prepare a array of values here $uniqueTableList = filter_unique_tables($data_array[2]); $this->db->trans_begin(); // inserting is done here // when there is a query error in $this->db->insert().. it is not rolling back the previous query executed foreach($uniqueTableList as $table_name) { $uniqueDataArray = filterDataArray($data_array,$table_name,2); $this->db->insert($table_name,$uniqueDataArray); if ($this->db->_error_message()) { $error = "I am caught!!"; } $returnID = $this->db->affected_rows(); } if ($this->db->trans_status() === FALSE) { $this->db->trans_rollback(); } else { $this->db->trans_commit(); } return "ERROR"; }

  Read the article

• How to get SQL Function run a different query and return value from either query?

  - by RoguePlanetoid

  I need a function, but cannot seem to get it quite right, I have looked at examples here and elsewhere and cannot seem to get this just right, I need an optional item to be included in my query, I have this query (which works): SELECT TOP 100 PERCENT SKU, Description, LEN(CONVERT(VARCHAR (1000),Description)) AS LenDesc FROM tblItem WHERE Title = @Title AND Manufacturer = @Manufacturer ORDER BY LenDesc DESC This works within a Function, however the Manufacturer is Optional for this search - which is to find the description of a similar item, if none is present, the other query is: SELECT TOP 100 PERCENT SKU, Description, LEN(CONVERT(VARCHAR (1000),Description)) AS LenDesc FROM tblItem WHERE Title = @Title ORDER BY LenDesc DESC Which is missing the Manufacturer, how to I get my function to use either query based on the Manufacturer Value being present or not. The reason is I will have a function which first checks an SKU for a Description, if it is not present - it uses this method to get a Description from a Similar Product, then updates the product being added with the similar product's description. Here is the function so far: ALTER FUNCTION [dbo].[GetDescriptionByTitleManufacturer] ( @Title varchar(400), @Manufacturer varchar(160) ) RETURNS TABLE AS RETURN ( SELECT TOP 100 PERCENT SKU, Description, LEN(CONVERT(VARCHAR (1000),Description)) AS LenDesc FROM tblItem WHERE Title = @Title AND Manufacturer = @Manufacturer ORDER BY LenDesc DESC ) I've tried adding BEGINs and IF...ELSEs but get errors or syntax problems each way I try it, I want to be able to do something like this pseudo-function (which does not work): ALTER FUNCTION [dbo].[GetDescriptionByTitleManufacturer] ( @Title varchar(400), @Manufacturer varchar(160) ) RETURNS TABLE AS BEGIN IF (@Manufacturer = Null) RETURN ( SELECT TOP 100 PERCENT SKU, Description, LEN(CONVERT(VARCHAR (1000),Description)) AS LenDesc FROM tblItem WHERE Title = @Title ORDER BY LenDesc DESC ) ELSE RETURN ( SELECT TOP 100 PERCENT SKU, Description, LEN(CONVERT(VARCHAR (1000),Description)) AS LenDesc FROM tblItem WHERE Title = @Title AND Manufacturer = @Manufacturer ORDER BY LenDesc DESC ) END

  Read the article

• MYSQL fetch 10 posts, each w/ vote count, sorted by vote count, limited by where clause on posts

  - by nibblebot

  I want to fetch a set of Posts w/ vote count listed, sorted by vote count (e.g.) Post 1 - Post Body blah blah - Votes: 500 Post 2 - Post Body blah blah - Votes: 400 Post 3 - Post Body blah blah - Votes: 300 Post 4 - Post Body blah blah - Votes: 200 I have 2 tables: Posts - columns - id, body, is_hidden Votes - columns - id, post_id, vote_type_id Here is the query I've tried: SELECT p.*, v.yes_count FROM posts p LEFT JOIN (SELECT post_id, vote_type_id, COUNT(1) AS yes_count FROM votes WHERE (vote_type_id = 1) GROUP BY post_id ORDER BY yes_count DESC LIMIT 0, 10) v ON v.post_id = p.id WHERE (p.is_hidden = 0) ORDER BY yes_count DESC LIMIT 0, 10 Correctness: The above query almost works. The subselect is including votes for posts that have is_hidden = 1, so when I left join it to posts, if a hidden post is in the top 10 (ranked by votes), I can end up with records with NULL on the yes_count field. Performance: I have ~50k posts and ~500k votes. On my dev machine, the above query is running in .4sec. I'd like to stay at or below this execution time. Indexes: I have an index on the Votes table that covers the fields: vote_type_id and post_id EXPLAIN id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY p ALL NULL NULL NULL NULL 45985 Using where; Using temporary; Using filesort 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10 2 DERIVED votes ref VotingPost VotingPost 4 319881 Using where; Using index; Using temporary; Using filesort

  Read the article

• How can I work around SQL Server - Inline Table Value Function execution plan variation based on par

  - by Ovidiu Pacurar

  Here is the situation: I have a table value function with a datetime parameter ,lest's say tdf(p_date) , that filters about two million rows selecting those with column date smaller than p_date and computes some aggregate values on other columns. It works great but if p_date is a custom scalar value function (returning the end of day in my case) the execution plan is altered an the query goes from 1 sec to 1 minute execution time. A proof of concept table - 1K products, 2M rows: CREATE TABLE [dbo].[POC]( [Date] [datetime] NOT NULL, [idProduct] [int] NOT NULL, [Quantity] [int] NOT NULL ) ON [PRIMARY] The inline table value function: CREATE FUNCTION tdf (@p_date datetime) RETURNS TABLE AS RETURN ( SELECT idProduct, SUM(Quantity) AS TotalQuantity, max(Date) as LastDate FROM POC WHERE (Date < @p_date) GROUP BY idProduct ) The scalar value function: CREATE FUNCTION [dbo].[EndOfDay] (@date datetime) RETURNS datetime AS BEGIN DECLARE @res datetime SET @res=dateadd(second, -1, dateadd(day, 1, dateadd(ms, -datepart(ms, @date), dateadd(ss, -datepart(ss, @date), dateadd(mi,- datepart(mi,@date), dateadd(hh, -datepart(hh, @date), @date)))))) RETURN @res END Query 1 - Working great SELECT * FROM [dbo].[tdf] (getdate()) The end of execution plan: Stream Aggregate Cost 13% <--- Clustered Index Scan Cost 86% Query 2 - Not so great SELECT * FROM [dbo].[tdf] (dbo.EndOfDay(getdate())) The end of execution plan: Stream Aggregate Cost 4% <--- Filter Cost 12% <--- Clustered Index Scan Cost 86%

  Read the article

• Testing performance of queries in mysl

  - by Unreason

  I am trying to setup a script that would test performance of queries on a development mysql server. Here are more details: I have root access I am the only user accessing the server Mostly interested in InnoDB performance The queries I am optimizing are mostly search queries (SELECT ... LIKE '%xy%') What I want to do is to create reliable testing environment for measuring the speed of a single query, free from dependencies on other variables. Till now I have been using SQL_NO_CACHE, but sometimes the results of such tests also show caching behaviour - taking much longer to execute on the first run and taking less time on subsequent runs. If someone can explain this behaviour in full detail I might stick to using SQL_NO_CACHE; I do believe that it might be due to file system cache and/or caching of indexes used to execute the query, as this post explains. It is not clear to me when Buffer Pool and Key Buffer get invalidated or how they might interfere with testing. So, short of restarting mysql server, how would you recommend to setup an environment that would be reliable in determining if one query performs better then the other?

  Read the article

• Advanced Linq query using into

  - by dilbert789

  I have this query that someone else wrote, it's over my head so I'm looking for some direction. What is happening currently is that it's taking numbers where there is a goal and no history entered, or history and no goal, this screws up the calculations as both goal and history for the same item are required on each. The three tables involved are: KPIType Goal KPIHistory What I need: Need all rows from KPIType. Need all goals where there is a matching KPIHistory row (Goal.KPItypeID == KPIHistory.KPItypeID ) into results 1 Need all kpiHistory’s where there is a matching Goal row (Goal.KPItypeID == KPIHistory.KPItypeID ) into results 2 Current query: var query = from t in dcs.KPIType.Where(k => k.ID <= 23) join g in dcs.Goal.Where(g => g.Dealership.ID == dealershipID && g.YearMonth >= beginDate && g.YearMonth <= endDate ) on t.ID equals g.KPITypeID into results1 join h in dcs.KPIHistory.Where(h => h.Dealership.ID == dealershipID && h.ForDate >= beginDate && h.ForDate <= endDate ) on t.ID equals h.KPIType.ID into results2 orderby t.DisplayOrder select new { t, Goal = results1, KPIHistory = results2 }; query.ToList().ForEach(q => { results.Add(q.t); }); Thanks, I'm happy to answer questions if more info needed.

  Read the article

• Rails Active Record Mysql find query HAVING clause

  - by meetraghu28

  Is there a way to use the HAVING clause in some other way without using group by. I am using rails and following is a sample sccenario of the problem that i am facing. In rails you can use the Model.find(:all,:select,conditions,:group) function to get data. In this query i can specify a having clause in the :group param. But what if i dont have a group by clause but want to have a having clause in the result set. Ex: Lets take a query select sum(x) as a,b,c from y where "some_conditions" group by b,c; This query has a sum() aggregation on one of the fields. No if there is nothing to aggregate then my result should be an empty set. But mysql return a NULL row. So this problem can be solved by using select sum(x) as a,b from y where "some_conditions" group by b having a NOT NULL; but what happens in case i dont have a group by clause?? a query like below select sum(x) as a,b from y where "some_conditions"; so how to specify that sum(x) should not be NULL? Any solution that would return an empty set in this case instead of a NULL row will help and also that solution should be doable in rails. We can use subqueries to get this condition working with sumthin like this select * from ((select sum(x) as b FROM y where "some_condition") as subq) where subq.b is not null; but is there a better way to do this thru sql/rails ??

  Read the article

• Parse numbers in singe textbox for query

  - by Joshua Slocum

  I’ve built a webform in Visual Web Developer Express 2008 to help me with my work. I use a webform to run query requests that are emailed to me. The inputs are in this format 12312 12312 12312 12312 12312 12312 12312 12312 I enter the first number in a textbox and the second number in another textbox and click a button that runs a query and returns the results in a gridview(single row). string strConn, strSQL; strConn = AppConfig.Connection strSQL = 'select fields from table where FirstNum=:FirstNum and SecondNum=:SecondNum'; using (OracleConnection cn = new OracleConnection(strConn)) { OracleCommand cmd = new OracleCommand(strSQL, cn); cmd.Parameters.AddWithValue(":FirstNum", txtFirstNum.Text); cmd.Parameters.AddWithValue(":SeconNum", txtSecondNum.Text); cn.Open(); using (OracleDataReader rdr = cmd.ExecuteReader()) { dgResults.DataSource = rdr; dgResults.DataBind(); } cn.Close(); } I had an idea to help me speed up my work. I’d like to be able to past both numbers in a single textbox ( like this 12312 12312 ) and have the code parse out the nubmers for the query. Or even better would be to past all of them in a multiline textbox like this 12312 12312 12312 12312 12312 12312 12312 12312 And have them all parsed and the query run for each line and the results all output to one gridview. I’m just not sure how to approach this. Any suggestions would be appreciated. Thank you.

  Read the article

• Stored Procedure: Variable passed from PHP alters second half of query string

  - by Stephanie

  Hello everyone, Basically, we have an ASP website right now that I'm converting to PHP. We're still using the MSSQL server for the DB -- it's not moving. In the ASP, now, there's an include file with a giant sql query that's being executed. This include sits on a lot of pages and this is a simple version of what happens. Pages A, B and C all use this include file to return a listing. In ASP, Page A passes through variable A to the include file - page B passes through variable B -- page C passes through variable C, and so on. The include file builds the SQL query like this: sql = "SELECT * from table_one LEFT OUTER JOIN table_two ON table_one.id = table_two.id" then adds (remember, ASP), based on the variable passed through from the parent page, Select Case sType Case "A" sql = sql & "WHERE LOWER(column_a) <> 'no' AND LTRIM(ISNULL(column_b),'') <> '' ORDER BY column_a Case "B" sql = sql & "WHERE LOWER(column_c) <> 'no' ORDER BY lastname, firstname Case "C" sql = sql & "WHERE LOWER(column_f) <> 'no' OR LOWER(column_g) <> 'no' ORDER BY column_g As you notice, every string that's added on as the second part of the sql query is different than the previous; not just one variable can be substituted out, which is what has me stumped. How do I translate this case / switch into the stored procedure, based on the varchar input that I pass to the stored procedure via PHP? This stored procedure will actually handle a query listing for about 20 pages, so it's a hefty one and this is my first major complicated one. I'm getting there, though! I'm also just more used to MySQL, too. Not that they're that different. :P Thank you very much for your help in advance. Stephanie

  Read the article

• insert Query is not executing, help me in tracking the problem

  - by Parth

  I tried the below query but it didnt executed giving error as : 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 INSERT INTO `jos_menu` SET params = 'orderby= show_noauth= show_title= link_titles= show_intro= show_section= link_section= show_category= link_category= show_author= show_create_date= show_modify_date= show_item_navigation= show_readmore= show_vote= show_icons= show_pdf_icon= show_print_icon= show_email_icon= show_hits= feed_summary= page_title= show_page_title=1 pageclass_sfx= menu_image=-1 secure=0 ', checked_out_time = '0000-00-00 00:00:00', ordering = '13', componentid = '20', published = '1', id = '152', menutype = 'accmenu', name = 'IPL', alias = 'ipl', link = 'index.php?option=com_content&view=archive', type = 'component') then i used mysql_real_escape_string() on the query containing variable which gives me the query as : INSERT INTO `jos_menu` SET params = \'orderby=\nshow_noauth=\nshow_title=\nlink_titles=\nshow_intro=\nshow_section=\nlink_section=\nshow_category=\nlink_category=\nshow_author=\nshow_create_date=\nshow_modify_date=\nshow_item_navigation=\nshow_readmore=\nshow_vote=\nshow_icons=\nshow_pdf_icon=\nshow_print_icon=\nshow_email_icon=\nshow_hits=\nfeed_summary=\npage_title=\nshow_page_title=1\npageclass_sfx=\nmenu_image=-1\nsecure=0\n\n\', checked_out_time = \'0000-00-00 00:00:00\', ordering = \'13\', componentid = \'20\', published = \'1\', id = \'152\', menutype = \'accmenu\', name = \'IPL\', alias = \'ipl\', link = \'index.php?option=com_content&view=archive\', type = \'component\') And on executing the above query I get an error as : 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\'orderby=\nshow_noauth=\nshow_title=\nlink_titles=\nshow_intro=\nshow_section=\' at line 1 Can Someone guide me to track the problem in it? Thanks In Advance....

  Read the article

• Getting table schema from a query

  - by Appu

  As per MSDN, SqlDataReader.GetSchemaTable returns column metadata for the query executed. I am wondering is there a similar method that will give table metadata for the given query? I mean what tables are involved and what aliases it has got. In my application, I get the query and I need to append the where clause programically. Using GetSchemaTable(), I can get the column metadata and the table it belongs to. But even though table has aliases, it still return the real table name. Is there a way to get the aliase name for that table? Following code shows getting the column metadata. const string connectionString = "your_connection_string"; string sql = "select c.id as s,c.firstname from contact as c"; using(SqlConnection connection = new SqlConnection(connectionString)) using(SqlCommand command = new SqlCommand(sql, connection)) { connection.Open(); SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo); DataTable schema = reader.GetSchemaTable(); foreach (DataRow row in schema.Rows) { foreach (DataColumn column in schema.Columns) { Console.WriteLine(column.ColumnName + " = " + row[column]); } Console.WriteLine("----------------------------------------"); } Console.Read(); } This will give me details of columns correctly. But when I see BaseTableName for column Id, it is giving contact rather than the alias name c. Is there any way to get the table schema and aliases from a query like the above? Any help would be great!

  Read the article

< Previous Page | 91 92 93 94 95 96 97 98 99 100 101 102  | Next Page >