Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I have a SQL Script with about 5000 SQL Commands.

How can I send it to SQL Server at once?

I see mysql_query() fails on first semicolon (;) who delimits the SQL  

Another question:
I send to a MySQL server via fast Internet connection, about 500 INSERT  
commands one by one via mysql_query().
It takes much time. Why?
I suspect that, mysql_query() after send the query to server, waits for  
a "OK" response. If it's true, how can I send and send and send SQL  
commands and MySQL server reply "OK" after send all these things?
(or no wait at all?)

Re: mysql_query

First of all, you'd better ask in comp.databases.mysql

You can insert more than one row with an insert command:
INSERT INTO tblname(...)

Depending on the engine used, you could send it without being interested  
in the result:

Best regards

Chameleon wrote:
Quoted text here. Click to load it

Re: mysql_query

Quoted text here. Click to load it

and in MySQL, you are limited to about I think 1MB per insert.(?) so you  
must do it in chunks if it's big.

Quoted text here. Click to load it

Re: mysql_query

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

  <META HTTP-EQUIV="Expires" CONTENT="Tue, 01 Dec 1990 06:30:00 GMT">
  <META HTTP-EQUIV="Pragma" CONTENT="no-cache">
 <title>Init/Create the DB</title>

Query# <div name=i id=i style="font-weight:bold;"></div>
Query: <div name=q id=q style="font-weight:bold;"></div>
//create the database
include 'databaseinc.php';

//hosting site already has already created the DB
// mysql_create_db($db, $link);
// mysql_select_db($db, $link) or die("db select error: " . mysql_error() .  
"<a href=\"kamaniki.php\">Admin Page</a>, <a href=\"index.html\">Main  

//read the whole SQL dump file.
//eliminate comments first
$s = file_get_contents("import.sql");
$s = preg_replace($s,"\/\*.*\/\*"); //remove block comments. not a very good  
$s = preg_replace($s,"^\#.*$"); //remove single-line comments
$a = explode(";",$s);
for ($x=0; $x<count($a); $x++) {
 echo "<script>i.innerHTML=$x;q.innerHTML=\"" . addslashes($a[$x]) .  
 if ($a != "" && $a != "\n") {
  mysql_query($a) or die("query error: <b>" . mysql_error() . "</b><a  
href=\"index.html\">Main page</a>");
echo "Success...<a href=\"index.html\">Main page</a>";

I created this a while back in the need for database initialization, when  
admins had not installed PHPMyAdmin.

Quoted text here. Click to load it

Re: mysql_query

Quoted text here. Click to load it

I don't think you're going to get anywhere using mysql_query to
send to Microsoft SQL Server.

Quoted text here. Click to load it

Prior to some version of MySQL (maybe 5.0), you can't send more than
one statement at a time, and it must not have a terminating semicolon.

Even with the latest version, you need to use a client interface
that knows how to deal with multiple result sets coming back.
I am not sure whether PHP does this, but at the least, you need
the mysqli interface, not mysql.

Quoted text here. Click to load it

This is the way it's defined to work.

Quoted text here. Click to load it

You can insert many, many records with one insert statement,  
(even, I believe, on MySQL 3.23.*) e.g.:

insert into table values

mysqldump using this form has been known to pack 5,000 records into
one insert statement.  You are limited to some buffer size which
might be a quarter of a megabyte or some such limit for the query.

                    Gordon L. Burditt

Re: mysql_query

Chameleon wrote:

Quoted text here. Click to load it

A bit late, I know but it got me to thinking....

If you are going to insert 5000 rows into a database once or many times,  
sometimes it is achieved much easier by using the LOAD FILE(MySQL), SQL*Loader  
(Oracle), RMU/LOAD (Oracle Rdb) or DTS (Data Transformation Service (SQL Server)  
to name a few.   Use Bulk loaders for bulk load jobs - and life is much  
easier...  These tools even have exception handling for rows not loaded due to  
bad data.  You can use the previously stated methods, however, you must remember  
to take into account the exceptions.

Exceptions can be:
    Bad data (char in num field etc...)
    Duplicate data (unique/ key constraints in the db)
if one record fails - your entire transaction must be rolled back/undone to  
preserve data integrity.

Yes, managing databases and data is quite complicated as the main goal is to  
preserve data integrity at all cost.  If not, it is not worth a dime to anyone.

my .10 worth :)

Michael Austin.
DBA Consultant
Donations welcomed. Http://

Site Timeline