Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Alko Kotalko]

Your answer in the part of SAP HANA (J)ODBC driver not supporting named
parameters actually makes sense. However I don't understand why would there
be a difference between PDO and regular odbc (odbc_connect, etc.) commands
since they're obviously using the same driver.

My testing machine is on Windows and when using raw odbc commands, I pass
in the driver parameter 'HDBODBC32'. If I use PDO, I have to pass in the
ODBC DSN, which is created in the Windows ODBC management tool. I double
checked and the driver in use is the same. So what bugs me is why would raw
odbc commands accept parameters with ? and $ notations but PDO doesn't
accept neither (well, query executes but doesn't return any results).

I'd like to try and debug the PDO module but unfortunately that's beyond my
knowledge and time frame for the moment :) Perhaps any advice on this,
debugging PDO? If nothing else, using PDO would at least unify the database
access accross my application and it does seem cleaner to use.

On Tue, Jan 26, 2016 at 3:50 PM, Lester Caine  wrote:

> On 26/01/16 19:06, Alko Kotalko wrote:
> > I've tried all the notations with PDO as well and none of them work with
> > SAP HANA. It works with MySQL though. So I presume that there is either a
> > bug in PDO driver or there is some mismatch between PDO and SAP HANA.
>
> Firebird does not support named parameters so we stick with ?
> parameters. Not sure on SAP HANA, but a quick google gives
> "The SAP HANA JDBC driver doesn't support named parameters" and I would
> anticipate that the ODBC driver probably has the same problem, but the
> simpler ordered array of parameters should work ...
>
> There WAS a document about just what combinations HAVE been tested and
> worked, but I'm not even sure now where it as created :(
>
> From the generic ODBC driver -
> http://php.net/manual/en/function.odbc-prepare.php ...
> Some databases (such as IBM DB2, MS SQL Server, and Oracle) support
> stored procedures that accept parameters of type IN, INOUT, and OUT as
> defined by the ODBC specification. However, the Unified ODBC driver
> currently only supports parameters of type IN to stored procedures.
>
> It is some time since I used the ODBC interface, but I think PDO_odbc
> still uses the generic ODBC inerface and this does have it's own
> restrictons based on what platform you are using. It may be worth trying
> the generic ODBC interface and see if this works any differently. The
> ODBC driver uses the same style of working as the Firebird/Interbase
> driver for binding variables and that does work.
>
> --
> Lester Caine - G8HFL
> -
> Contact - http://lsces.co.uk/wiki/?page=contact
> L.S.Caine Electronic Services - http://lsces.co.uk
> EnquirySolve - http://enquirysolve.com/
> Model Engineers Digital Workshop - http://medw.co.uk
> Rainbow Digital Media - http://rainbowdigitalmedia.co.uk
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Karl DeSaulniers]

On Jan 26, 2016, at 7:10 AM, Alko Kotalko  wrote:

> Hi,
> 
> I have a working connection from PHP to SAP HANA through PDO and regular
> ODBC commands.
> 
> The issue is that through PDO I can not get any prepared statements to
> work. None of the notations (?, $, :) work. The response from the server
> (fetch) gets me empty field values for all selected columns and if I try
> fetchAll the PHP script runs out of memory.
> 
> ODBC commands actually work with the ? and colon ($) notations. But not
> with colon (:). I suppose this is due to the lack of named parameters
> support in ODBC commands (haven't actually confirmed that though). The $
> notation brings me the closest to named parameters because a specific
> number can be repeated.
> 
> For example:
> "SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1" works.
> 
> However it is not ideal. I would like to know if anyone has any experience
> with SAP HANA and I can offer my help and participation in order to debug
> the possible problems with PDO<->HANA connectivity issues (in regards to
> prepared statements).
> 
> BR Aleš


Pardon me for asking, but shouldn't this line..

"SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1"

be

"SELECT * FROM dummy WHERE col1=".$1." AND col2=".$2." AND col3=".$1."  "

or like this

"SELECT * FROM dummy WHERE col1='".$1."' AND col2='".$2."' AND col3='".$1."'  "

?

I have found that letting php create the string without the quotes and periods 
in a SQL statement can cause issues.
Don't ask me why it causes issues, I couldn't tell you :)  Just my experience. 

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com


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

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Jeff]

Greetings,

On 01/26/2016 08:18 AM, Karl DeSaulniers wrote:
> On Jan 26, 2016, at 7:10 AM, Alko Kotalko  wrote:
> 
>> Hi,
>>
>> I have a working connection from PHP to SAP HANA through PDO and regular
>> ODBC commands.
>>
[...snipped...]

>>
>> For example:
>> "SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1" works.

the "$" character within a php string enclosed with quotes (") is
special. you /might/ have some success if you enclose the string in
single quotes (') instead. also personally I prefer named parameters
(:foo) or even "foo=?" (question mark) rather than $1...n etc.

[...snipped...]

regards,
J


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

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Lester Caine]

On 26/01/16 13:10, Alko Kotalko wrote:
> ODBC commands actually work with the ? and colon ($) notations. But not
> with colon (:). I suppose this is due to the lack of named parameters
> support in ODBC commands (haven't actually confirmed that though). The $
> notation brings me the closest to named parameters because a specific
> number can be repeated.

This is the key to your problem ...

The PDO generic process does not support named parameters directly, so
the ? format duplicating the multiple use of a named parameter is
required ... if the driver actually supports that?

Does your setup work if the SQL is

$stmt = $dbh->prepare("SELECT * FROM dummy WHERE col1=? AND col2=? AND
col3=?");
$stmt->bindParam(1, $S1);
$stmt->bindParam(2, $S2);
$stmt->bindParam(3, $S1);

On some drivers the named parameters have to be converted to '?' format,
but I can't find the notes now on what combination works and what does't :(

-- 
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

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

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Alko Kotalko]

I've tried all the notations with PDO as well and none of them work with
SAP HANA. It works with MySQL though. So I presume that there is either a
bug in PDO driver or there is some mismatch between PDO and SAP HANA.

The queries get executed but the returned result (if I fetch one) is an
associative array with empty values. If I fetch all, PHP runs out of memory
but the arrays in array are the same, keys without values.

On Tue, Jan 26, 2016 at 9:17 AM, Lester Caine  wrote:

> On 26/01/16 13:10, Alko Kotalko wrote:
> > ODBC commands actually work with the ? and colon ($) notations. But not
> > with colon (:). I suppose this is due to the lack of named parameters
> > support in ODBC commands (haven't actually confirmed that though). The $
> > notation brings me the closest to named parameters because a specific
> > number can be repeated.
>
> This is the key to your problem ...
>
> The PDO generic process does not support named parameters directly, so
> the ? format duplicating the multiple use of a named parameter is
> required ... if the driver actually supports that?
>
> Does your setup work if the SQL is
>
> $stmt = $dbh->prepare("SELECT * FROM dummy WHERE col1=? AND col2=? AND
> col3=?");
> $stmt->bindParam(1, $S1);
> $stmt->bindParam(2, $S2);
> $stmt->bindParam(3, $S1);
>
> On some drivers the named parameters have to be converted to '?' format,
> but I can't find the notes now on what combination works and what does't :(
>
> --
> Lester Caine - G8HFL
> -
> Contact - http://lsces.co.uk/wiki/?page=contact
> L.S.Caine Electronic Services - http://lsces.co.uk
> EnquirySolve - http://enquirysolve.com/
> Model Engineers Digital Workshop - http://medw.co.uk
> Rainbow Digital Media - http://rainbowdigitalmedia.co.uk
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Alko Kotalko]

It shouldn't be like that because I'm preparing a statement, which would
later have had parameters passed to. I'm not trying to concatenate a string.
(Sorry, forgot to reply to all before)

On Tue, Jan 26, 2016 at 8:18 AM, Karl DeSaulniers 
wrote:

> On Jan 26, 2016, at 7:10 AM, Alko Kotalko  wrote:
>
> > Hi,
> >
> > I have a working connection from PHP to SAP HANA through PDO and regular
> > ODBC commands.
> >
> > The issue is that through PDO I can not get any prepared statements to
> > work. None of the notations (?, $, :) work. The response from the server
> > (fetch) gets me empty field values for all selected columns and if I try
> > fetchAll the PHP script runs out of memory.
> >
> > ODBC commands actually work with the ? and colon ($) notations. But not
> > with colon (:). I suppose this is due to the lack of named parameters
> > support in ODBC commands (haven't actually confirmed that though). The $
> > notation brings me the closest to named parameters because a specific
> > number can be repeated.
> >
> > For example:
> > "SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1" works.
> >
> > However it is not ideal. I would like to know if anyone has any
> experience
> > with SAP HANA and I can offer my help and participation in order to debug
> > the possible problems with PDO<->HANA connectivity issues (in regards to
> > prepared statements).
> >
> > BR Aleš
>
>
> Pardon me for asking, but shouldn't this line..
>
> "SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1"
>
> be
>
> "SELECT * FROM dummy WHERE col1=".$1." AND col2=".$2." AND col3=".$1."  "
>
> or like this
>
> "SELECT * FROM dummy WHERE col1='".$1."' AND col2='".$2."' AND
> col3='".$1."'  "
>
> ?
>
> I have found that letting php create the string without the quotes and
> periods in a SQL statement can cause issues.
> Don't ask me why it causes issues, I couldn't tell you :)  Just my
> experience.
>
> Best,
>
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Alko Kotalko]

This (my code) actually works so it's not part of the problem. The problem
is that I can NOT use the colon notation for named parameters, even though
that's my goal :) If I use the colon notation with MySQL (PDO) it works
fine. With ODBC and SAP HANA database it doesn't. I have to use either ? or
$ notation.
(Sorry, forgot to reply to all before)

On Tue, Jan 26, 2016 at 9:01 AM, Jeff  wrote:

> Greetings,
>
> On 01/26/2016 08:18 AM, Karl DeSaulniers wrote:
> > On Jan 26, 2016, at 7:10 AM, Alko Kotalko 
> wrote:
> >
> >> Hi,
> >>
> >> I have a working connection from PHP to SAP HANA through PDO and regular
> >> ODBC commands.
> >>
> [...snipped...]
>
> >>
> >> For example:
> >> "SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1" works.
>
> the "$" character within a php string enclosed with quotes (") is
> special. you /might/ have some success if you enclose the string in
> single quotes (') instead. also personally I prefer named parameters
> (:foo) or even "foo=?" (question mark) rather than $1...n etc.
>
> [...snipped...]
>
> regards,
> J
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Karl DeSaulniers]

Oh ok, thanks for the clarification. Sorry for the noise.

Best,

Karl DeSaulniers
Design Drumm
http://designdrumm.com



On Jan 26, 2016, at 1:07 PM, Alko Kotalko  wrote:

> It shouldn't be like that because I'm preparing a statement, which would 
> later have had parameters passed to. I'm not trying to concatenate a string.
> (Sorry, forgot to reply to all before)
> 
> On Tue, Jan 26, 2016 at 8:18 AM, Karl DeSaulniers  
> wrote:
> On Jan 26, 2016, at 7:10 AM, Alko Kotalko  wrote:
> 
> > Hi,
> >
> > I have a working connection from PHP to SAP HANA through PDO and regular
> > ODBC commands.
> >
> > The issue is that through PDO I can not get any prepared statements to
> > work. None of the notations (?, $, :) work. The response from the server
> > (fetch) gets me empty field values for all selected columns and if I try
> > fetchAll the PHP script runs out of memory.
> >
> > ODBC commands actually work with the ? and colon ($) notations. But not
> > with colon (:). I suppose this is due to the lack of named parameters
> > support in ODBC commands (haven't actually confirmed that though). The $
> > notation brings me the closest to named parameters because a specific
> > number can be repeated.
> >
> > For example:
> > "SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1" works.
> >
> > However it is not ideal. I would like to know if anyone has any experience
> > with SAP HANA and I can offer my help and participation in order to debug
> > the possible problems with PDO<->HANA connectivity issues (in regards to
> > prepared statements).
> >
> > BR Aleš
> 
> 
> Pardon me for asking, but shouldn't this line..
> 
> "SELECT * FROM dummy WHERE col1=$1 AND col2=$2 AND col3=$1"
> 
> be
> 
> "SELECT * FROM dummy WHERE col1=".$1." AND col2=".$2." AND col3=".$1."  "
> 
> or like this
> 
> "SELECT * FROM dummy WHERE col1='".$1."' AND col2='".$2."' AND col3='".$1."'  
> "
> 
> ?
> 
> I have found that letting php create the string without the quotes and 
> periods in a SQL statement can cause issues.
> Don't ask me why it causes issues, I couldn't tell you :)  Just my experience.
> 
> Best,
> 
> Karl DeSaulniers
> Design Drumm
> http://designdrumm.com
> 
> 
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 
> 

Re: [PHP-DB] PDO and SAP HANA prepared statements issue

[Lester Caine]

On 26/01/16 19:06, Alko Kotalko wrote:
> I've tried all the notations with PDO as well and none of them work with
> SAP HANA. It works with MySQL though. So I presume that there is either a
> bug in PDO driver or there is some mismatch between PDO and SAP HANA.

Firebird does not support named parameters so we stick with ?
parameters. Not sure on SAP HANA, but a quick google gives
"The SAP HANA JDBC driver doesn't support named parameters" and I would
anticipate that the ODBC driver probably has the same problem, but the
simpler ordered array of parameters should work ...

There WAS a document about just what combinations HAVE been tested and
worked, but I'm not even sure now where it as created :(

>From the generic ODBC driver -
http://php.net/manual/en/function.odbc-prepare.php ...
Some databases (such as IBM DB2, MS SQL Server, and Oracle) support
stored procedures that accept parameters of type IN, INOUT, and OUT as
defined by the ODBC specification. However, the Unified ODBC driver
currently only supports parameters of type IN to stored procedures.

It is some time since I used the ODBC interface, but I think PDO_odbc
still uses the generic ODBC inerface and this does have it's own
restrictons based on what platform you are using. It may be worth trying
the generic ODBC interface and see if this works any differently. The
ODBC driver uses the same style of working as the Firebird/Interbase
driver for binding variables and that does work.

-- 
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

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