Search Results

Search found 27396 results on 1096 pages for 'mysql query'.

Page 288/1096 | < Previous Page | 284 285 286 287 288 289 290 291 292 293 294 295  | Next Page >

• What to learn after standard C++?

  - by Luca Cerone

  I switched to C++ a few months ago, learning its syntax, the main features of the STL and what you can usually find in a "learn C++" manual. Now I would like to go further. What would be your recommendations? I would like to know what to learn next (not only about the language, but also debugging, frameworks etc. etc.) I know probably the answer depends on the specific needs of each user, so here is a list of mine: Cross Platform development Developing GUI for my programs Develop extendible software, allowing the use of plugins Use of scientific libraries Interact with databases (mainly MySQL) Having server/client functionalities (I'd like users of my programs to interact through internet.. as you might have guessed I am not a programmer by training so I might have used the wrong terms.. if so I apologize for that). Of course I know it takes time, but I would like to have a good list of references and resources to start (both books and websites are ok). Thanks a lot for your help!

  Read the article

• Search multiple tables

  - by gilden

  I have developed a web application that is used mainly for archiving all sorts of textual material (documents, references to articles, books, magazines etc.). There can be any given number of archive tables in my system, each with its own schema. The schema can be changed by a moderator through the application (imagine something similar to a really dumbed down version of phpMyAdmin). Users can search for anything from all of the tables. By using FULLTEXT indexes together with substring searching (fields which do not support FULLTEXT indexing) the script inserts the results of a search to a single table and by ordering these results by the similarity measure I can fairly easily return the paginated results. However, this approach has a few problems: substring searching can only count exact results the 50% rule applies to all tables separately and thus, mysql may not return important matches or too naively discards common words. is quite expensive in terms of query numbers and execution time (not an issue right now as there's not a lot of data yet in the tables). normalized data is not even searched for (I have different tables for categories, languages and file attatchments). My planned solution Create a single table having columns similar to id, table_id, row_id, data Every time a new row is created/modified/deleted in any of the data tables this central table also gets updated with the data column containing a concatenation of all the fields in a row. I could then create a single index for Sphinx and use it for doing searches instead. Are there any more efficient solutions or best practises how to approach this? Thanks.

  Read the article

• Challenge 19 – An Explanation of a Query

  - by Dave Ballantyne

  I have received a number of requests for an explanation of my winning query of TSQL Challenge 19. This involved traversing a hierarchy of employees and rolling a count of orders from subordinates up to superiors. The first concept I shall address is the hierarchyId , which is constructed within the CTE called cteTree.   cteTree is a recursive cte that will expand the parent-child hierarchy of the personnel in the table @emp.  One useful feature with a recursive cte is that data can be ‘passed’ from the parent to the child data.  The hierarchyId column is similar to the hierarchyId data type that was introduced in SQL Server 2008 and represents the position of the person within the organisation. Let us start with a simplistic example Albert manages Bob and Eddie.  Bob manages Carl and Dave. The hierarchyId will represent each person’s position in this relationship in a single field.  In this simple example we could append the userID together into a varchar field as detailed below. This will enable us to select a branch of the tree by filtering using Where hierarchyId  ‘1,2%’ to select Bob and all his subordinates.  Naturally, this is not comprehensive enough to provide a full solution, but as opposed to concatenating the Id’s together into a varchar datatyped column, we can apply the same theory to a varbinary.  By CASTing the ID’s into a datatype of varbinary(4) ,4 is used as 4 bytes of data are used to store an integer and building a hierarchyId  from those.  For example: The important point to bear in mind for later in the query is that the binary data generated is 'byte order comparable'. ie We can ORDER a dataset with it and the resulting data, will be in the order required. Now, would probably be a good time to download the example file and, after the cte ‘cteTree’, uncomment the line ‘select * from cteTree’.  Mark this and all prior code and execute.  This will show you how this theory directly relates to the actual challenge data.  The only deviation from the above, is that instead of using the ID of an employee, I have used the row_number() ranking function to order each level by LastName,Firstname.  This enables me to order by the HierarchyId in the final result set so that the result set is in the required order. Your output should be something like the below.  Notice also the ‘Level’ Column that contains the depth that the employee is within the tree.  I would encourage you to ‘play’ with the query, change the order in the row_number() or the length of the cast in the hierarchyId to see how that effects the outcome.  The next cte, ‘cteTreeWithOrderCount’, is a join between cteTree and the @ord table, and COUNT’s the number of orders per employee.  A LEFT JOIN is employed here to account for the occasion where an employee has made no sales.   Executing a ‘Select * from cteTreeWithOrderCount’ will return the result set as below.  The order here is unimportant as this is only a staging point of the data and only the final result set in a cte chain needs an Order by clause, unless TOP is utilised. cteExplode joins the above result set to the tally table (Nums) for Level Occurances.  So, if level is 2 then 2 rows are required.  This is done to expand the dataset, to create a new column (PathInc), which is the (n+1) integers contained within the heirarchyid.  For example, with the data for Robert King as given above, the below 3 rows will be returned. From this you can see that the pathinc column now contains the values for Andrew Fuller and Steven Buchanan who are Robert King’s superiors within the tree.    Finally cteSumUp, sums the orders for each person and their subordinates using the PathInc generated above, and the final select does the final simple mathematics and filters to restrict the result set to only the ‘original’ row per employee.

  Read the article

• How To Publish Business Objects Query Service

  - by ssorrrell

  We are trying to copy a BO Query Service from one Universe to another. If you use the BO Query As A Service(QAAS) tool you can do this, but end up basically recreating the query service. It seems like the BusinessObjects.DSWS.* libraries allow you to read and write query services, but those don't appear in the QAAS tool. I think that those queries go into a different Universe than the QAAS tool pings. Perhaps there is a Universe for data and another for Web Service Queries. Monitoring the QAAS tool for HTTP traffic revealed that the BO Web Service used to run queries for the data they contain is also used to manage the Web Service queries. I was able to copy one Query Service into a new one in a new Universe using a Replace() on the XML string in QuerySpec to change the UniverseID. We can basically copy one Query Service to another Universe without manually rebuilding it except for one little thing. The QAAS tool includes a Publish button. This does something unknown, but important. Perhaps it makes some SOAP, WSDL or config files so that the copied Query Service is public. There doesn't seem to be any HTTP traffic to snoop on when it's doing this. The BusinessObjects.DSWS.* libraries include a Publish feature, but it's not for Query Services. It's for general files like Excel and PDF. Right now, we are relegated to using two tools. Does anyone know about how to Publish a BO Query Service programmatically just like the QAAS Tool?

  Read the article

• Appengine datastore phantom entity - inconsistent state?

  - by aloo

  Getting a weird error on java appengine code that used to work fine (nothing has changed but the data in the datastore). I'm trying to iterate over the results of a query and change a few properties of the entities. The query does return a set of results, however, when I try to access the first result in the list, it throws an exception when trying to access any of its properties (but its key). Here's the exception: org.datanucleus.state.JDOStateManagerImpl isLoaded: Exception thrown by StateManager.isLoaded Could not retrieve entity of kind OnTheCan with key OnTheCan(3204258) org.datanucleus.exceptions.NucleusObjectNotFoundException: Could not retrieve entity of kind OnTheCan with key OnTheCan(3204258) at org.datanucleus.store.appengine.DatastoreExceptionTranslator.wrapEntityNotFoundException(DatastoreExceptionTranslator.java:60) And here is my code: PersistenceManager pm = PMF.get().getPersistenceManager(); Query query = null; List<OnTheCan> cans; query = pm.newQuery("SELECT this FROM " + OnTheCan.class.getName() + " WHERE open == true ORDER BY onTheCanId ASC"); query.setRange(0, num); cans = (List<OnTheCan>) query.execute(); for (OnTheCan c : cans) { System.err.println(c.getOnTheCanId()); // this works fine! getting the key works c.setOpen(false); // failure here with the above exception c.setAutoClosed(true); c.setEndTime(new Date(c.getStartTime().getTime() + 600000/*10*60*1000*/)); } pm.close(); The code throws the exception when trying to execute c.setOpen(false) - thats the first time I'm accessing or setting a property that isnt the key. So it seems there is a phantom entity in my datastore with key 3204258. THis entity doesn't really exist (queried the datastore from admin console) but for some reason its being returned by the query. Could my data store be in an inconsistent state? I've managed the following workaround by placing it as the first line in my for loop. Clearly an ugly hack: if (c.getOnTheCanId() == 3204258) { continue; } Any ideas?

  Read the article

• Suggest the best options to me to design the dynamic web interface using PHP MYSQL and AJAX

  - by Krishna

  Hello, I am designing a web interface for a company. I am describing the company's profile: company is currently having 5 branches and planning to extend their branches all over the country. it is an insurance surveying company. they are dealing with 6 Categories in the insurance domain, vide .. Engineering Fire Marine Motor Miscellaneous Risk Inspection and branches named as b1, b2, b3, b4, b5 and Extending. and finally they have contract with 22 companies. For each claim they are assign a unique ID. like contractcompany/category/serialno Ex: take a contracted company names as xxx, sss, zzz. xxx/Engineering/001 sss/Engineering/001 . . . xxx/Enginnering/002 sss/Engineering/002 . . . xxx/Fire/001 sss/Fire/001 . . . xxx/Fire/002 . . . xxx/Fire/002 . . . and so on..... by this way they issue the unique ID for each claim. Finally what i want is developing the interface with PHP mysql and ajax auto generating the unique id for each claim. store full details of the claims with reference to unique id. show all claims in one page, and they can view by branch wise and category wise. send monthly Report (All claims they have given and status of claims) to contract companies. give access to contracted companies, but they can view only their respective claims. Each claim has its own documents. So they can be uploaded by own company users or administrator. these files are associated with unique ID. contracted companies can view files. Give access to branches to enter new claims and update old claims. Administrator can create, update and delete all the claims and their details. Only administrator can grant new users (own company branches / contracted companies) Finally the the panel is completely database driven. Could any body can help. Thanks in advance Kindly do the needful and oblige Thanks and Regards Krishna. P [email protected]

  Read the article

• How can I get a distinct list of elements in a hierarchical query?

  - by RenderIn

  I have a database table, with people identified by a name, a job and a city. I have a second table that contains a hierarchical representation of every job in the company in every city. Suppose I have 3 people in the people table: [name(PK),title,city] Jim, Salesman, Houston Jane, Associate Marketer, Chicago Bill, Cashier, New York And I have thousands of job type/location combinations in the job table, a sample of which follow. You can see the hierarchical relationship since parent_title is a foreign key to title: [title,city,pay,parent_title] Salesman, Houston, $50000, CEO Cashier, Houston, $25000 CEO, USA, $1000000 Associate Marketer, Chicago, $75000 Senior Marketer, Chicago, $125000 ..... The problem I'm having is that my Person table is a composite key, so I don't know how to structure the start with part of my query so that it starts with each of the three jobs in the cities I specified. I can execute three separate queries to get what I want, but this doesn't scale well. e.g.: select * from jobs start with city = (select city from people where name = 'Bill') and title = (select title from people where name = 'Bill') connect by prior parent_title = title UNION select * from jobs start with city = (select city from people where name = 'Jim') and title = (select title from people where name = 'Jim') connect by prior parent_title = title UNION select * from jobs start with city = (select city from people where name = 'Jane') and title = (select title from people where name = 'Jane') connect by prior parent_title = title How else can I get a distinct list (or I could wrap it with a distinct if not possible) of all the jobs which are above the three people I specified?

  Read the article

• Bulk retrieval in HQL: could not execute native bulk manipulation query

  - by user179056

  Hello, We are getting a "could not execute native bulk manipulation query" error in HQL When we execute a query which is something like this. String query = "Select person.id from Person person where"; String bindParam = ""; List subLists = getChucnkedList(dd); for(int i = 0 ; i < subLists.size() ;i++){ bindParam = bindParam + " person.id in (:param" + i + ")"; if (i < subLists.size() - 1 ) { bindParam = bindParam + " OR " ; } } query = query + bindParam; final Query query1 = session.createQuery(query.toString()); for(int i = 0 ; i < subLists.size() ;i++){ query1.setParameterList("param" + i, subLists.get(i)); } List personIdsList = query1.list(); Basically to avoid the limit on IN clause in terms of number of ids which can be inserted (not more than 1000), we have created sublists of ids of not more than 1000 in number. We are using bind parameters to bind each sublist. However we still get error "could not execute native bulk manipulation query" How does one avoid the problem of limited parameters possible in IN query when parameters passed are more than 1000? regards Sameer

  Read the article

• Query Level 2 Caching throwing ClassCastException

  - by Sameer Malhotra

  Hi, I am using JPA and Hibernate for the database. I have configured (EHCacache) second level cache and query level cache, but just to make sure that caching is working I was trying to get the statistics which is throwing class cast exception.Any help will be highly appreciated. My main goal is to see all the objects which have been cached to make sure that the caching is working properly. Here is the code: public List<CodeValue> findByCodetype(String propertyName) { try { final String queryString = "select model from CodeValue model where model.codetype" + "= :propertyValue" + " order by model.code"; Query query = em.createQuery(queryString); query.setHint("org.hibernate.cacheable", true); query.setHint("org.hibernate.cacheRegion", "query.findByCodetype"); query.setParameter("propertyValue", propertyName); List resultList = query.getResultList(); org.hibernate.Session session = (Session) em.getDelegate(); SessionFactory sessionFactory = session.getSessionFactory(); Map cacheEntries = sessionFactory.getStatistics() .getSecondLevelCacheStatistics("query.findByCodetype") .getEntries(); logger.info("The statistics are: " + cacheEntries); return resultList; } catch (RuntimeException re) { logger.error("findByCodetype failed in trauma patient", re); throw re; } } The error is existing right when I am trying to print the statistics. Below is exception: [6/7/10 19:23:17:059 GMT] 00000034 SystemOut O java.lang.ClassCastException: org.hibernate.cache.QueryKey incompatible with org.hibernate.cache.CacheKey at org.hibernate.stat.SecondLevelCacheStatistics.getEntries(SecondLevelCacheStatistics.java:51) at com.idph.trauma.registry.service.TraumaPatientDAO.findByCodetype(TraumaPatientDAO.java:439) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:64) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:615) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) at $Proxy209.findByCodetype(Unknown Source) Do you know what's going on?

  Read the article

• Codeigniter Pagination: Run the Query Twice?

  - by Frank

  I'm using codeigniter and the pagination class. This is such a basic question, but I need to make sure I'm not missing something. In order to get the config items necessary to paginate results getting them from a MySQL database it's basically necessary to run the query twice is that right? In other words, you have to run the query to determine the total number of records before you can paginate. So I'm doing it like: Do this query to get number of results $this->db->where('something', $something); $query = $this->db->get('the_table_name'); $num_rows = $query->num_rows(); Then I'll have to do it again to get the results with the limit and offset. Something like: $this->db->where('something', $something); $this->db->limit($limit, $offset); $query = $this->db->get('the_table_name'); if($query->num_rows()){ foreach($query->result_array() as $row){ ## get the results here } } I just wonder if I'm actually doing this right in that the query always needs to be run twice? The queries I'm using are much more complex than what is shown above.

  Read the article

• Why does my Doctrine DBAL query return no results when quoted?

  - by braveterry

  I'm using the Doctrine DataBase Abstraction Layer (DBAL) to perform some queries. For some reason, when I quote a parameter before passing it to the query, I get back no rows. When I pass it unquoted, it works fine. Here's the relevant snippet of code I'm using: public function get($game) { load::helper('doctrinehelper'); $conn = doctrinehelper::getconnection(); $statement = $conn->prepare('SELECT games.id as id, games.name as name, games.link_url, games.link_text, services.name as service_name, image_url FROM games, services WHERE games.name = ? AND services.key = games.service_key'); $quotedGame = $conn->quote($game); load::helper('loghelper'); $logger = loghelper::getLogger(); $logger->debug("Quoted Game: $quotedGame"); $logger->debug("Unquoted Game: $game"); $statement->execute(array($quotedGame)); $resultsArray = $statement->fetchAll(); $logger->debug("Number of rows returned: " . count($resultsArray)); return $resultsArray; } Here's what the log shows: 01/01/11 17:00:13,269 [2112] DEBUG root - Quoted Game: 'Diablo II Lord of Destruction' 01/01/11 17:00:13,269 [2112] DEBUG root - Unquoted Game: Diablo II Lord of Destruction 01/01/11 17:00:13,270 [2112] DEBUG root - Number of rows returned: 0 If I change this line: $statement->execute(array($quotedGame)); to this: $statement->execute(array($game)); I get this in the log: 01/01/11 16:51:42,934 [2112] DEBUG root - Quoted Game: 'Diablo II Lord of Destruction' 01/01/11 16:51:42,935 [2112] DEBUG root - Unquoted Game: Diablo II Lord of Destruction 01/01/11 16:51:42,936 [2112] DEBUG root - Number of rows returned: 1 Have I fat-fingered something?

  Read the article

• How to store date into Mysql database with play framework in scala?

  - by Rahul Kulhari

  I am working with play framework with scala and what am i doing : login page to login into web app sign up page to register into web app after login i want to store all databases values to user what i want to do: when user register for web app then i want to store user values into database with current time and date but my form is giving error. error: List(FormError(dates,error.required,List())),None) controllers/Application.scala object Application extends Controller { val ta:Form[Keyword] = Form( mapping( "id" -> ignored(NotAssigned:Pk[Long]), "word" -> nonEmptyText, "blog" -> nonEmptyText, "cat" -> nonEmptyText, "score"-> of[Long], "summaryId"-> nonEmptyText, "dates" -> date("yyyy-MM-dd HH:mm:ss") )(Keyword.apply)(Keyword.unapply) ) def index = Action { Ok(html.index(ta)); } def newTask= Action { implicit request => ta.bindFromRequest.fold( errors => {println(errors) BadRequest(html.index(errors))}, keywo => { Keyword.create(keywo) Ok(views.html.data(Keyword.all())) } ) } models/keyword.scala case class Keyword(id: Pk[Long],word: String,blog: String,cat: String,score: Long, summaryId: String,dates: Date ) object Keyword { val keyw = { get[Pk[Long]]("keyword.id") ~ get[String]("keyword.word")~ get[String]("keyword.blog")~ get[String]("keyword.cat")~ get[Long]("keyword.score") ~ get[String]("keyword.summaryId")~ get[Date]("keyword.dates") map { case id~blog~cat~word~score~summaryId~dates => Keyword(id,word,blog,cat,score, summaryId,dates) } } def all(): List[Keyword] = DB.withConnection { implicit c => SQL("select * from keyword").as(Keyword.keyw *) } def create(key: Keyword){DB.withConnection{implicit c=> SQL("insert into keyword values({word},{blog}, {cat}, {score},{summaryId},{dates})").on('word-> key.word,'blog->key.blog, 'cat -> key.cat, 'score-> key.score, 'summaryId -> key.summaryId, 'dates->new Date()).executeUpdate } } views/index.scala.html @(taskForm: Form[Keyword]) @import helper._ @main("Todo list") { @form(routes.Application.newTask) { @inputText(taskForm("word")) @inputText(taskForm("blog")) @inputText(taskForm("cat")) @inputText(taskForm("score")) @inputText(taskForm("summaryId")) <input type="submit"> <a href="">Go Back</a> } } please give me some idea to store date into mysql databse and date is not a field of form

  Read the article

• How can i get rid of 'ORA-01489: result of string concatenation is too long' in this query?

  - by core_pro

  this query gets the dominating sets in a network. so for example given a network A<----->B B<----->C B<----->D C<----->E D<----->C D<----->E F<----->E it returns B,E B,F A,E but it doesn't work for large data because i'm using string methods in my result. i have been trying to remove the string methods and return a view or something but to no avail With t as (select 'A' as per1, 'B' as per2 from dual union all select 'B','C' from dual union all select 'B','D' from dual union all select 'C','B' from dual union all select 'C','E' from dual union all select 'D','C' from dual union all select 'D','E' from dual union all select 'E','C' from dual union all select 'E','D' from dual union all select 'F','E' from dual) ,t2 as (select distinct least(per1, per2) as per1, greatest(per1, per2) as per2 from t union select distinct greatest(per1, per2) as per1, least(per1, per2) as per1 from t) ,t3 as (select per1, per2, row_number() over (partition by per1 order by per2) as rn from t2) ,people as (select per, row_number() over (order by per) rn from (select distinct per1 as per from t union select distinct per2 from t) ) ,comb as (select sys_connect_by_path(per,',')||',' as p from people connect by rn > prior rn ) ,find as (select p, per2, count(*) over (partition by p) as cnt from ( select distinct comb.p, t3.per2 from comb, t3 where instr(comb.p, ','||t3.per1||',') > 0 or instr(comb.p, ','||t3.per2||',') > 0 ) ) ,rnk as (select p, rank() over (order by length(p)) as rnk from find where cnt = (select count(*) from people) order by rnk ) select distinct trim(',' from p) as p from rnk where rnk.rnk = 1`

  Read the article

• Can I join two tables whereby the joined table is sorted by a certain column?

  - by Ferdy

  I'm not much of a database guru so I need some help on a query I'm working on. In my photo community project I want to richly visualize tags by not only showing the tag name and counter (# of images inside them), I also want to show a thumb of the most popular image inside the tag (most karma). The table setup is as follow: Image table holds basic image metadata, important is the karma field Imagefile table holds multiple entries per image, one for each format Tag table holds tag definitions Tag_map table maps tags to images In my usual trial and error query authoring I have come this far: SELECT * FROM (SELECT tag.name, tag.id, COUNT(tag_map.tag_id) as cnt FROM tag INNER JOIN tag_map ON (tag.id = tag_map.tag_id) INNER JOIN image ON tag_map.image_id = image.id INNER JOIN imagefile on image.id = imagefile.image_id WHERE imagefile.type = 'smallthumb' GROUP BY tag.name ORDER BY cnt DESC) as T1 WHERE cnt > 0 ORDER BY cnt DESC [column clause of inner query snipped for the sake of simplicity] This query gives me somewhat what I need. The outer query makes sure that only tags are returned for which there is at least 1 image. The inner query returns the tag details, such as its name, count (# of images) and the thumb. In addition, I can sort the inner query as I want (by most images, alphabetically, most recent, etc) So far so good. The problem however is that this query does not match the most popular image (most karma) of the tag, it seems to always take the most recent one in the tag. How can I make sure that the most popular image is matched with the tag?

  Read the article

• MSQL upgrade on Ubuntu - any heads ups?

  - by Rob Sedge

  I am needing to upgrade MYSQL on Ubuntu, it is a production server and naturally cautious. My many googles look to be essentially saying that I need to : 1) Backup my current mysql database and tables/data 2) Uninstall current mysql 3) Install new MYSQL 5+ 4) Restore Databases/ tables and data 5) Hope and Pray I got it right ?? Something doesn't seem right, sounds like a lot of down time and risk Am I missing something / or any simple solutions? Upgrading from mSQL 4 to 5 on Ubuntu 10 Many Thanks, Rob

  Read the article

• How does C#'s DateTime.Now affect query plan caching in SQL Server?

  - by Bill Paetzke

  Given: Let's say we have a stored procedure. It reports data back to a user on a webpage. The user can set a date range. If the user sets today's date as the "end date," which includes today's data, the web app passes DateTime.Now to the sql proc. Let's say that one user runs a report--5/1/2010 to now--over and over several times. On the webpage, the user sees "5/1/2010" to "5/4/2010." But the web app passes DateTime.Now to the sql proc as the end date. So, the end date in the proc will always be different, although the user is querying a similar date range. Assume the number of records in the table and number of users are large. So any performance gains matter. Hence the importance of the question. Question: Does passing DateTime.Now as a parameter to a proc prevent SQL Server from caching the query plan? If so, then is the web app missing out on huge performance gains? Possible Solution: I thought DateTime.Today.AddDays(1) would be a possible solution. It would allow the user to get the latest data and always pass the same end date to the sql proc--"5/5/2010" in this case. Please speak to this as well. Sample proc and execution (if that helps to understand): CREATE PROCEDURE GetFooData @StartDate datetime @EndDate datetime AS SELECT * FROM Foo WHERE LogDate >= @StartDate AND LogDate < @EndDate Here's a sample execution using DateTime.Now: EXEC GetFooData '2010-05-01', '2010-05-04 15:41:27' -- passed in DateTime.Now Here's a sample execution using DateTime.Today.AddDays(1) EXEC GetFooData '2010-05-01', '2010-05-05' -- passed in DateTime.Today.AddDays(1) The same data is returned for both procs, since the current time is: 2010-05-04 15:41:27.

  Read the article

• Delaying LINQ to SQL Select Query Execution

  - by Maxim Z.

  I'm building an ASP.NET MVC site that uses LINQ to SQL. In my search method that has some required and some optional parameters, I want to build a LINQ query while testing for the existence of those optional parameters. Here's what I'm currently thinking: using(var db = new DBDataContext()) { IQueryable<Listing> query = null; //Handle required parameter query = db.Listings.Where(l => l.Lat >= form.bounds.extent1.latitude && l.Lat <= form.bounds.extent2.latitude); //Handle optional parameter if (numStars != null) query = query.Where(l => l.Stars == (int)numStars); //Other parameters... //Execute query (does this happen here?) var result = query.ToList(); //Process query... Will this implementation "bundle" the where clauses and then execute the bundled query? If not, how should I implement this feature? Also, is there anything else that I can improve? Thanks in advance.

  Read the article

• How does DateTime.Now affect query plan caching in SQL Server?

  - by Bill Paetzke

  Question: Does passing DateTime.Now as a parameter to a proc prevent SQL Server from caching the query plan? If so, then is the web app missing out on huge performance gains? Possible Solution: I thought DateTime.Today.AddDays(1) would be a possible solution. It would pass the same end-date to the sql proc (per day). And the user would still get the latest data. Please speak to this as well. Given Example: Let's say we have a stored procedure. It reports data back to a user on a webpage. The user can set a date range. If the user sets today's date as the "end date," which includes today's data, the web app passes DateTime.Now to the sql proc. Let's say that one user runs a report--5/1/2010 to now--over and over several times. On the webpage, the user sees 5/1/2010 to 5/4/2010. But the web app passes DateTime.Now to the sql proc as the end date. So, the end date in the proc will always be different, although the user is querying a similar date range. Assume the number of records in the table and number of users are large. So any performance gains matter. Hence the importance of the question. Example proc and execution (if that helps to understand): CREATE PROCEDURE GetFooData @StartDate datetime @EndDate datetime AS SELECT * FROM Foo WHERE LogDate >= @StartDate AND LogDate < @EndDate Here's a sample execution using DateTime.Now: EXEC GetFooData '2010-05-01', '2010-05-04 15:41:27' -- passed in DateTime.Now Here's a sample execution using DateTime.Today.AddDays(1) EXEC GetFooData '2010-05-01', '2010-05-05' -- passed in DateTime.Today.AddDays(1) The same data is returned for both procs, since the current time is: 2010-05-04 15:41:27.

  Read the article

• MySQL developer here -- Nesting with select * finicky in Oracle 10g?

  - by John Sullivan

  I'm writing a simple diagnostic query then attempting to execute it in the Oracle 10g SQL Scratchpad. EDIT: It will not be used in code. I'm nesting a simple "Select *" and it's giving me errors. In the SQL Scratchpad for Oracle 10g Enterprise Manager Console, this statement runs fine. SELECT * FROM v$session sess, v$sql sql WHERE sql.sql_id(+) = sess.sql_id and sql.sql_text <> ' ' If I try to wrap that up in Select * from () tb2 I get an error, "ORA-00918: Column Ambiguously Defined". I didn't think that could ever happen with this kind of statement so I am a bit confused. select * from (SELECT * FROM v$session sess, v$sql sql WHERE sql.sql_id(+) = sess.sql_id and sql.sql_text <> ' ') tb2 You should always be able to select * from the result set of another select * statement using this structure as far as I'm aware... right? Is Oracle/10g/the scratchpad trying to force me to accept a certain syntactic structure to prevent excessive nesting? Is this a bug in scratchpad or something about how oracle works?

  Read the article

• How should I name my SQL query files? Should I use some methodology?

  - by Mehper C. Palavuzlar

  We have an Oracle 10g database (a huge one) in our company, and I provide employees with data upon their requests. My problem is, I save almost every SQL query I wrote, and now my list has grown too much. I want to organize and rename these .sql files so that I can find the one I want easily. At the moment, I'm using some folders named as Sales Dept, Field Team, Planning Dept, Special etc. and under those folders there are .sql files like Delivery_sales_1, Delivery_sales_2, ... Sent_sold_lostsales_endpoints, ... Sales_provinces_period, Returnrates_regions_bymonths, ... Jack_1, Steve_1, Steve_2, ... I try to name the files regarding their content but this makes file names longer and does not completely meet my needs. Sometimes someone comes and demands a special report, and I give the file his name, but this is also not so good. I know duplicates or very similar files are growing in time but I don't have control over them. Can you show me the right direction to rename all these files and folders and organize my queries for easy and better control? TIA.

  Read the article

• Why can't you return a List from a Compiled Query?

  - by Andrew

  I was speeding up my app by using compiled queries for queries which were getting hit over and over. I tried to implement it like this: Function Select(ByVal fk_id As Integer) As List(SomeEntity) Using db As New DataContext() db.ObjectTrackingEnabled = False Return CompiledSelect(db, fk_id) End Using End Function Shared CompiledSelect As Func(Of DataContext, Integer, List(Of SomeEntity)) = _ CompiledQuery.Compile(Function(db As DataContext, fk_id As Integer) _ (From u In db.SomeEntities _ Where u.SomeLinkedEntity.ID = fk_id _ Select u).ToList()) This did not work and I got this error message: Type : System.ArgumentNullException, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 Message : Value cannot be null. Parameter name: value However, when I changed my compiled query to return IQueryable instead of List like so: Function Select(ByVal fk_id As Integer) As List(SomeEntity) Using db As New DataContext() db.ObjectTrackingEnabled = False Return CompiledSelect(db, fk_id).ToList() End Using End Function Shared CompiledSelect As Func(Of DataContext, Integer, IQueryable(Of SomeEntity)) = _ CompiledQuery.Compile(Function(db As DataContext, fk_id As Integer) _ From u In db.SomeEntities _ Where u.SomeLinkedEntity.ID = fk_id _ Select u) It worked fine. Can anyone shed any light as to why this is? BTW, compiled queries rock! They sped up my app by a factor of 2.

  Read the article

• PHP DELETE immediately after select

  - by teehoo

  I have a PHP server script that SELECTs some data from a MySQL database. As soon as I have the result from mysql_query and mysql_fetch_assoc stored in my own local variables, I want to delete the row I just selected. The problem with this approach is that it seems that PHP has done pass-by-reference to my local variables instead of pass-by-value, and my local variables become undefined after the delete command. Is there anyway to get around this? Here is my code: $query="SELECT id, peerID, name FROM names WHERE peer = $userID AND docID = '$docID' AND seqNo = $nid"; $result = mysql_query($query); if (!$result) self::logError("FAIL:1 getUsersNamesUpdate() query: ".$query."\n"); if (mysql_num_rows($result) == 0) return array(); $row = mysql_fetch_assoc($result); $result = array(); $result["id"] = $row["id"]; $result["peerID"] = $row["peerID"]; $result["name"] = $row["name"]; $query="DELETE FROM names WHERE id = $result[id];"; $result = mysql_query($query); if (!$result) self::logError("FAIL:2 getUsersNamesUpdate() query: ".$query."\n"); return $result;

  Read the article

• How do I exit a series of If / else conditions in a mysql trigger?

  - by ScArcher2

  I have a trigger that checks to see if certain fields changed during the update. If any of these fields changed I update another table. I'd like to "break" out of the if conditions as soon as I know that something changed. Is there a way to do this within a MySQL Trigger? What I have works, but It seems inefficient. CREATE TRIGGER profile_trigger BEFORE UPDATE ON profile FOR EACH ROW BEGIN DECLARE changed INTEGER; SET changed = 0; IF STRCMP(NEW.first_name, OLD.first_name) <> 0 THEN SET changed = 1; ELSEIF STRCMP(NEW.last_name, OLD.last_name) <> 0 THEN SET changed = 1; ELSEIF STRCMP(NEW.maiden_name, OLD.maiden_name) <> 0 THEN SET changed = 1; ELSEIF STRCMP(NEW.suffix, OLD.suffix) <> 0 THEN SET changed = 1; ELSEIF STRCMP(NEW.title, OLD.title) <> 0 THEN SET changed = 1; ELSEIF STRCMP(NEW.gender, OLD.gender) <> 0 THEN SET changed = 1; ELSEIF STRCMP(NEW.street, OLD.street) <> 0 THEN SET changed = 1; ELSEIF STRCMP(NEW.street2, OLD.street2) <> 0 THEN SET changed = 1; ELSEIF STRCMP(NEW.city, OLD.city) <> 0 THEN SET changed = 1; ELSEIF STRCMP(NEW.state, OLD.state) <> 0 THEN SET changed = 1; ELSEIF STRCMP(NEW.zip, OLD.zip) <> 0 THEN SET changed = 1; ELSEIF STRCMP(NEW.home_phone, OLD.home_phone) <> 0 THEN SET changed = 1; ELSEIF NEW.date_of_birth <> OLD.date_of_birth THEN SET changed = 1; END IF; IF changed > 0 THEN update other_table set updated = CURRENT_TIMESTAMP where id = NEW.id; END IF; END; |

  Read the article

• Implementing Tagging System with PHP and mySQL. Caching help!!!

  - by Hamid Sarfraz

  With reference to this post: http://stackoverflow.com/questions/2122546/how-to-implement-tag-counting I have implemented the suggested 3 table tagging system completely. To count the number of Articles per tag, i am using another column named tagArticleCount in the tag definition table. (other columns are tagId, tagText, tagUrl, tagArticleCount). If i implement realtime editing of this table, so that whenever user adds another tag to article or deletes an existing tag, the tag_definition_table is updated to update the counter of the added/removed tag. This will cost an extra query each time any modification is made. (at the same time, related link entry for tag and article is deleted from tagLinkTable). An alternative to this is not allowing any real time editing to the counter, instead use CRONs to update counter of each tag after a specified time period. Here comes the problem that i want to discuss. This can be seen as caching the article count in database. Can you please help me find a way to present the articles in a list when a tag is explored and when the article counter for that tag is not up to date. For example: 1. Counter shows 50 articles, but there are infact 55 entries in the tag link table (that links tags and articles). 2. Counter shows 50 articles, but there are infact 45 extries in the tag link table. How to handle these 2 scenerios given in example. I am going to use APC to keep cache of these counters. Consider it too in your solution. Also discuss performance in the realtime / CRONNED counter updates.

  Read the article

• How do I choose what and when to cache data with ob_start rather than query the database?

  - by Tim Santeford

  I have a home page that has several independent dynamic parts. The parts consist of a list of recent news from the company, a site statistics panel, and the online status of certain employees. The recent news changes monthly, site statistics change daily, and online statuses change on a per minute bases. I would like to cache these panels so that the db is not hit on every page load. Is using ob_start() then ob_get_contents() to cache these parts to a file the correct way to do this or is there a better method in PHP5 for doing this? In asking this question I'm trying to answer these additional questions: How can I determine the correct approach for caching this data without doing extensive benchmarking? Does it make sense to cache these parts in different files and then join them together per requests or should I re-query the data and cache once per minute? I'm looking for a rule of thumb for planning pages and for situations where doing testing is not cost effective (The client is not paying enough for it I mean). Thanks!

  Read the article

< Previous Page | 284 285 286 287 288 289 290 291 292 293 294 295  | Next Page >