[PHP-DEV] Multiple inserts in one sybase_query issue
I'm running PHP 3 (and PHP 4 on another server) with Sybase 11.9.2 on the
database server.
I have tried in a number or variations to insert a series of updates and
inserts in the same sybase_query. I build a $query consisting of around
15-20 inserts and updates and call the sybase_query function, everything
works fine and it writes to the database as expected however none of the
sybase_querys after this point are recorded.
For example when I create this string:
$query = "Declare @nonuser_signup_id numeric(10,0)\n";
$query .= "Insert into nonuser_signup (email, firstname, lastname, gender)
values ('$email', '$firstname', '$lastname', '$gender')\n";
$query .= "Select @nonuser_signup_id = @@identity\n";
$query .= "Insert into nonuser_optin (nonuser_signup_id, optin_type_id,
promotion_id, signup_date) values (@nonuser_signup_id, $Free_Stuff, 5,
getdate())\n";
$query .= "Insert into nonuser_optin (nonuser_signup_id, optin_type_id,
promotion_id, signup_date) values (@nonuser_signup_id, $Contests, 5,
getdate())\n";
$query .= "Insert into nonuser_optin (nonuser_signup_id, optin_type_id,
promotion_id, signup_date) values (@nonuser_signup_id, $Surveys, 5,
getdate())\n";
$query .= "Insert into nonuser_optin (nonuser_signup_id, optin_type_id,
promotion_id, signup_date) values (@nonuser_signup_id, $Shopping, 5,
getdate())\n";
$query .= "Insert into nonuser_optin (nonuser_signup_id, optin_type_id,
promotion_id, signup_date) values (@nonuser_signup_id, $Games, 5,
getdate())\n";
$query .= "Insert into user_optin (user_id,optin_type_id,promotion_id)
values ($user_id, 10, 5)\n";
$result = sybase_query($query, $db);
Everything works fine, however when its broken up like this:
$query = "Declare @nonuser_signup_id numeric(10,0)\n";
$query .= "Insert into nonuser_signup (email, firstname, lastname, gender)
values ('$email', '$firstname', '$lastname', '$gender')\n";
$query .= "Select @nonuser_signup_id = @@identity\n";
$query .= "Insert into nonuser_optin (nonuser_signup_id, optin_type_id,
promotion_id, signup_date) values (@nonuser_signup_id, $Free_Stuff, 5,
getdate())\n";
$query .= "Insert into nonuser_optin (nonuser_signup_id, optin_type_id,
promotion_id, signup_date) values (@nonuser_signup_id, $Contests, 5,
getdate())\n";
$query .= "Insert into nonuser_optin (nonuser_signup_id, optin_type_id,
promotion_id, signup_date) values (@nonuser_signup_id, $Surveys, 5,
getdate())\n";
$query .= "Insert into nonuser_optin (nonuser_signup_id, optin_type_id,
promotion_id, signup_date) values (@nonuser_signup_id, $Shopping, 5,
getdate())\n";
$query .= "Insert into nonuser_optin (nonuser_signup_id, optin_type_id,
promotion_id, signup_date) values (@nonuser_signup_id, $Games, 5,
getdate())\n";
$result = sybase_query($query, $db);
$query = '';
$query .= "Insert into user_optin (user_id,optin_type_id,promotion_id)
values ($user_id, 10, 5)\n";
$result = sybase_query($query, $db);
The last insert doesn't work. Is this a bug in PHP or am I overlooking
something? Logically it seems that it should function the same in both
cases.
I should also note that when I break that up into a series of completely
independant queries it works fine too, I can run a series of single inserts
each with their own sybase_query and they all insert fine.
However as you can see in the case above I am using a @@identity which makes
splitting these up into different queries impossible.
Thnaks in advance for any aid.
Ben
--
PHP Development Mailing List
Re: [PHP-DEV] Multiple inserts in one sybase_query issue
OK, I understand that php won't recieve multiple result sets, but in the
case of a series of inserts why is this an issue?
One thing I had hoped to do was to run the first insert and then select the
@@identity. The lack of the ability to get multiple result sets is obviously
why I can't retrieve the @@identity using sybase_result() because it
discards the @@identity result set after it gets the result from the insert.
Sybase however still executes the second query I believe, the result set is
just lost in the transaction. Presuming this is the case I should be able to
affect as many inserts and updates as I want providing I don't need to query
out the result sets, correct?
Finally I'm not clear on how the multiple result sets would effect the
subsequent query? Once i reassign the values $query and $result don;t I get
a brand new result set?
I agree that developing a method to get an array of result sets would be
extremely useful, but I don;t think that its my issue here. Am I dealing
with a bug here?
~Ben
"Jo Giraerts" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> Well, what you are doing in the second query is actually getting more
> resultsets in one query. The current sybase modules can't handle more
> than 1 resultset at a time. Try running sp_help through sybase_query(),
> you'll notice that only the first resultset is given back, all the rest
> is discarded. I'm thinking already a while about changing this so you
> can get an array of resultsets from sybase_query() which would allow you
> to do the stuff you're trying to do now..
>
> Any ideas of the group would be appreciated (as i'm not a very good
> c-coder anyway)
>
>
>
> On Mon, Jun 18, 2001 at 06:01:54PM -0500, Ben Gabrielson wrote:
> > I'm running PHP 3 (and PHP 4 on another server) with Sybase 11.9.2 on
the
> > database server.
> >
> > I have tried in a number or variations to insert a series of updates and
> > inserts in the same sybase_query. I build a $query consisting of around
> > 15-20 inserts and updates and call the sybase_query function, everything
> > works fine and it writes to the database as expected however none of the
> > sybase_querys after this point are recorded.
> >
> > For example when I create this string:
> >
> > $query = "Declare @nonuser_signup_id numeric(10,0)\n";
> > $query .= "Insert into nonuser_signup (email, firstname, lastname,
gender)
> > values ('$email', '$firstname', '$lastname', '$gender')\n";
> > $query .= "Select @nonuser_signup_id = @@identity\n";
> > $query .= "Insert into nonuser_optin (nonuser_signup_id, optin_type_id,
> > promotion_id, signup_date) values (@nonuser_signup_id, $Free_Stuff, 5,
> > getdate())\n";
> > $query .= "Insert into nonuser_optin (nonuser_signup_id, optin_type_id,
> > promotion_id, signup_date) values (@nonuser_signup_id, $Contests, 5,
> > getdate())\n";
> > $query .= "Insert into nonuser_optin (nonuser_signup_id, optin_type_id,
> > promotion_id, signup_date) values (@nonuser_signup_id, $Surveys, 5,
> > getdate())\n";
> > $query .= "Insert into nonuser_optin (nonuser_signup_id, optin_type_id,
> > promotion_id, signup_date) values (@nonuser_signup_id, $Shopping, 5,
> > getdate())\n";
> > $query .= "Insert into nonuser_optin (nonuser_signup_id, optin_type_id,
> > promotion_id, signup_date) values (@nonuser_signup_id, $Games, 5,
> > getdate())\n";
> > $query .= "Insert into user_optin (user_id,optin_type_id,promotion_id)
> > values ($user_id, 10, 5)\n";
> > $result = sybase_query($query, $db);
> >
> >
> > Everything works fine, however when its broken up like this:
> >
> > $query = "Declare @nonuser_signup_id numeric(10,0)\n";
> > $query .= "Insert into nonuser_signup (email, firstname, lastname,
gender)
> > values ('$email', '$firstname', '$lastname', '$gender')\n";
> > $query .= "Select @nonuser_signup_id = @@identity\n";
> > $query .= "Insert into nonuser_optin (nonuser_signup_id, optin_type_id,
> > promotion_id, signup_date) values (@nonuser_signup_id, $Free_Stuff, 5,
> > getdate())\n";
> > $query .= "Insert into nonuser_optin (nonuser_signup_id, optin_type_id,
> > promotion_id, signup_date) values (@nonuser_signup_id, $Contests, 5,
> > getdate())\n";
> > $query .= "Insert into nonuser_optin (nonuser_signup_id, optin_type_id,
> > promotion_id, signup_date) values (@nonuser_signup_id, $Surveys, 5,
&g
