PDO and MS Sql Server

I'm using PHP 5.1. The documentation for PDO doesn't list MS Sql server =
as one of the drivers that support PDO but there is a php_pdo_mssql.dll =
which seems to work so I'm using that.

I need to get the id of a new record just inserted. I can't use lastInsert=
Id() as I get a message saying it's not supported. So I've created a =
stored proc that returns the id just created (code below). The insert =
works fine but the $emailid variable is not populated. I can run the =
stored proc in query analyzer and it outputs the id correctly so I know =
the stored proc works. All the examples I see return strings, not sure if =
that has anything to do with it. And as for the parameter length for the =
output parameter, I've tried nothing as well as 99999.

$stmt =3D $this->dbh->prepare("exec usp_EmailInsert :projectid, :mailfrom, =
:mailto, :mailcc, :subject, :body, :mimefilename, :emailid");
$stmt->bindParam(':projectid', $projectid, PDO::PARAM_INT);
$stmt->bindParam(':mailfrom', $from, PDO::PARAM_STR, 100);
$stmt->bindParam(':mailto', $to, PDO::PARAM_STR, 500);
$stmt->bindParam(':mailcc', $cc, PDO::PARAM_STR, 500);
$stmt->bindParam(':subject', $subject, PDO::PARAM_STR, 1000);
$stmt->bindParam(':body', $body, PDO::PARAM_LOB);
$stmt->bindParam(':mimefilename', $mimefilename, PDO::PARAM_STR, 500); =
=09
$stmt->bindParam(':emailid', $emailid, PDO::PARAM_INT, 99999);
$stmt->execute();
=09
echo "\nemailid =3D $emailid\n";



Anyone have any ideas? Thanks.


Regards,

Bruce

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: PDO and MS Sql Server

Bruce Cowin wrote:
> I'm using PHP 5.1. The documentation for PDO doesn't list MS Sql server as one of the drivers that support PDO but there is a php_pdo_mssql.dll which seems to work so I'm using that.
>
> I need to get the id of a new record just inserted. I can't use lastInsertId() as I get a message saying it's not supported. So I've created a stored proc that returns the id just created (code below). The insert works fine but the $emailid variable is not populated. I can run the stored proc in query analyzer and it outputs the id correctly so I know the stored proc works. All the examples I see return strings, not sure if that has anything to do with it. And as for the parameter length for the output parameter, I've tried nothing as well as 99999.
>
> $stmt = $this->dbh->prepare("exec usp_EmailInsert :projectid, :mailfrom, :mailto, :mailcc, :subject, :body, :mimefilename, :emailid");
> $stmt->bindParam(':projectid', $projectid, PDO::PARAM_INT);
> $stmt->bindParam(':mailfrom', $from, PDO::PARAM_STR, 100);
> $stmt->bindParam(':mailto', $to, PDO::PARAM_STR, 500);
> $stmt->bindParam(':mailcc', $cc, PDO::PARAM_STR, 500);
> $stmt->bindParam(':subject', $subject, PDO::PARAM_STR, 1000);
> $stmt->bindParam(':body', $body, PDO::PARAM_LOB);
> $stmt->bindParam(':mimefilename', $mimefilename, PDO::PARAM_STR, 500);
> $stmt->bindParam(':emailid', $emailid, PDO::PARAM_INT, 99999);
> $stmt->execute();

prepared statements are for ingoing queries, they can't put results from
that query into a binded parameter.

That is, when you bind a parameter it only works for the query TO the
database, they are not filled in for outgoing results.

Can you get your stored procedure to return the new id?

I'm not sure how this works for a stored procedure, but see the examples
here:

http://www.php.net/manual/en/function.PDO-prepare.php

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: PDO and MS Sql Server

Thanks for replying. According to the PDO doco: "If the database driver =
supports it, you may also bind parameters for output as well as input." =
So maybe this driver doesn't support it? I don't know. I'll try and get =
the stored proc to return the value, but not sure how I'll capture that =
yet. If that doesn't work, I'll just do a "select @@identity".

Thanks again.



Regards,

Bruce

>>> Chris 6/07/2007 12:44:34 p.m. >>>
Bruce Cowin wrote:
> I'm using PHP 5.1. The documentation for PDO doesn't list MS Sql server =
as one of the drivers that support PDO but there is a php_pdo_mssql.dll =
which seems to work so I'm using that.
>=20
> I need to get the id of a new record just inserted. I can't use =
lastInsertId() as I get a message saying it's not supported. So I've =
created a stored proc that returns the id just created (code below). The =
insert works fine but the $emailid variable is not populated. I can run =
the stored proc in query analyzer and it outputs the id correctly so I =
know the stored proc works. All the examples I see return strings, not =
sure if that has anything to do with it. And as for the parameter length =
for the output parameter, I've tried nothing as well as 99999.
>=20
> $stmt =3D $this->dbh->prepare("exec usp_EmailInsert :projectid, =
:mailfrom, :mailto, :mailcc, :subject, :body, :mimefilename, :emailid");
> $stmt->bindParam(':projectid', $projectid, PDO::PARAM_INT);
> $stmt->bindParam(':mailfrom', $from, PDO::PARAM_STR, 100);
> $stmt->bindParam(':mailto', $to, PDO::PARAM_STR, 500);
> $stmt->bindParam(':mailcc', $cc, PDO::PARAM_STR, 500);
> $stmt->bindParam(':subject', $subject, PDO::PARAM_STR, 1000);
> $stmt->bindParam(':body', $body, PDO::PARAM_LOB);
> $stmt->bindParam(':mimefilename', $mimefilename, PDO::PARAM_STR, 500); =
=09
> $stmt->bindParam(':emailid', $emailid, PDO::PARAM_INT, 99999);
> $stmt->execute();

prepared statements are for ingoing queries, they can't put results =
from=20
that query into a binded parameter.

That is, when you bind a parameter it only works for the query TO the=20
database, they are not filled in for outgoing results.

Can you get your stored procedure to return the new id?

I'm not sure how this works for a stored procedure, but see the examples=20=

here:

http://www.php.net/manual/en/function.PDO-prepare.php=20

--=20
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: PDO and MS Sql Server

Bruce Cowin wrote:
> Thanks for replying. According to the PDO doco: "If the database driver supports it, you may also bind parameters for output as well as input." So maybe this driver doesn't support it? I don't know. I'll try and get the stored proc to return the value, but not sure how I'll capture that yet. If that doesn't work, I'll just do a "select @@identity".

Ah didn't know that :)

Maybe check with the php-general list to see if anyone else had similar
experience with mssql & the pdo driver.

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php