Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 14 total
Thread Problems with bound parameters
Mon, Dec 10 2018 10:53 AMPermanent Link

Eric

US Engine Production

We're having issues using PHP (PDO) and parameters.  Please see the code below:
Table version = 4
ODBC Driver version = 4.40
PHP version = 7.2.4

       $db = new PDO($connection_string, $dbisam_username, $dbisam_password);
       
       echo "CONNECTION OK<br>";

       $stmt = $db->prepare("SELECT * FROM Warranty");
       $stmt->execute();   
       $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
       $count = $stmt->rowCount();        
       echo "<br>" . $count . "<br>";  
       foreach ($result as $row) {
           echo $row['WarrantyID'] . " - " . $row['Notes'] . "<hr>";
       }         
      
      ^^^ the above section returns data from all 147 rows in the table

       $accountname = 'ATK NORTH AMERICA';
       $stmt = $db->prepare("SELECT * FROM Warranty WHERE AccountName = :a");
       $stmt->bindParam(':a', $accountname, PDO::PARAM_STR, strlen($accountname));
       $stmt->execute();   
       $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
       $count = $stmt->rowCount();        
       echo "<br>" . $count . "<br>";  
       foreach ($result as $row) {
           echo $row['WarrantyID'] . " - " . $row['Notes'] . "<hr>";
       }            
      
      ^^^ this section returns only 1 row - a row (the only one) where AccountName is NULL
       
       $stmt = $db->prepare("SELECT * FROM Warranty WHERE AccountName = 'ATK NORTH AMERICA'");
       $stmt->execute();   
       $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
       $count = $stmt->rowCount();        
       echo "<br>" . $count . "<br>";  
       foreach ($result as $row) {
           echo $row['WarrantyID'] . " - " . $row['Notes'] . "<hr>";
       }         
      
      ^^^ this section returns 31 rows that match the AccountName

       $db = null; //close the connections
      
Can you give me any insight into what I may be doing wrong?
Ultimately, we need to have it working with bound parameters, as we need to update BLOB fields,
and my understanding is that the only way to do that is to use parameters.  If you could look at
the code below also, and verify that this is the correct way to update a BLOB field, that would be great.

       $newnotes = 'This is a test.';
      $warrantyid = 10031;
       $stmt = $db->prepare("UPDATE Warranty SET Notes = :b WHERE WarrantyID = :a");
       $stmt->bindParam(':a', $warrantyid, PDO::PARAM_INT);
      $stmt->bindParam(':b', $newnotes, PDO::PARAM_LOB);
       $stmt->execute();   
Mon, Dec 10 2018 3:20 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Eric,

I need to install PHP 7.x and take a look.  I suspect that the PDO layer is not binding the parameters properly, or is trying to get the parameter data types and screwing it up.

Tim Young
Elevate Software
www.elevatesoft.com
Tue, Dec 11 2018 3:05 PMPermanent Link

Eric

US Engine Production

FYI, I don't think this is a native PDO issue.

I've done some additional testing, and PDO's debugDumpParams() doesn't report any parameters (parameter count = 0) when using the DBISAM driver.

When I used basically the same code, but connected to a MSSQL server, PDO reported the parameter count correctly.


Tim Young [Elevate Software] wrote:

Eric,

I need to install PHP 7.x and take a look.  I suspect that the PDO layer is not binding the parameters properly, or is trying to get the parameter data types and screwing it up.

Tim Young
Elevate Software
www.elevatesoft.com
Tue, Dec 11 2018 3:09 PMPermanent Link

Eric

US Engine Production

Additional info:

Here's what the connection string looks like:

   $connection_string = "odbc:DRIVER={DBISAM 4 ODBC Driver};ConnectionType=Remote;CatalogName=USEngine;RemoteIPAddress=" . $dbisam_server_ip . ";";
   $connection_string .= "RemotePort=12015;UID=" . $dbisam_username . ";PWD=" . $dbisam_password . ";";

I've verified that the values contained in $dbisam_server_ip, $dbisam_username, and $dbisam_password are correct, and the server is running on the non-standard port 12015.

Is it possible that I've missed something on the connection string?

---------------------------------------------------------------------------
Tim Young [Elevate Software] wrote:

Eric,

I need to install PHP 7.x and take a look.  I suspect that the PDO layer is not binding the parameters properly, or is trying to get the parameter data types and screwing it up.

Tim Young
Elevate Software
www.elevatesoft.com
Tue, Dec 11 2018 6:06 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Eric,

<< FYI, I don't think this is a native PDO issue.

I've done some additional testing, and PDO's debugDumpParams() doesn't report any parameters (parameter count = 0) when using the DBISAM driver. >>

Yes, this is due to what I thought it was: the DBISAM ODBC driver does not support SQLDescribeParam because DBISAM (the engine) cannot infer and auto-populate parameter types in SQL statements.  This trips up the PDO layer in PHP:

https://bugs.php.net/bug.php?id=44643&edit=1

And, instead of just using the parameters and the defined types passed in by the PHP script, PDO uses random, non-compatible parameter types (see the link) that cause a DBISAM error during execution (which is then ignored by the PDO code).

The underlying issue is that DBISAM doesn't support SQLDescribeParam, but the PDO functionality could handle this better.  Our newer database engine, ElevateDB, *does* support SQLDescribeParam, but I will need to look into if it's even possible to back-port this to DBISAM, so I can't make any promises.

As a workaround, you're going to need to use hard-coded SQL statements.

I tried using the:

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);

option, but it didn't seem to have any effect at all, so I'll need to look into this further.

Tim Young
Elevate Software
www.elevatesoft.com
Wed, Dec 12 2018 1:56 PMPermanent Link

Eric

US Engine Production

Tim,

Thank you for your research.  I forsee two potential issues with using hard-coded SQL statments:

1. How to update a BLOB field using hard-coded SQL
2. When updating text (varchar, etc.) fields, how can apostrophes be maintained in the data, since single quotes (the same character) will be used to encapsulate the value in the hard-coded SQL statement?

Would you have any insight into these two things?

Tim Young [Elevate Software] wrote:

Eric,

<< FYI, I don't think this is a native PDO issue.

I've done some additional testing, and PDO's debugDumpParams() doesn't report any parameters (parameter count = 0) when using the DBISAM driver. >>

Yes, this is due to what I thought it was: the DBISAM ODBC driver does not support SQLDescribeParam because DBISAM (the engine) cannot infer and auto-populate parameter types in SQL statements.  This trips up the PDO layer in PHP:

https://bugs.php.net/bug.php?id=44643&edit=1

And, instead of just using the parameters and the defined types passed in by the PHP script, PDO uses random, non-compatible parameter types (see the link) that cause a DBISAM error during execution (which is then ignored by the PDO code).

The underlying issue is that DBISAM doesn't support SQLDescribeParam, but the PDO functionality could handle this better.  Our newer database engine, ElevateDB, *does* support SQLDescribeParam, but I will need to look into if it's even possible to back-port this to DBISAM, so I can't make any promises.

As a workaround, you're going to need to use hard-coded SQL statements.

I tried using the:

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);

option, but it didn't seem to have any effect at all, so I'll need to look into this further.

Tim Young
Elevate Software
www.elevatesoft.com
Wed, Dec 12 2018 1:59 PMPermanent Link

Eric

US Engine Production

Forgot to put this in my previous reply:

Unfortunately moving to ElevateDB isn't an option for us.  Although we're building out a web-based system, the databases are also used by a legacy Windows app, which will be in use until we completely replicate all of its functionality.  That's not going to happen quickly, since it's a pretty complex app.

----------------------------------------------------------
Tim Young [Elevate Software] wrote:

Eric,

<< FYI, I don't think this is a native PDO issue.

I've done some additional testing, and PDO's debugDumpParams() doesn't report any parameters (parameter count = 0) when using the DBISAM driver. >>

Yes, this is due to what I thought it was: the DBISAM ODBC driver does not support SQLDescribeParam because DBISAM (the engine) cannot infer and auto-populate parameter types in SQL statements.  This trips up the PDO layer in PHP:

https://bugs.php.net/bug.php?id=44643&edit=1

And, instead of just using the parameters and the defined types passed in by the PHP script, PDO uses random, non-compatible parameter types (see the link) that cause a DBISAM error during execution (which is then ignored by the PDO code).

The underlying issue is that DBISAM doesn't support SQLDescribeParam, but the PDO functionality could handle this better.  Our newer database engine, ElevateDB, *does* support SQLDescribeParam, but I will need to look into if it's even possible to back-port this to DBISAM, so I can't make any promises.

As a workaround, you're going to need to use hard-coded SQL statements.

I tried using the:

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);

option, but it didn't seem to have any effect at all, so I'll need to look into this further.

Tim Young
Elevate Software
www.elevatesoft.com
Wed, Dec 12 2018 2:03 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Eric,

<< Unfortunately moving to ElevateDB isn't an option for us.  Although we're building out a web-based system, the databases are also used by a legacy Windows app, which will be in use until we completely replicate all of its functionality.  That's not going to happen quickly, since it's a pretty complex app. >>

No problem.  I wasn't trying to imply that you should use ElevateDB, just letting you know that this is a solved issue there and that I'm aware of this deficiency in DBISAM.

Tim Young
Elevate Software
www.elevatesoft.com
Wed, Dec 12 2018 2:12 PMPermanent Link

Eric

US Engine Production

I figured out one of the issues - I can maintain single quote characters in the data by doubling them up in the query like this: UPDATE Item SET ItemDescription = 'STW''S TEST' WHERE ItemID=49903.

So the only outstanding question I have is how it would be possible to update BLOB data (which in our case is always text stored in a BLOB field) without using parameters.

----------------------------------------------------
Tim Young [Elevate Software] wrote:

Eric,

<< Unfortunately moving to ElevateDB isn't an option for us.  Although we're building out a web-based system, the databases are also used by a legacy Windows app, which will be in use until we completely replicate all of its functionality.  That's not going to happen quickly, since it's a pretty complex app. >>

No problem.  I wasn't trying to imply that you should use ElevateDB, just letting you know that this is a solved issue there and that I'm aware of this deficiency in DBISAM.

Tim Young
Elevate Software
www.elevatesoft.com
Wed, Dec 12 2018 4:20 PMPermanent Link

Eric

US Engine Production

Figured out the BLOB update as well:

UPDATE Warranty
   SET Notes = CAST('Diesel long block doesn''t built oil pressure,' + #10 + 'customer removed and diesel longblock is ' + #10 + 'ready to be shiped back... ' + #10 + '12-12-18' AS MEMO)
WHERE WarrantyID = 10193

This works properly for me when executed by PHP via ODBC.  I've checked it both in the database itself, and the legacy Windows app sees the changes properly.

----------------------------------------------------
Eric wrote:

I figured out one of the issues - I can maintain single quote characters in the data by doubling them up in the query like this: UPDATE Item SET ItemDescription = 'STW''S TEST' WHERE ItemID=49903.

So the only outstanding question I have is how it would be possible to update BLOB data (which in our case is always text stored in a BLOB field) without using parameters.

----------------------------------------------------
Tim Young [Elevate Software] wrote:

Eric,

<< Unfortunately moving to ElevateDB isn't an option for us.  Although we're building out a web-based system, the databases are also used by a legacy Windows app, which will be in use until we completely replicate all of its functionality.  That's not going to happen quickly, since it's a pretty complex app. >>

No problem.  I wasn't trying to imply that you should use ElevateDB, just letting you know that this is a solved issue there and that I'm aware of this deficiency in DBISAM.

Tim Young
Elevate Software
www.elevatesoft.com
Page 1 of 2Next Page »
Jump to Page:  1 2
Image