Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved SQL Syntax Error With OleDatabaseBlob Control

  1. How-to
0
Votes
Undo
  1. James Medick
    James Medick
  2. PowerBuilder
  3. Tuesday, 12 March 2019 20:14 PM UTC

PB 2017 R3 is generating an SQL Syntax Error at run time when trying to retrieve a blob column in a data window. I'm using MySQL  5.7.24.  The message is a sqlstate = 37000, 1064.  The partial syntax is:  "You have an error in your SQL...near ': blob FROM job_desc_blobs WHERE idjob_pos = 25' at line 1

I expect the actual SQL to read:

SELECT job_desc_blob FROM job_desc_blobs WHERE idjob_pos = 25.

I have no idea why its generating this syntax error.  I've tried every possible configuration combination. 

Jim

 

  1. OLEDB
Git commit also tries to commit .pbd files (PB2017...
Cannot Connect to Workspace 2017 R3
Responses (3)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 12 March 2019 21:17 PM UTC
  2. PowerBuilder
  3. # 1

Hi James;

1)  Is this the 1st time that you have tried the DW Blob feature or ... did this issue just start happening?

2)  Have you tried a PB based SQL trace (SQLCA.DBMS = "TRACE ODBC") for more information?

3) Did you set the PBMaxBlobSize parameter in your ODBC settings?

4) Have you tried the TableBLob feature of the DWO instead of OLE?

Regards ... Chris

Comment
There are no comments made yet.
James Medick
James Medick Accepted Answer Pending Moderation
  1. Tuesday, 12 March 2019 22:42 PM UTC
  2. PowerBuilder
  3. # 2

I had this working once, or so I thought.  I started getting strange results on my data window.  First, a "-3 data changed between retrieve and update".  Deleted the old blob column and added a new, which leaves me with this error.

The blob size setting > 16 megs, I have no blobs currently.

Tried using the table blob, I get the exact same result.

I successfully got a trace file output in *.pbp but can't read it (Notepad?)  Just get symbols.  Added the DBParm to DisableBind but that didn't help with the readable output.

This is all happening on a very simple DW with nothing else going on. 

Comment
Roland Smith
  1. Roland Smith
  2. Wednesday, 13 March 2019 14:51 PM UTC
Wrong kind of trace. Add 'TRACE ' to the beginning of sqlca.DBMS to create a SQL trace.
  1. Helpful
James Medick
  1. James Medick
  2. Wednesday, 13 March 2019 15:11 PM UTC
Hi Roland,

Can't seem to generate SQL trace output. I'm using these PS statements in the Open event:



SQLCA.DBParm = "ConnectString='DSN=MySql ITERP 32;UID=root;SQLCA.DBParm=PBMaxBlobSize=16777215',PBCatalogOwner='iterp',ConnectOption='SQL_OPT_TRACE,SQL_OPT_TRACE_ON;SQL_OPT_TRACEFILE,C:\ITERP\MYTrace.LOG'"

SQLCA.DBMS = "TRACE ODBC"

SQLCA.DBParm="PBTrace=1"

SQLCA.DBParm="DisableBind=1"



Then process the window to do the initial retrieve (where I get the retrieve error when using a Ole or Table Blob.



dw_detail.settransobject ( sqlca )

this.EVENT ue_retrieve()

  1. Helpful
There are no comments made yet.
Shenn Sellers
Shenn Sellers Accepted Answer Pending Moderation
  1. Wednesday, 13 March 2019 16:13 PM UTC
  2. PowerBuilder
  3. # 3

It may have something to do with this bug if you are using the TableBlob.

https://www.appeon.com/standardsupport/search/view?id=848#

It still hasn't been fixed, but there is a workaround.

Also, for your SQL statement, shouldn't it be...

SELECTBLOB job_desc_blob FROM job_desc_blobs WHERE idjob_pos = 25

Comment
James Medick
  1. James Medick
  2. Wednesday, 13 March 2019 17:30 PM UTC
Thanks Shenn,



I believe you found the cause. I do see that in the SQL I can see from the error message, that it appears that there is the word ":Blob" inserted where it shouldn't be and that's what SQL is complaining about. I get the same result for Table Blob or Ole.



My SQL Select statement works OK without using "BLOB". Not sure if PB does something to it, but the SELECT statement works in Mysql as "Select job_desc_blob From job_desc_blobs Where idjob_pos = 25".



I've tried not populating the Blob/Text column in the definition window in PB when defining the blob (suggested workaround). Although I don't get the retrieve error, I am unable to double click the blob to activate it. So it looks like a no go.



This is rather odd because up until recently, this worked. I can't think of anything that has changed on my side.



  1. Helpful
Shenn Sellers
  1. Shenn Sellers
  2. Wednesday, 13 March 2019 18:17 PM UTC
Did you try manually editing the source...



Before modification：

column=(type=blob name=blob_1 dbname="some_blob_column: blob" )

After modification：

column=(type=blob name=blob_1 dbname="some_blob_column" )

The redundant ‘: blob’ is removed.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.