SelectBlob is truncated when the embedded SQL statement exceeds
512 characters
Symptom
When a SelectBlob embedded SQL statement exceeds 512 characters,
the SQL statement itself will be truncated, causing the SQL statement to
fail. For example:
|
1 2 |
Blob lb_data SelectBlob image_file into :lb_data From file_system_test Where doc_id = 3 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789; |
SQLCA.SQLErrText displays this error when the SQL is run:
An expression of non-boolean type specified in a context where
a condition is expected, near "a".
Environment
PowerBuilder 2017 or later
SQL Server
Cause
This is a PowerBuilder known issue. When
PowerBuilder is using the “SNC SQL Native Client (OLE DB)” and the
“MSOLEDBSQL SQL Server” driver, if the SelectBlob embedded SQL statement
length exceeds 512 characters, it will be truncated.
Solution
You can work around the issue by using any of the following
ways:
Method #1: Use OLE DB to connect database, for
example:
|
1 |
SQLCA.DBMS = "OLE DB" |
Method #2: Use Dynamic SQL to execute SQL, for
example:
|
1 2 3 4 5 6 |
blob lb_data DECLARE my_cursor DYNAMIC CURSOR FOR SQLSA ; PREPARE SQLSA FROM " SelectBlob image_file into :lb_data From file_system_test Where doc_id = 3 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789 and doc_id <> 123456789" ; OPEN DYNAMIC my_cursor; FETCH my_cursor INTO :lb_data ; CLOSE my_cursor ; |