sqlrelay-discussion

[Sqlrelay-discussion] php, bind variables and postgresql

[Chris <dm...@gm...>]

Hi all,

I'm looking to use sqlrelay with php & postgresql but having some 
trouble getting it working properly.

Using sqlrelay 0.39.4 (compiled from source) on ubuntu (8.0.4) against 
postgresql 8.3 (ubuntu package).

I've tried the pear db method and the non-pear method but both seem to 
not process bind variables correctly.

Copied pretty much verbatim from the manual (except tablename & field 
names changed):

<?php

dl("sql_relay.so");

$con=sqlrcon_alloc("localhost",9000,"","user1","password1",0,1);

$cur=sqlrcur_alloc($con);

sqlrcur_prepareQuery($cur,"select * from sq_ast where assetid=:assetid");
sqlrcur_inputBind($cur,"assetid","43");

sqlrcur_executeQuery($cur);

for ($row=0; $row<sqlrcur_rowCount($cur); $row++) {
	echo "Row: " . $row . "\n";
	for ($col=0; $col<sqlrcur_colCount($cur); $col++) {
		echo sqlrcur_getField($cur,$row,$col);
		echo ",";
	}
	echo "\n";
}

sqlrcur_free($cur);
sqlrcon_free($con);

Postgres error log says:

ERROR:  syntax error at or near ":" at character 36
STATEMENT:  select * from sq_ast where assetid=:assetid

I'm sure I'm missing something really simple but can't work out what it 
is. Any suggestions very welcome.

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

Re: [Sqlrelay-discussion] php, bind variables and postgresql

[Martin F. <mar...@po...>]

Chris schreef:
> sqlrcur_prepareQuery($cur,"select * from sq_ast where assetid=:assetid");
> sqlrcur_inputBind($cur,"assetid","43");
> ERROR:  syntax error at or near ":" at character 36
> STATEMENT:  select * from sq_ast where assetid=:assetid
>
> I'm sure I'm missing something really simple but can't work out what it 
> is. Any suggestions very welcome.
>   

Try

sqlrcur_inputBind($cur,":assetid","43");

??

(The colon should be in the name of the bindvar.)

Regards,

Martin

Re: [Sqlrelay-discussion] php, bind variables and postgresql

[Chris <dm...@gm...>]

Martin Fleurke wrote:
> Chris schreef:
>> sqlrcur_prepareQuery($cur,"select * from sq_ast where assetid=:assetid");
>> sqlrcur_inputBind($cur,"assetid","43");
>> ERROR:  syntax error at or near ":" at character 36
>> STATEMENT:  select * from sq_ast where assetid=:assetid
>>
>> I'm sure I'm missing something really simple but can't work out what 
>> it is. Any suggestions very welcome.
>>   
> 
> Try
> 
> sqlrcur_inputBind($cur,":assetid","43");
> 
> ??
> 
> (The colon should be in the name of the bindvar.)

I got the same thing as before (postgres throwing an error).

Could I have done something wrong with the compiling/configuring (though 
I'm not really sure what it could be).. ?

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

Re: [Sqlrelay-discussion] php, bind variables and postgresql

[Chris <dm...@gm...>]

David Muse wrote:
> There are 2 solutions; use postgresql native bind format or add
> fakebinds=yes to the string attribute of the connection tag in your
> sqlrelay.conf file.
> 
> Prior to version 8.0, postgres did not support bind variables so
> sqlrelay faked them by rewriting the query.  These days, sqlrelay
> attempts to use postgres's native binds, but you can override that and
> fall back to the old behavior using fakebinds=yes.
> 
> If you want to use native postgresql binds, you'll need to rewrite your
> queries, replacing :assetid with a ? and then in the inputBind call,
> you'll need to refer to each bind variable by number rather than name.
> 
> For example:
> 
> sqlrcur_prepareQuery($cur,"select * from sq_ast where assetid=? and
> otherid=?");
> sqlrcur_inputBind($cur,"1","43");
> sqlrcur_inputBind($cur,"2","100");

Ahh. That works.

I tried using fakebinds (since it's more "pdo" style which is what the 
app is using) but I must have it wrong.

<connection connectionid="pgsql" 
string="user=user;password=pass;db=dbname;host=127.0.0.1;port=5432;fakebinds=yes;" 
metric="1" behindloadbalancer="no" />

Does that look right?

Of course I restarted sqlrelay after adding the fakebinds param in..

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

Re: [Sqlrelay-discussion] php, bind variables and postgresql

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

Yep, looks right to me.

On Tue, 2008-11-11 at 10:49 +1100, Chris wrote:
> David Muse wrote:
> > There are 2 solutions; use postgresql native bind format or add
> > fakebinds=yes to the string attribute of the connection tag in your
> > sqlrelay.conf file.
> > 
> > Prior to version 8.0, postgres did not support bind variables so
> > sqlrelay faked them by rewriting the query.  These days, sqlrelay
> > attempts to use postgres's native binds, but you can override that and
> > fall back to the old behavior using fakebinds=yes.
> > 
> > If you want to use native postgresql binds, you'll need to rewrite your
> > queries, replacing :assetid with a ? and then in the inputBind call,
> > you'll need to refer to each bind variable by number rather than name.
> > 
> > For example:
> > 
> > sqlrcur_prepareQuery($cur,"select * from sq_ast where assetid=? and
> > otherid=?");
> > sqlrcur_inputBind($cur,"1","43");
> > sqlrcur_inputBind($cur,"2","100");
> 
> Ahh. That works.
> 
> I tried using fakebinds (since it's more "pdo" style which is what the 
> app is using) but I must have it wrong.
> 
> <connection connectionid="pgsql" 
> string="user=user;password=pass;db=dbname;host=127.0.0.1;port=5432;fakebinds=yes;" 
> metric="1" behindloadbalancer="no" />
> 
> Does that look right?
> 
> Of course I restarted sqlrelay after adding the fakebinds param in..
> 


_____________________________________________________________
Veteran's Day Domain Sales: Hosting + Domain = US$5.95/year
Offer Ends: Nov 12, 2008.       http://www.doteasy.com 

Re: [Sqlrelay-discussion] php, bind variables and postgresql

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

There are 2 solutions; use postgresql native bind format or add
fakebinds=yes to the string attribute of the connection tag in your
sqlrelay.conf file.

Prior to version 8.0, postgres did not support bind variables so
sqlrelay faked them by rewriting the query.  These days, sqlrelay
attempts to use postgres's native binds, but you can override that and
fall back to the old behavior using fakebinds=yes.

If you want to use native postgresql binds, you'll need to rewrite your
queries, replacing :assetid with a ? and then in the inputBind call,
you'll need to refer to each bind variable by number rather than name.

For example:

sqlrcur_prepareQuery($cur,"select * from sq_ast where assetid=? and
otherid=?");
sqlrcur_inputBind($cur,"1","43");
sqlrcur_inputBind($cur,"2","100");

Either way should work.

On Tue, 2008-11-11 at 08:50 +1100, Chris wrote:
> Martin Fleurke wrote:
> > Chris schreef:
> >> sqlrcur_prepareQuery($cur,"select * from sq_ast where assetid=:assetid");
> >> sqlrcur_inputBind($cur,"assetid","43");
> >> ERROR:  syntax error at or near ":" at character 36
> >> STATEMENT:  select * from sq_ast where assetid=:assetid
> >>
> >> I'm sure I'm missing something really simple but can't work out what 
> >> it is. Any suggestions very welcome.
> >>   
> > 
> > Try
> > 
> > sqlrcur_inputBind($cur,":assetid","43");
> > 
> > ??
> > 
> > (The colon should be in the name of the bindvar.)
> 
> I got the same thing as before (postgres throwing an error).
> 
> Could I have done something wrong with the compiling/configuring (though 
> I'm not really sure what it could be).. ?
> 


_____________________________________________________________
Veteran's Day Domain Sales: Hosting + Domain = US$5.95/year
Offer Ends: Nov 12, 2008.       http://www.doteasy.com