I do a query that returns a list of entities. How can I retrieve the entities from a ScrollableResults: Session s = ....; Query q = s.createQuery("....") # returns 100000s rows ScrollableResults sr = q.scroll(); sr.scroll(45999); # just a number Employee employee = ??? How do I get an employee in the last line of code ...
I am getting errno 150 when I try to create the following two tables. CREATE TABLE `mydatabase`.`userstatus`( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `name` VARCHAR(50) NOT NULL , `description` VARCHAR(255) , PRIMARY KEY (`id`) ); CREATE TABLE `mydatabase`.users( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , ...
Can you please help me optimize this query. I use this query to get a list of friends along with their details and their status. It takes about 0.08 secs to process this on a Athlon X2 6000 I cant use materizlized view as well because this is frequently changing. SELECT p.userid, p.firstname, p.lastname, p.gender, p.dob, x.relationship...
How can I log all executed SQL statements to the conosle/stdout when using Ruby MySQL? Like the log/development.log output from rails. ...
In phpMyAdmin v2.8.2.4 for MyISAM tables, the "Relation View" appears under the Structure tab. It shows a list of Internal Relations. But what do these do, given that MyISAM does not support foreign key constraints or relational integrity? By phpMyAdmin version 3.2.0.1 this page ("Relation View") no longer appears for MyISAM tables. So ...
My table (projects): id, lft, rgt 1, 1, 6 2, 2, 3 3, 4, 5 4, 7, 10 5, 8, 9 6, 11, 12 7, 13, 14 As you may have noticed, this is hierarchical data using the nested set model http://tr.im/EO3G. Tree pretty-printed: 1 2 3 4 5 6 7 I want to select all sub projects under project 1 and 4. I can do this with: SELECT p.id FROM projects...
I am using phpMyAdmin. In order to set up a foreign key constraint with InnoDB (under the "Relation View" link on the Structure tab) it appears that I need to add an index for the field to which I want to add the restraint. This obviously has an impact on performance of inserts/updates on the table, particularly if there are several cons...
How can I extract the serialized values of a string stored in MySQL? The values look something like this: a:{s1:./... }. What is this? ...
Hello, We have a websites with hundreds of visitors every day and tens of thousands queries a day. So, some tables in the database are updated very rarely, some tables are updated few times a minute and some tables are updated ~10 times a seconds. MyISAM uses table-level locking for updates and InnoDb uses row-level locking. So, as I und...
I've got few doubts regarding quoting mysql queries in Zend framework. Though this question has helped me a bit but few things are still confusing: 1) $table is Zend_Db_Table. Trying to fetch a row from the table. $where[] = $db->quoteInto('id = ?', $id); $where[] = $db->quoteInto('user_id = ?', $user_id); $row = $table->fetchRow($wher...
Hi, I have an app with the use of namespace data.odbc in order to work with MySQL. I have tried it on different PC, the app works but when the odbc object should work, it does nothing. I suppose something is missing - I thought its automatically included. I have looked into app files - all are included. I have tried Add reference but th...
Hi, I am using System.data.odbc with "Driver={MySQL ODBC 5.1 Driver}" in the connection string. However, I have not realised there is MySQL connector installed on my PC so however I have not referenced it, it doesn work without it. Now I need to distribute it embed in the app. I have downloaded MySQL Connector/ODBC 5.1 files, but with Ad...
Hi, (Php & MySQL) I'm trying to figure out how come this function does not work correctly. It's add extra \ everytime I edit my entries. Online server have these settings: magic_quotes_gpc On magic_quotes_runtime Off magic_quotes_sybase Off function esc($s) { if (get_magic_quotes_gpc()) { if (ini_get('magic_quotes_sy...
I have a table of a couple of million records. During production, we find that there is a particular type of query that is taking a few seconds to complete. When I try to reproduce the issue on my machine which has replicated production data, I ran the exact query (as obtained from the slow query log) in the mysql client, it took a few ...
I recently asked this question. I have a relational database with three tables. The first containts id's that relate to the second. The second contains id's that relate to the third. The third contains the results I am after. Is it possible with a single query to query an id in the first table which g...
I've seen this on a lot of fields on a DB from a project I've been working on, where a column will be defined not null, but will have an empty string as the default. what's the point of doing this? If you will allow an empty string, why not just allow a field to be null? ...
I'm trying to update a row when it gets updated to keep one of the columns consistant, CREATE TRIGGER user_country BEFORE UPDATE ON user_billing FOR EACH ROW BEGIN IF NEW.billing_country = OLD.billing_country AND NEW.country_id != OLD.country_id THEN SET NEW.billing_country = cms.country.country_name WHERE cms.country.country_i...
I am trying to work out the best way to stop double 'booking' in my application. I have a table of unique id's each can be sold only once. My current idea is to use a transaction to check if the chosen products are available, if they are then insert into a 'status' column that it is 'reserved' along with inserting a 'time of update' th...
I want to export data and structure from MySQL database using PHP. I know about SELECT INTO OUTFILE command but I want to have the file like the one which is generated by PhpMyAdmin in Export window, so all the CREATE TABLE and INSERT INTO. HDo you know how PhpMyAdmn generates those files? I was browsing through the code and I've found ...
Hi guys, I've been using the adjacency model for hierarchical data for the longest time and was searching the internet for a more efficient way to traverse trees until I read about Nested Sets yesterday. I liked the idea but have a few doubts............. Now I just wanted to know if it is possible to use the Nested Sets model for many...