MYSQL :: Prepared Statements (for Batch Inserts)

Prepared Statements
I am trying to prepare a dataset to use in R for statistical analysis. R is pretty rubbish on the DBMS front so I want to have everything prepared in MySQL.

I currently have a dataset (weather) showing weather data over time where there are 3 columns: "year", "month" & "total_rainfall". I would like to create a dataset showing average rainfall over a 3 month period. This is easy for one such period e.g

CREATE TABLE avgweather
SELECT Year,
AVG(total_rainfall) AS avg_rain_jan_to_mar
FROM weather
WHERE month BETWEEN 1 AND 3
GROUP BY Year
;

What I can't figure out what to do, is how to make the dataset avgweather contain lots of different 3 month periods without a lot of copying and pasting.

I would like avgweather to contain lots of columns e.g. "year", "avg_rain_jan_to_mar", avg_rain_feb_to_apr", "avg_rain_mar_to_may"......and so on, but I just can't make prepared statements cooperate!

Even better, would be if instead of the column names containing months as text, I could use the numbers that I specify in the where clause. E.g. "avg_rain_jan_to_mar" would become "avg_rain_1_3"

Prepared Statements
Prepared sql queries are nice, especially, as is often the case with websites, you have about 100 queries that are reused over and over and over again with slightly different parameters.
There is just one hitch. From what I can tell, prepared statements belong to a mysql connection. I.E. if you have a hundred open connections, you cannot share prepared queries among them. Is this true?
I use a connection pool to hand out a mysql connection to every incoming request. If prepared queries can not be shared among connections, do you think it would be a good or bad idea to prepare all 100 sql queries for all of the (possibly over 100) connections? (10,000 prepared queries)?

Prepared Statements
prepared statements are to be initialized (defined) each time you connect to database (mysql_connect) or each time mysql is started

Prepared Statements
i've got some questions:
what happens when we deallocate a prepare statement?
can we use same name for 2 prepared statements in a SP by deallocating
first
one before defining second one?
can we use a prepare statement in a loop?
can we use more than 1 prepared statement in a single SP?

Inspecting Prepared Statements?
MySQL Version: 5.0.21
Java Version: 1.5.0_06
MySQL Connector/J Version: 3.1.12

I've been Googling all day and I haven't been able to find out if there is a way of seeing what statements have been prepared. I'm getting intermittent errors from my web app from some PreparedStatements with an error message of "Unknown column 'x' in 'where clause'". I'd like to verify that the PreparedStatements are getting created correctly but I don't know of a way to inspect those statements on the database server. Any ideas?

Cloning Prepared Statements
I have a prepared statement on a given connection. Is there an easy way to create a duplicate of the statement in an other connection?

See Prepared Statements Parameters On The Log
Is there any (simple) way of getting a Prepared Statement *with its parameters* on the log file?

Dynamic SQL Using Prepared Statements
can we use prepared statements inside SPs to execute dynamic queries? what is the difference between a variable that starts with '@' and regular variables?
(sorry to ask this simple question but search engines ignore '@' and i couldn't find a proper answer in pdf manuals i had.)

Error 2031 - Prepared Statements
I tried the following:

$query = "UPDATE `registration` SET `id`=?, `username`=?, `password`=?, `name`=?, `surname`=?, `institution`=?, `address`=?, `phone`=?, `fax`=?, `email`=?, `accomod_6`=?, `accomod_7`=?, `accomod_8`=?, `accomod_9`=?, `accomod_10`=? WHERE `registration`.`id`=$uid LIMIT 1";

if (($stmt = mysqli_prepare($sql, $query)) != false) {
mysqli_stmt_bind_param($stmt, "sssssssssiiiii", $r['username'], $r['password'], $r['name'], $r['surname'], $r['institution'], $r['address'], $r['phone'], $r['fax'], $r['email'], $r['accomod_6'], $r['accomod_7'], $r['accomod_8'], $r['accomod_9'], $r['accomod_10']);
mysqli_stmt_execute($stmt);
echo mysqli_stmt_errno($stmt).": ".mysqli_stmt_error($stmt));
mysqli_stmt_close($stmt);

The code returns the following:
2031: No data supplied for parameters in prepared statement.

Which is nice but i don't know how to make it work :-) I first had a look if all the variables passed to mysqli_stmt_bind_param are set (they were not so i force-set them but without a result.) and now i don't know what to do. Any suggestions?

Maximum Of 254 Prepared Statements Per Connection
MySQL v4.1.0-alpha only allows a client to prepare a maximum of 254
statements. On the 255th mysql_prepare() call, a failure is returned
with no information returned by mysql_error(). This occurs even if
the statements are closed after each use. Code:

Reading Binary Data With Prepared Statements
is there any example of getting binary data from a mysql database?. I tried the example in http://dev.mysql.com/doc/refman/5.0/en/mysql-stmt-fetch.html which reads strings, numbers and datetimes, and it works fine. But how should I do the same with blob fields?. What is the buffer_length for blob data type?

(in the example it is STRING_SIZE=50 for string, but it doesn't work for blobs (FIELD_TYPE_BLOB). I tried calling mysql_fetch_lengths but it doesn't work for prepared statements.

Prepared Statement
I have a Stored Procedure, inside of which I have a prepared statement. The prepared statement exists because I want to search for a value using LIKE and wildcards. Here is what I have:

---------------------------------------------------------------------
(IN param1 VARCHAR(45))
BEGIN
SET @a := param1;
SET @sql_text := concat("SELECT UPC, Name, Price FROM inventory WHERE Name LIKE concat(`%`, ", ?, ", `%`);");
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
END
---------------------------------------------------------------------

I think it would be cool if I could just do this:

(IN param1 VARCHAR(45))
BEGIN
SELECT UPC, Name, Price FROM inventory WHERE Name LIKE concat('%', param1, '%');
END

BTW, while I have you, what is the difference between ' and ` as far as MySQL is concerned???

MySQL Prepared
I'm using the prepared statements part of the C API since it appears
to be the only way to get data from the database in binary form.
There appears to be a design flaw in the current API - it seems to be
impossible to get the maximum length for the data in each column of a
result set even when you store the entire result to the client (using
mysql_stmt_store_result). The column metadata returned by
mysql_prepare_result has nothing to do with any particular execution
and therefore does not contain max length information.

Cursor For A Prepared SELECT
how can I create a cursor for a SELECT statement previously prepared?

I must do something like this:
DECLARE cur CURSOR
FOR SELECT * FROM <tab> WHERE Id=<id>;

<tab> and <id> are parameters passed to my stored procedure.
Can I prepare the SELECT statement and then assign it to the cursor AFTER the declaration?

Prepared Statement? For Table Name Trouble
I need to select data from a table where part of the table name is the user name. I'd like to use a prepared statement to avoide the risk of SQL injection but it isn't working.....

Batch Mode
I understand that to use batch mode it is necessary to put the commands you want to run into a file before telling MySQL to read its input from the file, but how do I create the file and what do I create it in? Also, I'm not really sure how you would then tell MySQL to read it.

Using Batch Files
I have a small problem with using batch files (platform: Windows XP). The problem I have is that I do not want to place and call all my db batch files from within BIN folder, but rather from, say c:a1a2a3. Calling a batch file in Windows like this is not an issue:

mysql>source c:/a1/a2/a3/test.sql;

Then, you can call test.sql from test2.sql, for example, by the same line of code:

source c:/a1/a2/a3/test.sql;

Is there any way to keep the base direcory, i.e. c:a1a2a3 in some sort of environment or whatever variable and then use it in the parameter passed to the source command.

I have experimented with user-defined variables to no avail:

SET @BASEDIR = 'c:/a1/a2/a3/test.sql';
SOURCE @BASEDIR;

Batch Mode
I want to execute the queries wriiten in a .sql file in a single attempt using the batch process of mysql.Since I don't have a good grip over the database systems, I am confused how should I go for it..

Using Batch Mode
can someone show me the way of using batch mode?
what is the syntax of it?
what should i do for the first step?
can someone tell me about that?

Batch File
I am new to running batch files. Is there a way to loop through a results set in a batch file? I want to be able to create tables using table names that are stored in a table. I had hoped that I could query the table to retrieve all tablenames, then loop through this result set, creating a table with each tablename returned. (Table definitions will be the same for all tables created. Only the tablenames are different.)

Mysql C Prepared Statement API Sending Incorrect Data
I am having a problem with the C api with prepared statements, the data
recorded in the database does not match the data I am sending. It seems to
be some sort of bit shifted version of the data, and I have no idea why.

I am including code I have been using to test this, followed by the output
in the database.

I am using mysql version 4.1.12, I have tried this on multiple servers, remote and local. Code:

Prepared Statement In Stored Procedure Not Returning Rows To App
I have a stored procedure that I'm using to allow users to perform a search on the database. I'm compiling the select statement in the stored procedure and then using PREPARE, EXECUTE, DEALLOCATE.

When I run just the stored procedure it works. When I call it from the app it returns 0 rows. If I just put the SQL statement without using PREPARE it returns rows. Here is the DDL for my stored procedure:

Cant Execute Batch File
Hi new to this but pulled out nearly all my hair with this.
I got the test database open and I want to automate creating the menagerie table ( as in the tutorial ) so I download the textfile save it to c:/ and execute the command source c:/cr_pets_tbl.txt all I get is unable to open file error 22.
I have tried creating my own text file ( windows, notepad ) just containing the following
use test;
create table pets(name varchar(10),species varchar(8),born date);

simple as that then type in the consoe source c:/pets.txt;
unable to open file error 22 or sometimes error 2
tried enclosing filename in " and ' tried source= filename
tried . instead of source but nothing works.
using manual insert command or load data local infile works but not this, HELP.
Malcolm K.

Batch File For Backup
Can I make complete Backup of a database (same as from MySQL Administrator) using batch file?

Getting Error In Batch Update
I am using Mysql 4.1.13 with Tomcat5 and Connector mysql-connector-java-3.1.10. When I try to update the table in batch mode sometimes( When server is idle for a long time) get following error. Code:

Consecutive Ids In A Batch Insert?
I am attempting to batch insert 100 rows into a table, and I need to know the 100 id's of all the inserted rows.

I know that I can find the id of the last inserted row despite my app
being multi-threaded, since the select last inserted command is
connection-specific.

Given the id of the last inserted row, can I back out all the ids of
the inserted rows? Are the inserts consecutive in an auto-increment
table?

If not, I will be looking to lock the table during inserts, but that
seems very sub-optimal.

Running mysql 5.0.24

Consecutive Ids In A Batch Insert
I am new to mysql and would appreciate any advice that you have to
offer.
I am attempting to batch insert 100 rows into a table, and I need to
know the 100 id's of all the inserted rows.

I know that I can find the id of the last inserted row despite my app
being multi-threaded, since the select last inserted command is
connection-specific.

Given the id of the last inserted row, can I back out all the ids of
the inserted rows? Are the inserts consecutive in an auto-increment
table?

If not, I will be looking to lock the table during inserts, but that
seems very sub-optimal.

Batch Mode Problem
I am having a problem with 2 sql statements in 1 batch file. Only the last
statement executes. If I separate the statements into individual files they
work fine.

Are you only allowed 1 statement per file?

Here is the contents of the batch file:
insert into monlog (monlog_locno, monlog_custno, monlog_time,
monlog_inputdate, monlog_errno) select loc_no, loc_custno,
(hour(now())*60)+minute(now()), now(), 200 from loc where
loc_next+29<(hour(now())*60)+minute(now())+30 and loc_errno>=200 and
loc_errno<=249;
update loc set loc_errno='250' where
loc_next+29<(hour(now())*60)+minute(now())+30;

Mysql Batch Question
I'm writing this here because it's related to mysql. I'm using Mysql with PHP and because Mysql 4 doesn't support stored procedures, I would like to run several scripts from one database connection. In running a multi-line sql.txt file externally I can do this by delimiting the statements with a semi-colon so I tried to do the same:

$strsql = "";

$strsql = "delete from tbl1 where tbl1ID = '1';";
$strsql = $strsql . "delete from tbl1 where tbl1ID = '2';";
$strsql = $strsql . "delete from tbl1 where tbl1ID = '3';";

$result = @mysql_query($strsql, $db);

I'm not getting an error; the sql scripts simply won't run.

Batch FIle To Create Database
I am trying to create a batch file to create a database and populate it with create table statements. I can't figure out why it will not create the database and load the tables. I have the following in my batch file

/mysql/bin/mysql.exe -vvv -u root -pcs3911 < C:mySql est.txt

and the following is my test.txt file

CREATE DATABASE 'lovettsite';

CREATE TABLE `applications` (
`username` varchar(255) NOT NULL default '',
`firstname` varchar(255) NOT NULL default '',
`middlename` varchar(255) NOT NULL default '',
`lastname` varchar(255) NOT NULL default '',
`current_address_line_1` varchar(255) NOT NULL default '',
`current_address_line_2` varchar(255) NOT NULL default '',
`current_city_town` varchar(255) NOT NULL default '',
`current_state_province` varchar(255) NOT NULL default '',
`current_zip_code` varchar(16) NOT NULL default '',
`p.........................................................................

Running MySQL Using A Dos Batch File
I am used to use MySQL on Unix, now I have to do some development for MySQL running on Windows machine. The following script works well in Unix but I am unable to run it on Windows. "MY_FLAG" is the one which is causing issue. Due to certain restriction in my framework, I can use the normal batch by supplying the .sql file while opening the mysql connection ( infact i need to run multiple source files) Code:

See Feedback Message On Batch Mode
I wrote a perl program to update/insert mysql
database. It worked fine but I could not see the feedback message to
show how many rows updated or inserted. I can see that if I issue SQL
commands interactively.

BTW, what's the easiest way to check return code from SQL commands in
perl. I use korn shell script to invoke perl script that does SQL
stuff. I need to pass the return code back to shell script.

See Feedback Message On Batch Mode
I am new on mysql. I wrote a perl program to update/insert mysql
database. It worked fine but I could not see the feedback message to
show how many rows updated or inserted. I can see that if I issue SQL
commands interactively.

BTW, what's the easiest way to check return code from SQL commands in
perl. I use korn shell script to invoke perl script that does SQL
stuff. I need to pass the return code back to shell script.

Using Batch-/cmd-file For Dumping Data
i want to build in data in my database that are written in single table dumps like [tablename].sql.

Each time it is nececery to use several files. And it is also nececary to do this using the command line because of the files are bigger than 1,5 gb
But i didnt find a way to transfere the needed commands into the mysql command line.

How To Execute A Batch Sql File Under Windows?
I have installed MySQL 5 in c:Program FilesMysql. I have a sql file named "books.sql" which is put in the subfolder in,it contains many statements that like "INSERT ... value ...".

my question is how to execute it? I try it, but failed.

One more question is how can I return from "->" to "sql>" promopt?

Tabs Missing In Batch Mode
in a shell script of mine I'm executing a mysql SELECT in batch mode. I
want to use the return value for other sql statements.
The problem I'm facing is that the tabs, which seperate the columns
from each other, are missing in the output.
What am I doing wrong?

#!/bin/sh
QUERY="select id, i.handle from idmap i, person p where
i.handle=p.handle and email="$1""
mysql mydatabase -pmypassword -N -B -e "$QUERY" | while read line ; do
personid=$(echo $line | cut -f1) ; handle=$(echo $line | cut -f2 ) ;
echo $handle ; done ;

Running A Sql Batch Script From The Linux Command
s this possible?

I want to write a command script which i want to put into a cron job which will download a db backup from cpanel. then i want to unzip it, and run the sql file, so keeping the database on the mirror machine up to date.

So what i have is:

#get the database
wget -v --http-user user --http-pass pass https://www.site.com/path/to/cpanel/backup
#move to backups folder
date=`date -I`
mv -fv ./mysqldump.gz /home/user/dbbackup/$date.gz
now what i also want to do is:

mysql -uuser -ppassword
u database
set foreign_key_checks = 0;
. mysqldumpfile.sql
set foreign_key_checks =1;
exit
is it possible to do that last bit from the command-line? as far as i know, a shell script can't run commands to mysql?

Batch Query Or Large List Of IN() Arguments?
I'm receiving a potentially large list of productID numbers from an external data source.

Each productID has a number of partID's associated with it and those partID's are stored in my database.

I would like to create a list of products sorted by the number of parts each has.

If I only have one table called "part" which stores a partID and its associated productID, which of the following methods would be more efficient assuming the application and DB are on the same server.

Option A)

SELECT productID, Count(*) counter
FROM part
WHERE productID
IN(large list of productID's received, maybe 1 or >10000)
GROUP BY productID
ORDER BY counter DESC
LIMIT ?, 10

Option B)

Execute a batch query of prepared statements (SELECT COUNT(*) FROM part WHERE productID=?) for each product in the received product list then sort the returned results in the Java app that is making the query.

Option C)

???

Running Mysql Commands In A Batch File
I am trying to automate a mysql command to upload data everymorning on a windows server. I have created a .bat file and entered the commands to login to mysql but when it gets to the mysql prompt it fails to complete the rest of the commands.

cd
c:
cdmysqlin
mysql
TRUNCATE TABLE blah
LOAD DATA INFILE 'blah.txt' INTO blah
QUIT
exit

Is this possible?

Mysqld-nt Crashes When Executing Batch File In 5.03 Beta
I'm quite new to mysql but I had installed the 5.0.1 and 5.0.0a alpha under windows XP, and designed a little database for managing cards. To make some backup of this database I dumped it in a batch file. This file was working perfectly with mysql 5.0.1 and 5.0.0a.

When I try and execute my batch file, having previously created the database concerned, it crashes the mysqld-nt. On top of that the first table created in the database is listed but when a DROP DATABASE is issued it says that this table does not exist and then the command fails.

The database uses UTF8 and SET FOREIGN_KEY_CHECKS=0; is set in the begining of the file.

So I have two questions :

1- Can I force the database to be dropped or is it definitely corrupted?

2- Any clue on why the batch file crashes the server? (I'd be glad to send it is is a mere 250 ko when zipped)

Questionable Row Inserts
with mysql table colum set at varchar(15), somebody recently managed to do an insert having 16 characters. Too, the regular expression on the insert form for validations is at 15

through a textarea input area on the form, this is the start of the inserted values, followed by a very long list of hyperlinks (mainly gambling sites, etc):

"Vnpqxs
Of Action "

- how is the greater than varchar(15) possible?
- does this look like someone could be trying to run scripts from within the table?
- can I assume my insert form was bypassed?

an ip lookup revealed Amsterdam (my site is a Los Angeles based city site)

Multiple INSERTS At Once Possible?
say i have table users that holds names, and i have a page that has 10 textfields through which i can import new names.

Is it possible to enter all 10 at once rather than:

$query = mysql_query("INSERT INTO users VALUES '".$_POST['t1']."');
$query = mysql_query("INSERT INTO users VALUES '".$_POST['t2']."');
$query = mysql_query("INSERT INTO users VALUES '".$_POST['t3']."');
$query = mysql_query("INSERT INTO users VALUES '".$_POST['t4']."');

Auto_number And Inserts
When I do an insert I am doing an insert and an update immediately afterwards - can this be modified to a straight insert for performance enhancement at all?

Stored procedure section:

insert into tree_node(name, parent) values ('x', 123);
select max(id) into _created_id from tree_node;
update tree_node set self = _created_id;

I'd like to do something like:
insert into tree_node(name, parent, self) values ('x', 123, NEW_ID);

is that possible?

MULTIPLE INSERTS
Is it possible to simultaneously insert a row into different tables using different column names in each table and different/similar values.

EG:

INSERT INTO table1 (col1, col2, col3) values (val1,val2,val3)

INSERT INTO table2 (col5, col6, col7 ,col8) value(val1,val2,val6,val8)

(BOTH above inserts must be carried out at the same time)

This would process both inserts, and if one insert failed then the other would fail also? Similar to transactions in MS SQL Server.

Multiple Inserts
I'm saving data into a table that has a list format. So I usually have 5-10 rows of data each with about 4-5 columns. Is it more efficient to insert once with multiple rows like

INSERT into TABLE VALUES (val1, val2, val3), (val4,val5,val6) etc

or should i just insert once for each row?
Are there performance/security pros and cons to either approach?

PhpMyAdmin - Use Ignore Inserts
What does the "Use ignore inserts" option within phpMyAdmin in the "export" tab do?

Am I right in thinking it will ignore an insert when the primary key already exists?

What is the best way to do inserts in such a way that incase a record with the primary key already exists, it will simply overwrite it in the database?

I Have Way Too Many Inserts/updates In My Forum
when someone posts a thread theres 2 count queries
2 fetch queries
3 insert queries
and 2 update queries

so my question is, is there a way to do a sort of join query for inserts and updates?

also, has anyone got some mysql musts?
i dont see a mysql tips thread
i think there should be one

Inserts - MyISAM Vs InnoDB
I am making a logging script which will log every request to the site. The frequency of inserts will be approximately 60K per hour. After every 1 hour a cronjob will generate a report of the data, save it to in a different table and truncate this logging table. So this table will have only 1 hour data.

I am logging IP, user-agent and page visited. I have created

Today I have used both table types one by one to experiment. I used INSERT DELAYED with MyISAM and INSERT with InnoDB. But when I viewed the processlist, there were a lot of threads in sleep state (in both cases). Why it was so?

Question: Which table type is best suitable for this need?

I am using Linux, PHP 4.4 and MySQL 4.1.