Search Results

Search found 4685 results on 188 pages for 'queries'.

Page 163/188 | < Previous Page | 159 160 161 162 163 164 165 166 167 168 169 170  | Next Page >

• What's the best way to get a bunch of rows from MySQL if you have an array of integer primary keys?

  - by Evan P.

  I have a MySQL table with an auto-incremented integer primary key. I want to get a bunch of rows from the table based on an array of integers I have in memory in my program. The array ranges from a handful to about 1000 items. What's the most efficient query syntax to get the rows? I can think of a few: "SELECT * FROM thetable WHERE id IN (1, 2, 3, 4, 5)" (this is what I do now) "SELECT * FROM thetable where id = 1 OR id = 2 OR id = 3" Multiple queries of the form "SELECT * FROM thetable WHERE id = 1". Probably the most friendly to the query cache, but expensive due to having lots of query parsing. A union, like "SELECT * FROM thetable WHERE id = 1 UNION SELECT * FROM thetable WHERE id = 2 ..." I'm not sure if MySQL caches the results of each query; it's also the most verbose format. I think using the NoSQL interface in MySQL 5.6+ would be the most efficient way to do this, but I'm not yet up to MySQL 5.6.

  Read the article

• What technologies/tools do people use to implement live websites ?

  - by MadSeb

  Hi, I have the following situation: -I have a server A hooked up to a piece of hardware that sends values and information out of every second. Programs on the server machine can read these values. This server A is in a very remote location so Internet connection is very slow and not reliable but the connection does exist. Let's say it's a weather station in the Arctic. -Users from the home location want to monitorize the weather values somehow. Well, the users can use a remote desktop connection the server A but that would be too too slow. My idea is somehow to have a website on a web server (let's call the webserver - B and B is in a home location ) and make the server A connect to the server B and somehow send values and the web application reads the values and displays them....... but how to do such a system ?? I know I can use MySQL and have the server A connect to a SQL server on server B and send INSERT queries and have the web application running on server B constantly read from the SQL server but I think that would be way way too slow and I think there has to be a better solution. Any ideas ? BTW. The users should be able to send information to the weather station from the website as well ( so an ADMIN user should be allowed to shut down the weather station from the website or whatever) Best regards, MadSeb

  Read the article

• SQL connection to database repeating

  - by user175084

  ok now i am using the SQL database to get the values from different tables... so i make the connection and get the values like this: DataTable dt = new DataTable(); SqlConnection connection = new SqlConnection(); connection.ConnectionString = ConfigurationManager.ConnectionStrings["XYZConnectionString"].ConnectionString; connection.Open(); SqlCommand sqlCmd = new SqlCommand("SELECT * FROM Machines", connection); SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd); sqlCmd.Parameters.AddWithValue("@node", node); sqlDa.Fill(dt); connection.Close(); so this is one query on the page and i am calling many other queries on the page. So do i need to open and close the connection everytime...??? also if not this portion is common in all: DataTable dt = new DataTable(); SqlConnection connection = new SqlConnection(); connection.ConnectionString = ConfigurationManager.ConnectionStrings["XYZConnectionString"].ConnectionString; connection.Open(); can i like put it in one function and call it instead.. the code would look cleaner... i tried doing that but i get errors like: Connection does not exist in the current context. any suggestions??? thanks

  Read the article

• Numerical calculations in Prolog

  - by user8472

  While reading SICP I came across logic programming chapter 4.4. Then I started looking into the Prolog programming language and tried to understand some simple assignments in Prolog. I found that Prolog seems to have troubles with numerical calculations. Here is the computation of a factorial in standard Prolog: f(0, 1). f(A, B) :- A > 0, C is A-1, f(C, D), B is A*D. The issues I find is that I need to introduce two auxiliary variables (C and D), a new syntax (is) and that the problem is non-reversible (i.e., f(5,X) works as expected, but f(X,120) does not). Naively, I expect that at the very least C is A-1, f(C, D) above may be replaced by f(A-1,D), but even that does not work. My question is: Why do I need to do this extra "stuff" in numerical calculations but not in other queries? I do understand (and SICP is quite clear about it) that in general information on "what to do" is insufficient to answer the question of "how to do it". So the declarative knowledge in (at least some) math problems is insufficient to actually solve these problems. But that begs the next question: How does this extra "stuff" in Prolog help me to restrict the formulation to just those problems where "what to do" is sufficient to answer "how to do it"?

  Read the article

• Mysql query in drupal database - groupwise maximum with duplicate data

  - by nselikoff

  I'm working on a mysql query in a Drupal database that pulls together users and two different cck content types. I know people ask for help with groupwise maximum queries all the time... I've done my best but I need help. This is what I have so far: # the artists SELECT users.uid, users.name AS username, n1.title AS artist_name FROM users LEFT JOIN users_roles ur ON users.uid=ur.uid INNER JOIN role r ON ur.rid=r.rid AND r.name='artist' LEFT JOIN node n1 ON n1.uid = users.uid AND n1.type = 'submission' WHERE users.status = 1 ORDER BY users.name; This gives me data that looks like: uid username artist_name 1 foo Joe the Plumber 2 bar Jane Doe 3 baz The Tooth Fairy Also, I've got this query: # artwork SELECT n.nid, n.uid, a.field_order_value FROM node n LEFT JOIN content_type_artwork a ON n.nid = a.nid WHERE n.type = 'artwork' ORDER BY n.uid, a.field_order_value; Which gives me data like this: nid uid field_order_value 1 1 1 2 1 3 3 1 2 4 2 NULL 5 3 1 6 3 1 Additional relevant info: nid is the primary key for an Artwork every Artist has one or more Artworks valid data for field_order_value is NULL, 1, 2, 3, or 4 field_order_value is not necessarily unique per Artist - an Artist could have 4 Artworks all with field_order_value = 1. What I want is the row with the minimum field_order_value from my second query joined with the artist information from the first query. In cases where the field_order_value is not valuable information (either because the Artist has used duplicate values among their Artworks or left that field NULL), I would like the row with the minimum nid from the second query.

  Read the article

• What's the best way to "shuffle" a table of database records?

  - by Darth

  Say that I have a table with a bunch of records, which I want to randomly present to users. I also want users to be able to paginate back and forth, so I have to perserve some sort of order, at least for a while. The application is basically only AJAX and it uses cache for already visited pages, so even if I always served random results, when the user tries to go back, he will get the previous page, because it will load from the local cache. The problem is, that if I return only random results, there might be some duplicates. Each page contains 6 results, so to prevent this, I'd have to do something like WHERE id NOT IN (1,2,3,4 ...) where I'd put all the previously loaded IDs. Huge downside of that solution is that it won't be possible to cache anything on the server side, as every user will request different data. Alternate solution might be to create another column for ordering the records, and shuffle it every insert time unit here. The problem here is, I'd need to set random number out of a sequence to every record in the table, which would take as many queries as there are records. I'm using Rails and MySQL if that's of any relevance.

  Read the article

• Oracle User definied aggregate function for varray of varchar

  - by baju

  I am trying to write some aggregate function for the varray and I get this error code when I'm trying to use it with data from the DB: ORA-00600 internal error code, arguments: [kodpunp1], [], [], [], [], [], [], [], [], [], [], [] [koxsihread1], [0], [3989], [45778], [], [], [], [], [], [], [], [] Code of the function is really simple(in fact it does nothing ): create or replace TYPE "TEST_VECTOR" as varray(10) of varchar(20) ALTER TYPE "TEST_VECTOR" MODIFY LIMIT 4000 CASCADE create or replace type Test as object( lastVector TEST_VECTOR, STATIC FUNCTION ODCIAggregateInitialize(sctx in out Test) return number, MEMBER FUNCTION ODCIAggregateIterate(self in out Test, value in TEST_VECTOR) return number, MEMBER FUNCTION ODCIAggregateMerge(self IN OUT Test, ctx2 IN Test) return number, MEMBER FUNCTION ODCIAggregateTerminate(self IN Test, returnValue OUT TEST_VECTOR, flags IN number) return number ); create or replace type body Test is STATIC FUNCTION ODCIAggregateInitialize(sctx in out Test) return number is begin sctx := Test(TEST_VECTOR()); return ODCIConst.Success; end; MEMBER FUNCTION ODCIAggregateIterate(self in out Test, value in TEST_VECTOR) return number is begin self.lastVector := value; return ODCIConst.Success; end; MEMBER FUNCTION ODCIAggregateMerge(self IN OUT Test, ctx2 IN Test) return number is begin return ODCIConst.Success; end; MEMBER FUNCTION ODCIAggregateTerminate(self IN Test, returnValue OUT TEST_VECTOR, flags IN number) return number is begin returnValue := self.lastVector; return ODCIConst.Success; end; end; create or replace FUNCTION test_fn (input TEST_VECTOR) RETURN TEST_VECTOR PARALLEL_ENABLE AGGREGATE USING Test; Next I create some test data: create table t1_test_table( t1_id number not null, t1_value TEST_VECTOR not null, Constraint PRIMARY_KEY_1 PRIMARY KEY (t1_id) ) Next step is to put some data to the table insert into t1_test_table (t1_id,t1_value) values (1,TEST_VECTOR('x','y','z')) Now everything is prepared to perform queries: Select test_fn(TEST_VECTOR('y','x')) from dual Query above work well Select test_fn(t1_value) from t1_test_table where t1_id = 1 Version of Oracle DBMS I use: 11.2.0.3.0 Does anyone tried do such a thing? What can be the reason that it does not work? How to solve it? Thanks in advance for help.

  Read the article

• Hundreds of custom UserControls create thousands of USER Objects

  - by Andy Blackman

  I'm creating a dashboard application that shows hundreds of "items" on a FlowLayoutPanel. Each "item" is a UserControl that is made up of 12 or labels. My app queries a database and then creates an "item" instance for each record, populating ethe labels and textboxes with data before adding it to the FlowLayoutPanel. After adding about 560 items to the panel, I noticed that the USER Objects count in my Task Manager had gone up to about 7300, which was much much larger than any other app on my machine. I did a quick spot of mental arithmetic (OK, I might have used calc.exe) and figured that 560 * 13 (12 labels plus the UserControl itself) is 7280. So that suddenly gave away where all the objects were coming from... Knowing that there is a 10,000 USER object limit before windows throws in the towel, I'm trying to figure better ways of drawing these items onto the FlowLayoutPanel. My ideas so far are as follows: 1) User-draw the "item", using graphics.DrawText and DrawImage in place of many of the labels. I'm hoping that this will mean 1 item = 1 USER Object, not 13. 2) Have 1 instance of the "item", then for each record, populate the instance and use the Control.DrawToBitmap() method to grab an image and then use that in the FlowLayoutPanel (or similar) So... Does anyone have any other suggestions ??? P.S. It's a zoomable interface, so I have already ruled out "Paging" as there is a requirement to see all items at once :( Thanks everyone.

  Read the article

• Combining two-part SQL query into one query

  - by user332523

  Hello, I have a SQL query that I'm currently solving by doing two queries. I am wondering if there is a way to do it in a single query that makes it more efficient. Consider two tables: Transaction_Entries table and Transactions, each one defined below: Transactions - id - reference_number (varchar) Transaction_Entries - id - account_id - transaction_id (references Transactions table) Notes: There are multiple transaction entries per transaction. Some transactions are related, and will have the same reference_number string. To get all transaction entries for Account X, then I would do SELECT E.*, T.reference_number FROM Transaction_Entries E JOIN Transactions T ON (E.transaction_id=T.id) where E.account_id = X The next part is the hard part. I want to find all related transactions, regardless of the account id. First I make a list of all the unique reference numbers I found in the previous result set. Then for each one, I can query all the transactions that have that reference number. Assume that I hold all the rows from the previous query in PreviousResultSet UniqueReferenceNumbers = GetUniqueReferenceNumbers(PreviousResultSet) // in Java foreach R in UniqueReferenceNumbers // in Java SELECT * FROM Transaction_Entries where transaction_id IN (SELECT * FROM Transactions WHERE reference_number=R Any suggestions how I can put this into a single efficient query?

  Read the article

• PHP OOP: Avoid Singleton/Static Methods in Domain Model Pattern

  - by sunwukung

  I understand the importance of Dependency Injection and its role in Unit testing, which is why the following issue is giving me pause: One area where I struggle not to use the Singleton is the Identity Map/Unit of Work pattern (Which keeps tabs on Domain Object state). //Not actual code, but it should demonstrate the point class Monitor{//singleton construction omitted for brevity static $members = array();//keeps record of all objects static $dirty = array();//keeps record of all modified objects static $clean = array();//keeps record of all clean objects } class Mapper{//queries database, maps values to object fields public function find($id){ if(isset(Monitor::members[$id]){ return Monitor::members[$id]; } $values = $this->selectStmt($id); //field mapping process omitted for brevity $Object = new Object($values); Monitor::new[$id]=$Object return $Object; } $User = $UserMapper->find(1);//domain object is registered in Id Map $User->changePropertyX();//object is marked "dirty" in UoW // at this point, I can save by passing the Domain Object back to the Mapper $UserMapper->save($User);//object is marked clean in UoW //but a nicer API would be something like this $User->save(); //but if I want to do this - it has to make a call to the mapper/db somehow $User->getBlogPosts(); //or else have to generate specific collection/object graphing methods in the mapper $UserPosts = $UserMapper->getBlogPosts(); $User->setPosts($UserPosts); Any advice on how you might handle this situation? I would be loathe to pass/generate instances of the mapper/database access into the Domain Object itself to satisfy DI - At the same time, avoiding that results in lots of calls within the Domain Object to external static methods. Although I guess if I want "save" to be part of its behaviour then a facility to do so is required in its construction. Perhaps it's a problem with responsibility, the Domain Object shouldn't be burdened with saving. It's just quite a neat feature from the Active Record pattern - it would be nice to implement it in some way.

  Read the article

• PHP/MySQL time zone migration

  - by El Yobo

  I have an application that currently stores timestamps in MySQL DATETIME and TIMESTAMP values. However, the application needs to be able to accept data from users in multiple time zones and show the timestamps in the time zone of other users. As such, this is how I plan to amend the application; I would appreciate any suggestions to improve the approach. Database modifications All TIMESTAMPs will be converted to DATETIME values; this is to ensure consistency in approach and to avoid having MySQL try to do clever things and convert time zones (I want to keep the conversion in PHP, as it involves less modification to the application, and will be more portable when I eventually manage to escape from MySQL). All DATETIME values will be adjusted to convert them to UTC time (currently all in Australian EST) Query modifications All usage of NOW() to be replaced with UTC_TIMESTAMP() in queries, triggers, functions, etc. Application modifications The application must store the time zone and preferred date format (e.g. US vs the rest of the world) All timestamps will be converted according to the user settings before being displayed All input timestamps will be converted to UTC according to the user settings before being input Additional notes Converting formats will be done at the application level for several main reasons The approach to converting time zones varies from DB to DB, so handing it there will be non-portable (and I really hope to be migrating away from MySQL some time in the not-to-distant future). MySQL TIMESTAMPs have limited ranges to the permitted dates (~1970 to ~2038) MySQL TIMESTAMPs have other undesirable attributes, including bizarre auto-update behaviour (if not carefully disabled) and sensitivity to the server zone settings (and I suspect I might screw these up when I migrate to Amazon later in the year). Is there anything that I'm missing here, or does anyone have better suggestions for the approach?

  Read the article

• many to many relationship mysql select

  - by zeina

  Let's consider 2 tables "schools" and "students". Now a student may belong to different schools in his life, and a school have many students. So this is a many to many example. A third table "links" specify the relation between student and school. Now to query this I do the following: Select sc.sid , -- stands for school id st.uid, -- stands for student id sc.sname, -- stands for school name st.uname, -- stands for student name -- select more data about the student joining other tables for that from students s left join links l on l.uid=st.uid -- l.uid stands for the student id on the links table left join schools sc on sc.sid=l.sid -- l.sid is the id of the school in the links table where st.uid=3 -- 3 is an example this query will return duplicate data for the user id if he has more than one school, so to fix this I added group by st.uid, yet I also need the list of school name related to the same user. Is there a way to do it with fixing the query I wrote instead of having 2 queries? So as example I want to have Luci of schools ( X, Y, Z, R, ...) etc

  Read the article

• Get the first and last posts in a thread

  - by Grampa

  I am trying to code a forum website and I want to display a list of threads. Each thread should be accompanied by info about the first post (the "head" of the thread) as well as the last. My current database structure is the following: threads table: id - int, PK, not NULL, auto-increment name - varchar(255) posts table: id - int, PK, not NULL, auto-increment thread_id - FK for threads The tables have other fields as well, but they are not relevant for the query. I am interested in querying threads and somehow JOINing with posts so that I obtain both the first and last post for each thread in a single query (with no subqueries). So far I am able to do it using multiple queries, and I have defined the first post as being: SELECT * FROM threads t LEFT JOIN posts p ON t.id = p.thread_id ORDER BY p.id LIMIT 0, 1 The last post is pretty much the same except for ORDER BY id DESC. Now, I could select multiple threads with their first or last posts, by doing: SELECT * FROM threads t LEFT JOIN posts p ON t.id = p.thread_id ORDER BY p.id GROUP BY t.id But of course I can't get both at once, since I would need to sort both ASC and DESC at the same time. What is the solution here? Is it even possible to use a single query? Is there any way I could change the structure of my tables to facilitate this? If this is not doable, then what tips could you give me to improve the query performance in this particular situation?

  Read the article

• Django Aggregation Across Reverse Relationship

  - by Tom

  Given these two models: class Profile(models.Model): user = models.ForeignKey(User, unique=True, verbose_name=_('user')) about = models.TextField(_('about'), blank=True) zip = models.CharField(max_length=10, verbose_name='zip code', blank=True) website = models.URLField(_('website'), blank=True, verify_exists=False) class ProfileView(models.Model): profile = models.ForeignKey(Profile) viewer = models.ForeignKey(User, blank=True, null=True) created = models.DateTimeField(auto_now_add=True) I want to get all profiles sorted by total views. I can get a list of profile ids sorted by total views with: ProfileView.objects.values('profile').annotate(Count('profile')).order_by('-profile__count') But that's just a dictionary of profile ids, which means I then have to loop over it and put together a list of profile objects. Which is a number of additional queries and still doesn't result in a QuerySet. At that point, I might as well drop to raw SQL. Before I do, is there a way to do this from the Profile model? ProfileViews are related via a ForeignKey field, but it's not as though the Profile model knows that, so I'm not sure how to tie the two together. As an aside, I realize I could just store views as a property on the Profile model and that may turn out to be what I do here, but I'm still interested in learning how to better use the Aggregation functions.

  Read the article

• "Most popular" GROUP BY in LINQ?

  - by tags2k

  Assuming a table of tags like the stackoverflow question tags: TagID (bigint), QuestionID (bigint), Tag (varchar) What is the most efficient way to get the 25 most used tags using LINQ? In SQL, a simple GROUP BY will do: SELECT Tag, COUNT(Tag) FROM Tags GROUP BY Tag I've written some LINQ that works: var groups = from t in DataContext.Tags group t by t.Tag into g select new { Tag = g.Key, Frequency = g.Count() }; return groups.OrderByDescending(g => g.Frequency).Take(25); Like, really? Isn't this mega-verbose? The sad thing is that I'm doing this to save a massive number of queries, as my Tag objects already contain a Frequency property that would otherwise need to check back with the database for every Tag if I actually used the property. So I then parse these anonymous types back into Tag objects: groups.OrderByDescending(g => g.Frequency).Take(25).ToList().ForEach(t => tags.Add(new Tag() { Tag = t.Tag, Frequency = t.Frequency })); I'm a LINQ newbie, and this doesn't seem right. Please show me how it's really done.

  Read the article

• why is Active Record firing extra query when I use Includes method to fetch data

  - by riddhi_agrawal

  I have the following model structure: class Group < ActiveRecord::Base has_many :group_products, :dependent => :destroy has_many :products, :through => :group_products end class Product < ActiveRecord::Base has_many :group_products, :dependent => :destroy has_many :groups, :through => :group_products end class GroupProduct < ActiveRecord::Base belongs_to :group belongs_to :product end I wanted to minimize my database queries so I decided to use includes.In the console I tried something like, groups = Group.includes(:products) my development logs show the following calls, Group Load (403.0ms) SELECT `groups`.* FROM `groups` GroupProduct Load (60.0ms) SELECT `group_products`.* FROM `group_products` WHERE (`group_products`.group_id IN (1,3,14,15,16,18,19,20,21,22,23,24,25,26,27,28,29,30,33,42,49,51)) Product Load (22.0ms) SELECT `products`.* FROM `products` WHERE (`products`.`id` IN (382,304,353,12,63,103,104,105,262,377,263,264,265,283,284,285,286,287,302,306,307,308,328,335,336,337,340,355,59,60,61,247,309,311,66,30,274,294,324,350,140,176,177,178,64,240,327,332,338,380,383,252,254,255,256,257,325,326)) Product Load (10.0ms) SELECT `products`.* FROM `products` WHERE (`products`.`id` = 377) LIMIT 1 I could analyze the initial three calls were necessary but don't get the reason why the last database call is made, Product Load (10.0ms) SELECT `products`.* FROM `products` WHERE (`products`.`id` = 377) LIMIT 1 Any idea why this is happening? Thanks in advance. :)

  Read the article

• Working with PHP and MySQL - need a good and secure design with OO design

  - by Andrew

  I am new to PHP- first time developer. I am working on my web application and it is nearly done; nevertheless, most of my sql was done directly via code using direct mysql requests. This is the way I approached it: In classes_db.php I declared the db settings and created methods that I use to open and close DB connections. I declare those objects on my regular pages: class classes_db { public $dbserver = 'server; public $dbusername = 'user'; public $dbpassword = 'pass'; public $dbname = 'db'; function openDb() { $dbhandle = mysql_connect($this->dbserver, $this->dbusername, $this->dbpassword); if (!$dbhandle) { die('Could not connect: ' . mysql_error()); } $selected = mysql_select_db($this->dbname, $dbhandle) or die("Could not select the database"); return $dbhandle; } function closeDb($con) { mysql_close($con); } } On my regular page, I do this: <?php require 'classes_db.php'; session_start(); //create instance of the DB class $db = new classes_db(); //get dbhandle $dbhandle = $db->openDb(); //process query $result = mysql_query("update user set username = '" . $usernameFromForm . "' where iduser= " . $_SESSION['user']->iduser); //close the connection if (isset($dbhandle)) { $db->closeDb($dbhandle); } ?> My questions is: how to do it right and make it OO and secure? I know that I need incorporate prepared queries- how to do it the best way? Please provide some code

  Read the article

• SQL query for an access database needed

  - by masfenix

  Hey guys, first off all sorry, i can't login using my yahoo provider. anyways I have this problem. Let me explain it to you, and then I'll show you a picture. I have a access db table. It has 'report id', 'recpient id', and 'recipient name' and 'report req'. What the table "means" is that do the user using that report still require it or can we decommission it. Here is how the data looks like (blocked out company userids and usernames): *check the link below, I cant post pictures cuz yahoo open id provider isnt working. So basically I need to have 3 select queries: 1) Select all the reports where for each report, ALL the users have said no to 'reportreq'. In plain English, i want a listing of all the reports that we have to decommission because no user wants it. 2) Select all the reports where the report is required, and the batchprintcopy is more then 0. This way we can see which report needs to be printed and save paper instead of printing all the reports. 3)A listing of all the reports where the reportreq field is empty. I think i can figure this one out myself. This is using Access/VBA and the data will be exported to an excel spreadsheet. I just a simple query if it exists, OR an alogorithm to do it quickly. I just tried making a "matrix" and it took about 2 hours to populate. https://docs.google.com/uc?id=0B2EMqbpeBpQkMTIyMzA5ZjMtMGQ3Zi00NzRmLWEyMDAtODcxYWM0ZTFmMDFk&hl=en_US

  Read the article

• How do I Order on common attribute of two models in the DB?

  - by Will

  If i have two tables Books, CDs with corresponding models. I want to display to the user a list of books and CDs. I also want to be able to sort this list on common attributes (release date, genre, price, etc.). I also have basic filtering on the common attributes. The list will be large so I will be using pagination in manage the load. items = [] items << CD.all(:limit => 20, :page => params[:page], :order => "genre ASC") items << Book.all(:limit => 20, :page => params[:page], :order => "genre ASC") re_sort(items,"genre ASC") Right now I am doing two queries concatenating them and then sorting them. This is very inefficient. Also this breaks down when I use paging and filtering. If I am on page 2 of how do I know what page of each table individual table I am really on? There is no way to determine this information without getting all items from each table. I have though that if I create a new Class called items that has a one to one relationship with either a Book or CD and do something like Item.all(:limit => 20, :page => params[:page], :include => [:books, :cds], :order => "genre ASC") However this gives back an ambiguous error. So can only be refined as Item.all(:limit => 20, :page => params[:page], :include => [:books, :cds], :order => "books.genre ASC") And does not interleave the books and CDs in a way that I want. Any suggestions.

  Read the article

• An Erroneous SQL Query makes browser hang until script timeout exceeded

  - by Jimbo

  I have an admin page in a Classic ASP web application that allows the admin user to run queries against the database (SQL Server 2000) Whats really strange is that if the query you send has an error in it (an invalid table join, a column you've forgotten to group by etc) the BROWSER hangs (CPU usage goes to maximum) until the SERVER script timeout is exceeded and then spits out a timeout exceeded error (server and browser are on different machines, so not sure how this happens!) I have tried this in IE 8 and FF 3 with the same result. If you run that same query (with errors) directly from SQL Enterprise Manager, it returns the real error immediately. Is this a security feature? Does anyone know how to turn it off? It even happens when the connection to the database is using 'sa' credentials so I dont think its a security setting :( Dim oRS Set oRS = Server.CreateObject("ADODB.Recordset") oRS.ActiveConnection = sConnectionString // run the query - this is for the admin only so doesnt check for sql safe commands etc. oRS.Open Request.Form("txtSQL") If Not oRS.EOF Then // list the field names from the recordset For i = 0 to oRS.Fields.Count - 1 Response.Write oRS.Fields(i).name & "&nbsp;" Next // show the data for each record in the recordset While Not oRS.EOF For i = 0 to oRS.Fields.Count - 1 Response.Write oRS.Fields(i).value & "&nbsp;" Next Response.Write "<br />" oRS.Movenext() Wend End If

  Read the article

• How do I write this GROUP BY in mysql UNION query

  - by user1652368

  Trying to group the results of two queries together. When I run this query: SELECT pr_id, pr_sbtcode, pr_sdesc, od_quantity, od_amount FROM ( SELECT `bgProducts`.`pr_id`, `bgProducts`.`pr_sbtcode`, `bgProducts`.`pr_sdesc`, SUM(`od_quantity`) AS `od_quantity`, SUM(`od_amount`) AS `od_amount`, MIN(UNIX_TIMESTAMP(`or_date`)) AS `or_date` FROM `bgOrderMain` JOIN `bgOrderData` JOIN `bgProducts` WHERE `bgOrderMain`.`or_id` = `bgOrderData`.`or_id` AND `od_pr` = `pr_id` AND UNIX_TIMESTAMP(`or_date`) >= '1262322000' AND UNIX_TIMESTAMP(`or_date`) <= '1346990399' AND (`pr_id` = '415' OR `pr_id` = '1088') GROUP BY `bgProducts`.`pr_id` UNION SELECT `bgProducts`.`pr_id`, `bgProducts`.`pr_sbtcode`, `bgProducts`.`pr_sdesc`,SUM(`od_quantity`) AS `od_quantity`, SUM(`od_amount`) AS `od_amount`, MIN(UNIX_TIMESTAMP(`or_date`)) AS `or_date` FROM `npOrderMain` JOIN `npOrderData` JOIN `bgProducts` WHERE `npOrderMain`.`or_id` = `npOrderData`.`or_id` AND `od_pr` = `pr_id` AND UNIX_TIMESTAMP(`or_date`) >= '1262322000' AND UNIX_TIMESTAMP(`or_date`) <= '1346990399' AND (`pr_id` = '415' OR `pr_id` = '1088') GROUP BY `bgProducts`.`pr_id` ) TEMPTABLE3; it produces this result +-------+------------+--------------------------+-------------+-----------+ | pr_id | pr_sbtcode | pr_sdesc | od_quantity | od_amount +-------+------------+--------------------------+-------------+-----------+ | 415 | NP13 | Product 13 | 5 | 125 | 1088 | NPAW | Product AW | 4 | 100 | 415 | NP13 | Product 13 | 5 | 125 | 1088 | NPAW | Product AW | 2 | 50 +-------+------------+--------------------------+-------------+-----------+</pre> What I want to get a result that combines those into 2 lines: +-------+------------+--------------------------+-------------+-----------+ | pr_id | pr_sbtcode | pr_sdesc | od_quantity | od_amount +-------+------------+--------------------------+-------------+-----------+ | 415 | NP13 | Product 13 | 10 | 250 | 1088 | NPAW | Product AW | 6 | 150 +-------+------------+--------------------------+-------------+-----------+</pre> So I added GROUP BY pr_id to the end of the query: SELECT pr_id, pr_sbtcode, pr_sdesc, od_quantity, od_amount FROM ( SELECT `bgProducts`.`pr_id`, `bgProducts`.`pr_sbtcode`, `bgProducts`.`pr_sdesc`, SUM(`od_quantity`) AS `od_quantity`, SUM(`od_amount`) AS `od_amount`, MIN(UNIX_TIMESTAMP(`or_date`)) AS `or_date` FROM `bgOrderMain` JOIN `bgOrderData` JOIN `bgProducts` WHERE `bgOrderMain`.`or_id` = `bgOrderData`.`or_id` AND `od_pr` = `pr_id` AND UNIX_TIMESTAMP(`or_date`) >= '1262322000' AND UNIX_TIMESTAMP(`or_date`) <= '1346990399' AND (`pr_id` = '415' OR `pr_id` = '1088') GROUP BY `bgProducts`.`pr_id` UNION SELECT `bgProducts`.`pr_id`, `bgProducts`.`pr_sbtcode`, `bgProducts`.`pr_sdesc`,SUM(`od_quantity`) AS `od_quantity`, SUM(`od_amount`) AS `od_amount`, MIN(UNIX_TIMESTAMP(`or_date`)) AS `or_date` FROM `npOrderMain` JOIN `npOrderData` JOIN `bgProducts` WHERE `npOrderMain`.`or_id` = `npOrderData`.`or_id` AND `od_pr` = `pr_id` AND UNIX_TIMESTAMP(`or_date`) >= '1262322000' AND UNIX_TIMESTAMP(`or_date`) <= '1346990399' AND (`pr_id` = '415' OR `pr_id` = '1088') GROUP BY `bgProducts`.`pr_id` ) TEMPTABLE3 GROUP BY pr_id; But that just gives me this: +-------+------------+--------------------------+-------------+-----------+ | pr_id | pr_sbtcode | pr_sdesc | od_quantity | od_amount +-------+------------+--------------------------+-------------+-----------+ | 415 | NP13 | Product 13 | 5 | 125 | 1088 | NPAW | Product AW | 4 | 100 +-------+------------+--------------------------+-------------+-----------+ What am I missing here??

  Read the article

• Using Memcached in Python/Django - questions.

  - by Thomas

  I am starting use Memcached to make my website faster. For constant data in my database I use this: from django.core.cache import cache cache_key = 'regions' regions = cache.get(cache_key) if result is None: """Not Found in Cache""" regions = Regions.objects.all() cache.set(cache_key, regions, 2592000) #(2592000sekund = 30 dni) return regions For seldom changed data I use signals: from django.core.cache import cache from django.db.models import signals def nuke_social_network_cache(self, instance, **kwargs): cache_key = 'networks_for_%s' % (self.instance.user_id,) cache.delete(cache_key) signals.post_save.connect(nuke_social_network_cache, sender=SocialNetworkProfile) signals.post_delete.connect(nuke_social_network_cache, sender=SocialNetworkProfile) Is it correct way? I installed django-memcached-0.1.2, which show me: Memcached Server Stats Server Keys Hits Gets Hit_Rate Traffic_In Traffic_Out Usage Uptime 127.0.0.1 15 220 276 79% 83.1 KB 364.1 KB 18.4 KB 22:21:25 Can sombody explain what columns means? And last question. I have templates where I am getting much records from a few table (relationships). So in my view I get records from one table and in templates show it and related info from others. Generating page last a few seconds for very small table (<100records). Is it some easy way to cache queries from templates? Have I to do some big structure in my view (with all related tables), cache it and send to template?

  Read the article

• PyGTK: Manually render an existing widget at a given Rectangle? (TextView in a custom CellRenderer)

  - by NicDumZ

  Hello! I am trying to draw a TextView into the cell of a TreeView. (Why? I would like to have custom tags on text, so they can be clickable and trigger different actions/popup menus depending on where user clicks). I have been trying to write a customized CellRenderer for this purpose, but so far I failed because I find it extremely difficult to find generic documentation on rendering design in gtk. More than an answer at the specific question (that might be hard/not doable, and I'm not expecting you to do everything for me), I am first looking for documentation on how a widget is rendered, to understand how one is supposed to implement a CellRenderer. Can you share any link that explains, either for gtk or for pygtk, the rendering mechanism? More specifically: size allocation mechanism (should I say protocol?). I understand that a window has a defined size, and then queries its children, saying "my size is w x h, what would be your ideal size, buddy?", and then sometimes shrinks children when all children cant fit together at their ideal sizes. Any specific documentation on that, and on particular on when this happens during rendering? How are rendered "builtin" widgets? What kind of methods do they call on Widget base class? On the parent Window? When? Do they use pango.Layout? can you manually draw a TextView onto a pango.Layout object? This link gives an interesting example showing how you can draw content in a pango.Layout object and use it in a CellRenderer. I guess that I could adapt it if only I understood how TextView widget are rendered. Or perhaps, to put it more simply: given an existing widget instance, how does one render it at a specific gdk.Rectangle? Thanks a lot.

  Read the article

• Access VBA question: Change the query being referenced by a function, depending on context

  - by Tara Amatista

  I have a custom function in Access2007 that hinges on grabbing data out of a specific query. It opens Outlook, creates a new email and populates the fields with specific addresses and data taken from the query ("DecisionEmail"). Now I want to make a different query ("RequestEmail") and have it populate the email with that data. So all I have to do is change this line: Set MailList = db.OpenRecordset("DecisionEmail") and that's where I get stumped. This is my desired result: If the user is on Form_Decision and clicks the button "Send email", "DecisionEmail" will get plugged into the function and that data will appear in the email. If the user on Form_SendRequest and clicks the button "Send email", "RequestEmail" will instead get plugged in. The reason that these are different queries is because they contain very different information that is smudged about in different ways. However, since it's just one little thing that needs to change in the function code, I don't think a brand new function is a good idea. My last resort would be to make a brand new function and use the Conditions field in the Macro interface to choose between them, but I have a feeling there's a more elegant solution possible. I have a vague notion of setting the query names as variables and using an If statement but I just don't have the mental vocabulary for thinking through this.

  Read the article

• build SQL query string using user input

  - by user175084

  i have to make a string by using the values which the user selects on the webpage suppose i need to display files for multiple machines with differnt search criteria.. i currently use this code: DataTable dt = new DataTable(); SqlConnection connection = new SqlConnection(); connection.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString; connection.Open(); SqlCommand sqlCmd = new SqlCommand("SELECT FileID FROM Files WHERE MachineID=@machineID and date= @date", connection); SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd); sqlCmd.Parameters.AddWithValue("@machineID", machineID); sqlCmd.Parameters.AddWithValue("@date", date); sqlDa.Fill(dt); now this is fixed query where the user just has one machine and just selects one date... i want to make a query in which the user has multiple search options like type or size if he wants depending on what he selects also if he can select multiple machines.. SELECT FileID FROM Files WHERE (MachineID=@machineID1 or MachineID = @machineID2...) and (date= @date and size=@size and type=@type... ) all of this happens in runtime... other wise i have to create a for loop to put multiple machines one by one... and have multiple queries depending on the case the user selected... this is quiet interesting and i could use some help... thanks

  Read the article

< Previous Page | 159 160 161 162 163 164 165 166 167 168 169 170  | Next Page >