I appreciate the semantic meaning of a NULL value in a database table, different from both false and the empty string ''. However, I have often read about performance problems when fields are nullable and been advised to use an empty string in cases where NULL is actually semantically correct. What circumstances are appropriate to us...
When I try to call a stored procedure from Rails, I get this exception: ActiveRecord::StatementInvalid: Mysql::Error: PROCEDURE pipeline-ws_development.match_save_all can't return a result set in the given context: call match_save_all() from /Users/otto/Projects/Futures/src/pipeline-ws/vendor/rails/activerecord/lib/active_record/con...
I have to create a report on some student completions. The students each belong to one client. Here are the tables (simplified for this question). CREATE TABLE `clients` ( `clientId` int(10) unsigned NOT NULL auto_increment, `clientName` varchar(100) NOT NULL default '', `courseNames` varchar(255) NOT NULL default '' ) The cour...
I know think that I've read that one of the following is faster than the other, but I forget which is which: SELECT * FROM `myTable` WHERE `myDate` > NOW(); SELECT * FROM `myTable` WHERE NOW() < `myDate`; ...
LIKE operator in MySql is used to find rows that contain our query text, for example: select name from user where name like "%john%" which will return "John Smith", "Peter Johnson" etc. What if I need the opposite - to find rows that are CONTAINED in our query text? For example I give it "John Smith and Peter Johnson are best friends...
How would this stored procedure look like in MySQL? : ========================================================================= Create PROCEDURE [dbo].[Customer_Insert] ( @CustomerID int = NULL OUTPUT, @CustomerRef varchar(25), @Name varchar(64) = NULL, ) AS SET NOCOUNT ON INSERT INTO [Customers] ( [CustomerRef], [Name] ) V...
I am looking for a php webbased free query analyzer to see slow and currently executing SQL statements ...
I'm about to use MySQL with Hibernate on a Tomcat 5.5.x server. Do I have to put mysql-connector-java-[version]-bin.jar in $CATALINA_HOME/common/lib/ or could I place it in WEB-INF/lib inside my WAR file with my other library dependencies? It would be easier to have it in my WAR in WEB-INF/lib, as I could get it using the Maven reposit...
I need to create an SQL query to insert some data into a table based on the results of a SELECT query into a local variable. My DB platform is MySQL 5.1, but I don't think that should make a difference here. Basically, what I want to do is: SELECT id INTO var_name FROM table1 WHERE some_column='something' LIMIT 1; INSERT INTO table2 (...
Hi all, I am working on a Web based organisation tool. I am not aiming the same market as the wonderful Basecamp, but let's say the way users and data interact look like the same. I will have to deal with user customisation, file uploads and graphical tweaks. There is a fora for each account as well. And I'd like to provide a way to b...
On a very important Windows server we use a scheduled task set up by the mysqladmin utility to back up our critical data. I'd like to use the simpler mysqldump command, but I want to ensure that the output is the same. Presumably mysqladmin and mysqldump both share a common core dumping component, so for any given setting in mysqladmin ...
Hi. I am part of a team creating a web application using PHP and MySQL. The application will have multiple users with different roles. The application will also be used in a geographically distributed manner. Accordingly we need to create an access control system that controls user permissions for specific database records i.e. modifi...
I have a database table full of some really ugly and messy data. In a seperate table i have a cleaner version of the data and they are linked by an id, but I need to keep the messy dataset and can't overwrite it as I use it to check against data differences. I'm trying to merge the data into a new table, OR use a single query across bo...
This is for MySQL and PHP I have a table that contains the following columns: navigation_id (unsigned int primary key) navigation_category (unsigned int) navigation_path (varchar (256)) navigation_is_active (bool) navigation_store_id (unsigned int index) Data will be filled like: 1, 32, "4/32/", 1, 32 2, 33, "4/32/33/", 1, 32 3, 34,...
stripslashes() ? That's lame and so 4.0. What's the 5.0 counterpart of mysqli::real_escape_string that strips all slashes added for SQL queries? Got some other questions: Tried to update a record and added a single quote in a text field, turns out phpMyAdmin escapes the string with single quotes instead of slashes - e.g. a single quot...
Hi, I was wondering if it which is faster when trying to, for example, check how many posts are in a particular thread on a forum. Should I... (a) Go through each post in the database with the particular thread ID, and count how many rows or (b) Add one to a separate column in the threads database every time a thread is made, and then...
Hai Techies, I have some stored procedure which was written in SQL server.Now i want to migrate this to mysql.Is there any freeware tools which can do this for me. ...
In my application I have different categories that users can post their transactions as. Example: Food, Shopping, Movies, etc.. I want the user to be able to edit these categories and add/remove categories. What is the best way to store this information in a database. A categories table with the category and corresponding user? Then I ...
I currently have following two tables: **files_list** -listid -name -synonym -description **files_tags** -tag_name -listid If someone uses the keyword "dragon ball", at the moment, I use following query to search my_list for possible matches: **SELECT * FROM `files_list` WHERE ( name LIKE '%dragon%' OR synonym LIKE '%dragon%' OR des...
I thought that I'll be clever and use subquery to get my report in one go. But after running into problems and reading documentation I saw that my approach does not work in MySQL. My inner query returns ~100 records and outer query scans 20000 records. When I restricted outer query to 20 records then it run 20 sec - really slow. I wonde...