mysql_query

[Chameleon]

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
Commands.


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?)

[Dikkie Dik]

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(...)
VALUES(...),
(...),
(...);

Depending on the engine used, you could send it without being interested
in the result:
see http://dev.mysql.com/doc/refman/4.1/...t-delayed.html

Best regards

Chameleon wrote:[color=blue]
> 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
> Commands.
>
>
> 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?)[/color]

[Gordon Burditt]

Re: mysql_query

>How can I send it to SQL Server at once?

I don't think you're going to get anywhere using mysql_query to
send to Microsoft SQL Server.
[color=blue]
>I see mysql_query() fails on first semicolon (;) who delimits the SQL
>Commands.[/color]

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.
[color=blue]
>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.[/color]

This is the way it's defined to work.
[color=blue]
>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?)[/color]

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

[Jim Michaels]

Re: mysql_query


"Dikkie Dik" <nospam@nospam. org> wrote in message
news:634d9$43ee 086b$57d40752$1 1566@news.versa tel.nl...[color=blue]
> 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(...)
> VALUES(...),
> (...),
> (...);[/color]

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

[color=blue]
>
> Depending on the engine used, you could send it without being interested
> in the result:
> see http://dev.mysql.com/doc/refman/4.1/...t-delayed.html
>
> Best regards
>
> Chameleon wrote:[color=green]
>> 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
>> Commands.
>>
>>
>> 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?)[/color][/color]

[Michael Austin]

Re: mysql_query

Chameleon wrote:
[color=blue]
> 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
> Commands.
>
>
> 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?)[/color]

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/{primary|foreig n} key constraints in the db)
etc...
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://www.firstdbasource.com/donations.html
:)

[Jim Michaels]

Re: mysql_query

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

<html>
<head>
<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>
</head>

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

//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.ht ml\">Main
page</a>");

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


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




"Jim Michaels" <jmichae3@nospa m.yahoo.com> wrote in message
news:YtWdnR4vNu www3PeRVn-qQ@comcast.com. ..[color=blue]
>
> "Dikkie Dik" <nospam@nospam. org> wrote in message
> news:634d9$43ee 086b$57d40752$1 1566@news.versa tel.nl...[color=green]
>> 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(...)
>> VALUES(...),
>> (...),
>> (...);[/color]
>
> and in MySQL, you are limited to about I think 1MB per insert.(?) so you
> must do it in chunks if it's big.
>
>[color=green]
>>
>> Depending on the engine used, you could send it without being interested
>> in the result:
>> see http://dev.mysql.com/doc/refman/4.1/...t-delayed.html
>>
>> Best regards
>>
>> Chameleon wrote:[color=darkred]
>>> 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
>>> Commands.
>>>
>>>
>>> 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?)[/color][/color]
>
>[/color]