Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM ODBC Driver » View Thread |
Messages 1 to 10 of 14 total |
Problems with bound parameters |
Mon, Dec 10 2018 10:53 AM | Permanent 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 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 PM | Permanent 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 PM | Permanent 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 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 PM | Permanent 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 PM | Permanent 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 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 PM | Permanent 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 PM | Permanent 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 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |