[PHP-DB] PDO and SAP HANA prepared statements issue
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š
Re: [PHP-DB] PDO and SAP HANA prepared statements issue
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
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
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
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
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
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
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
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
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 > >