Re: [Sqlrelay-discussion] PHP Pear SQLRelay driver patch

[David M. <dav...@fi...>]

Thanks for the fix, I just committed it to CVS.  You're right, it should
be this->$connection.  Odd though, that it always worked for me without
that fix.  I guess I never called a function that needed
this->$connection to be set in the cursor after running a query :/

Here's how I run prepared statements in my test programs:

Bind by name:
	$res=$db->prepare("insert into testtable values
(:var1,:var2,:var3,:var4,:var5)");
	$bindvars=array("var1" => 2,
			"var2" => "testchar2",
			"var3" => "testvarchar2",
			"var4" => "01-JAN-2002",
			"var5" => "testlong2");
	$res=$db->execute($res,$bindvars);

or

Bind by position:
	$res=$db->prepare("insert into testtable values
(:var1,:var2,:var3,:var4,:var5)");
	$bindvars=array("1" => 2,
			"2" => "testchar2",
			"3" => "testvarchar2",
			"4" => "01-JAN-2002",
			"5" => "testlong2");
	$res=$db->execute($res,$bindvars);

Note the colon-delimited bind variables.  Some databases support bind
variables natively (oracle, sybase, msssql server, interbase, db2 and
postgresql) and for those databases, sqlrelay uses the native bind
syntax.  Ie, for sybase/mssqlserver, the query should look like:

insert into testtable values (@var1,@var2,@var3,@var4)

for DB2:

insert into testtable values (?,?,?,?)

For databases that don't support bind variables natively, SQL Relay
fakes them by rewriting the query.  For those databases, you have to use
colon-delimited bind variables.

MySQL 4.1 (I think) and 5.0 (for sure) support bind variables natively
as well, but SQL Relay doesn't yet use those features, so for now, when
using mysql, SQL Relay still fakes bind variables.  I belive MySQL
uses ?'s to denote bind variables, so eventually, when SQL Relay
supports native bind variables with MySQL, you'll be able to write
queries like:

insert into testtable values (?,?,?,?)

but for now you have to use colon-delimited names.

Hope this helps,

David Muse
dav...@fi...

On Fri, 2005-12-09 at 11:19 -0500, Gena01 wrote:
> I found out about SQLRelay recently. So I tried to set it up and get
> our stuff running against it. I grabbed the latest stable release from
> download section. It seems that the Pear driver for SQL relay in 26.4
> is quite buggy. So i went and pulled the code from cvs. There are
> still some bugs in that code that I found and fixed in my copy.
> 
> Specifically:
> 
>  function prepare($query)
>     {
>         $cursor = sqlrcur_alloc($this->connection); 
>         sqlrcur_setResultSetBufferSize($cursor,100);
>         sqlrcur_prepareQuery($cursor, $query);
>         return new DB_sqlrelay_cursor($cursor,$connection);  <--- no
> such variable, should be $this->connection 
>     }
> 
> 
> What do I need to do to get Prepared Statements to work. Esp in Pear
> the ones that look like
> SELECT a 
> FROM table 
> WHERE id = ?
> 
> Thank you,
> 
> Gena01