-
Daniel Vivier
- PowerBuilder
- Thursday, 23 November 2017 03:02 PM UTC
I'm testing some PB17 code that is uploading data from a local Firebird DB to a MySQL DB on our web server. It is being done through DataStores - load one from the local DB, copy the data to one connected to the web DB (with "dsTarget.Data = dsSource.Data"), and then Update().
If I do it with DisableBind=1 in the DbParm of the Transaction object for the web DB, so prepared statements aren't used, it works.
If I don't set DisableBind, so prepared statements ARE used, I start somewhat randomly getting errors such as:
Bind parameter value for ':3' is too big (2282)
No changes made to database.
Insert into tablename(columns, ) VALUES(?, ...)
The italic parts are filled in with the real table and column names of course.
What's weird is that (a) it works when not using prepared statements, and (b) the length stated in the first line of that message isn't too long for the 3rd column in the list of columns, which in this case is set in the DataWindow definition to a length of 8190. (And the actual MySQL column is MEDIUMTEXT, which holds up to about 16 million bytes!) Not only that, but there is no row of the table being updated that has a value in that column with the problem length (2282). The closest are 2257 and 2303. There's also one just under half the problem length at 1140.
On previous runs of this, it stopped at an earlier place in a different table being uploaded, where again the stated problem data length in the SqlErrText was less than the maximum column length for that field in the datawindow definition, and the complaint was of a length that didn't exist in the actual data - but a row with that column just under half the length did exist.
Both DBs are using UTF8.
Any bright ideas? Obviously we'd like to not have to use DisableBind, because this process is fairly slow already and using prepared statements should speed it up.
I also don't know whether this is in any way relevant, but when we added some code to show progress in the Update() statement, by having our custom DataStore user object call back to the window showing the progress of conversion, in its SqlPreview event, and having the window update a row number in a display DW (followed by Yield(), so you see it), the activity kept crashing at seemingly random places, often with a disconnect of the web DB! But the tests above weren't doing that.
Thanks.
Find Questions by Tag
Helpful?
If a reply or comment is helpful for you, please don’t hesitate to click the Helpful button. This action is further confirmation of their invaluable contribution to the Appeon Community.