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

Resolved SELECTBLOB and UPDATEBLOB not working

  1. Issue
0
Votes
Undo
  1. Richard Donagrandi
    Richard Donagrandi
  2. PowerBuilder
  3. Tuesday, 21 May 2024 20:10 PM UTC

I am trying to save a datawindow definition to SQL Server Varchar(Max) column, using UPDATEBLOB and the command runs without error, but the data saved is severely truncated. Using ODBC, SQL Server 2022, and PB 2022 R3 Build 3356.

lblb_DWSyntax = Blob(ls_DWSyntax)

UPDATEBLOB group1.userdw_tb SET dwsyntax = :lblb_DWSyntax
WHERE (group1.userdw_tb.username = :ls_UserID )
AND (group1.userdw_tb.dwkey = :ls_DWKey );

The result of the save to the table in the dwsyntax field is simply "r"

I have tried running it as a Dynamic Cursor as well, but it errors with a strange error: "Syntax error near '.'" 

The documentation mentions setting the PBMaxBlobSize but doesn't mention this is necessary for ODBC.

I would like to mention we're migrating from SQL Anywhere (using ODBC) to SQL Server (using ODBC) ... This used to work just fine with SQL Anywhere, so there should be no excuse why it wouldn't work with SQL Server particularly when the documentation says PB supports Varchar(Max) on SQL Server.

Likewise, when there is data in the Dwsyntax field, the SELECTBLOB returned gibberish:

SELECTBLOB group1.userdw_tb.dwsyntax
INTO :lblb_DWSyntax
FROM group1.userdw_tb
WHERE ( group1.userdw_tb.username = :ls_UserID ) AND
( group1.userdw_tb.dwkey = :ls_DWKey );

ls_DWSyntax = String(lblb_DWSyntax)

 ls_DWSyntax = ?????????????7????‰??????????????????????????????????????????????‰???????????????????????????????‰??????????????????????????????????????????‰?????????????????‰?????????‰???????•???????‰?????????‰?????????‰?????????‰???????????????????‰??????????‰??????????????????‰??????????†??????????•????????????????????????????????‰?????????"????????????????????‰?????????????????????????????????????????????????????????????????????????????•????????????????????•????????????•??????????????????????????????•????????????????????????????????????????•????????????????????????????????????????????????•??????????????????????????????????•??????•??????????????????????????????•??????????????•???????????????????•??????????•??????????????????????????•??????????????????????????????????•??????•??????????????????????????????•??????????????•???????????????????•??????????•??????????????????•????????????????????•????????????•??????????????????????????????•????????????????????????????????????????•??????????????????????????????????????????????????????????????????????•????????????????????????????????????????????†????????????????????????????????????????????=†??????????????????????????????????????????????†?????????????????????????????????????????????•????????????????????????????????????????????????†????????????????????????????????????????????????†??????????????????????????????????????????????†??????????????????????????????????????????=†??????????????????????????????????????????=†??????????????????????????????????????????=†????????????????????????????????????????????•???????????????????????????????????????????????=†?????????????????????????????????????????????????•???????????????????????????????????????•?????????????????????????????????????????????•??????????????????????????????????????????????•???????????????????????????????????????????•????????????????????????????????????????•?????????????????????????????????????=†???????????????????????????????????????=†?????????????????????????????????????????†????????????????????????????????????????†???????????????????????????????????????•?????????????????????????????'????????†?????????????????????????????????????•???????????????????????????????????????????????†???????????????????????????????????????????? ????•????????????????????????????????????????????????=? ???????†????????????????????????????????????????†???????????????????????????????????????????=†????????????????????????????????????????=†?????????????????????????????????????????=†? ????????????????????????????????????†††??????????????????????????†††???????????? ††??????????????†††????????????????????????????†††????????????????????????????????????????????????? ††???????????????†††???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????‰???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????†????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????‰???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ???????????????????????????????????????????????????????????????????????????????????????????????? ??????????????†?/??????????? ?????????????????????????????????????????•????????•???????•???????????????•????????•?????????•????????????????????????????????????????????????•??????????????????????•??????????????•????????????•?????????????????????•?????????•?????????????•?????????????•?????????????????•?????????????????????????????????????????????•???????????????????•?????????????????????•???????????????????????????????????????????????????????????????????•????????????????????•??•????????:????????????????????????•???????????4?????•???????????????????????????????????????•????????????????????????????????????•??????????????•??????????????•??????????????????•?????????????????????????????????????????????????????•??????????????????????????????????????????????????????????????????????????????????•??????????????•??????????????????????????????????????????????????????????????????????????•??????????????•???????????????•????????????????????????•??????•????:???????•?????•?????•??????????????????????•????????????????????????????????•????????•???????•???????????????•????????•?????????•????????????????????????????????????????????????•??????????????????????•??????????????•????????????•?????????????????????•?????????•?????????????•?????????????•?????????????????•?????????????????????????????????????????????•???????????????????•?????????????????????•???????????????????????????????????????????????????????????????????•????????????????????•???????•?????•?????•???????????????????•?????????????????????????????????•????????•???????•???????????????•????????•?????????•????????????????????????????????????????????????•??????????????????????•??????????????•????????????•?????????????????????•?????????•?????????????•?????????????•?????????????????•?????????????????????????????????????????????•???????????????????•?????????????????????•???????????????????????????????????????????????????????????????????•????????????????????•???????•?????•?????•??????|??????????•????????????4?????•???????????????????????????????????????•????????????????????????????????????•??????????????•??????????????•??????????????????•?????????????????????????????????????????????????????•??????????????????????????????????????????????????????????????????????????????????•??????????????•??????????????????????????????????????????????????????????????????????????•??????????????•???????????????•????????????????????????•??????•????:????•????????:?????????????•????????????????????????????????????????????•????????•???????•???????????????•????????•?????????•????????????????????????????????????????????????•??????????????????????•??????????????•????????????•?????????????????????•?????????•?????????????•?????????????•?????????????????•?????????????????????????????????????????????•???????????????????•?????????????????????•???????????????????????????????????????????????????????????????????•????????????????????•???????•?????•?????•???????????????•????????????4?????•???????????????????????????????????????•????????????????????????????????????•??????????????•??????????????•??????????????????•?????????????????????????????????????????????????????•??????????????????????????????????????????????????????????????????????????????????•??????????????•??????????????????????????????????????????????????????????????????????????•??????????????•???????????????•?????????????????????????????????•???????•?????•?????•?????•?????????????????????4?????•???????????????????????????????????????•????????????????????????????????????•??????????????•??????????????•??????????????????•?????????????????????????????????????????????????????•??????????????????????????????????????????????????????????????????????????????????•??????????????•??????????????????????????????????????????????????????????????????????????•??????????????•???????????????•????????????????????????•??????•????:????•????????:????????????•????????????????????????????????????????????•????????•???????•???????????????•????????•?????????•????????????????????????????????????????•??????????????????????•????????????•????????????•?????????????????????•?????????•?????????????•?????????????•??????????????•?????????????????????????????????????????????•???????????????????•????????????????????•?????????????????????????????????????????????????????????????????•????•??????•????:????•????????:??????????????•???????????????????????????4?????•???????????????????????????????????????•????????????????????????????????????•??????????????•??????????????•??????????????????•?????????????????????????????????????????????????????•??????????????????????????????????????????????????????????????????????????????????•??????????????•??????????????????????????????????????????????????????????????????????????•??????????????•???????????????•????????????????????????•??????•????:????•????????:???????????=??????????•???????????4?????•???????????????????????????????????????•????????????????????????????????????•??????????????•??????????????•??????????????????•?????????????????????????????????????????????????????•??????????????????????????????????????????????????????????????????????????????????•??????????????•??????????????????????????????????????????????????????????????????????????•??????????????•???????????????•????????????????????????•??????•????:????•????????:????????????•??????????????????????????????????????????????•????????•???????•???????????????•????????•?????????•????????????????????????????????????????????????•??????????????????????•??????????????•????????????•?????????????????????•?????????•?????????????•?????????????•?????????????????•?????????????????????????????????????????????•???????????????????•?????????????????????•???????????????????????????????????????????????????????????????????•????•??????•????:????•????????:???????????•??????????????????????????4?????•???????????????????????????????????????•????????????????????????????????????•??????????????•??????????????•??????????????????•?????????????????????????????????????????????????????•??????????????????????????????????????????????????????????????????????????????????•??????????????•??????????????????????????????????????????????????????????????????????????•??????????????•???????????????•?????????????????????????????????•???????•?????•?????•?????•???????????????????????????????????????????•????????•???????•???????????????•????????•?????????•????????????????????????????????????????????????•??????????????????????•??????????????•????????????•?????????????????????•?????????•?????????????•?????????????•?????????????????•?????????????????????????????????????????????•???????????????????•?????????????????????•???????????????????????????????????????????????????????????????????•????????????????????•???????•?????•?????•??????=??????????•??????????????4?????•???????????????????????????????????????•????????????????????????????????????•??????????????•??????????????•??????????????????•?????????????????????????????????????????????????????•??????????????????????????????????????????????????????????????????????????????????•??????????????•??????????????????????????????????????????????????????????????????????????•??????????????•???????????????•?????????????????????????????????•???????•?????•?????•???????•????????????????????????????????????????????•????????•???????•???????????????•????????•?????????•????????????????????????????????????????????????•??????????????????????•??????????????•????????????•?????????????????????•?????????•?????????????•?????????????•?????????????????•?????????????????????????????????????????????•???????????????????•?????????????????????•???????????????????????????????????????????????????????????????????•????????????????????•???????•?????•?????•?????=??????????•?????????????????????????????????•????????•???????•???????????????•????????•?????????•????????????????????????????????????????????????•??????????????????????•??????????????•????????????•?????????????????????•?????????•?????????????•?????????????•?????????????????•?????????????????????????????????????????????•???????????????????•?????????????????????•???????????????????????????????????????????????????????????????????•????•??????•????:????•????????:????????????????????????•?????????????????????????????????•????????•???????•???????????????•????????•?????????•????????????????????????????????????????????????•??????????????????????•??????????????•????????????•?????????????????????•?????????•?????????????•?????????????•?????????????????•?????????????????????????????????????????????•???????????????????•?????????????????????•???????????????????????????????????????????????????????????????????•????•??????•????:????•????????:?????????????=??????????•????????????4?????•???????????????????????????????????????•????????????????????????????????????•??????????????•??????????????•??????????????????•?????????????????????????????????????????????????????•??????????????????????????????????????????????????????????????????????????????????•??????????????•??????????????????????????????????????????????????????????????????????????•??????????????•???????????????•?????????????????????????????????•???????•?????•?????•?????????????????•????????????????????????????????•????????•???????•???????????????•????????•?????????•????????????????????????????????????????????????•??????????????????????•??????????????•????????????•?????????????????????•?????????•?????????????•?????????????•?????????????????•?????????????????????????????????????????????•???????????????????•?????????????????????•???????????????????????????????????????????????????????????????????•????•??????•????:????•????????:??????•??????•??????????????????????4?????•???????????????????????????????????????•????????????????????????????????????•??????????????•??????????????•??????????????????•?????????????????????????????????????????????????????•??????????????????????????????????????????????????????????????????????????????????•??????????????•??????????????????????????????????????????????????????????????????????????•??????????????•???????????????•?????????????????????????????????•????•????????:???????????•?????????????????????4?????•???????????????????????????????????????•????????????????????????????????????•??????????????•??????????????•??????????????????•?????????????????????????????????????????????????????•??????????????????????????????????????????????????????????????????????????????????•??????????????•??????????????????????????????????????????????????????????????????????????•??????????????•???????????????•?????????????????????????????????•????•????????:??????????????????????•???????????????????????????????•????????•???????•???????????????•????????•?????????•????????????????????????????????????????????????•??????????????????????•??????????????•????????????•?????????????????????•?????????•?????????????•?????????????•?????????????????•?????????????????????????????????????????????•???????????????????•?????????????????????•???????????????????????????????????????????????????????????????????•????????????????????•???????•?????•?????•????????????????•????????????????4?????•???????????????????????????????????????•????????????????????????????????????•??????????????•??????????????•??????????????????•?????????????????????????????????????????????????????•??????????????????????????????????????????????????????????????????????????????????•??????????????•??????????????????????????????????????????????????????????????????????????•??????????????•???????????????•????????????????????????•??????•????:????•????????:????????????4????•?????????????????????4?????•???????????????????????????????????????•????????????????????????????????????•??????????????•??????????????•??????????????????•?????????????????????????????????????????????????????•??????????????????????????????????????????????????????????????????????????????????•??????????????•??????????????????????????????????????????????????????????????????????????•??????????????•???????????????•?????????????????????????????????•????•????????:??????????????????????•?????????????4?????•???????????????????????????????????????•????????????????????????????????????•??????????•??????????•??????????????????•???????????????????????????????????????????????????•???????????????????????????????????????????????????????????????????????????????•??????????????•?????????????????????????????????????????????????????????????????????????•??????????????•??????????????•????????????????????????????????•???:???•?????•?????•?????•?????????????????????????????4?????•???????????????????????????????????????•????????????????????????????????????•??????????•??????????•??????????????????•???????????????????????????????????????????????????•???????????????????????????????????????????????????????????????????????????????•?????????????

 

  1. BLOB
  2. SQL Server
ShellExecute for 64 bit PB application
Window with header datawindow and detail datawindo...
Responses (6)
David Peace
David Peace Accepted Answer Pending Moderation
  1. Wednesday, 22 May 2024 11:03 AM UTC
  2. PowerBuilder
  3. # 1

Hi Richard

I would reccomend using the MSOLEDB Client for SQL Server rather than ODBC. I'm not sure but I think the default BLOB size for ODBC is 32k so you might be hitting that limit.

Also when working with BLOBS converting to strings it probably best to specifiy the Encoding.

I hope that helps

David

Comment
Richard Donagrandi
  1. Richard Donagrandi
  2. Wednesday, 22 May 2024 17:47 PM UTC
We used ODBC with SQL Anywhere and did not have this issue. I don't think this is an issue with ODBC. It's actually updating/saving 65537 bytes, so it's beyond the 32k "limit". Yes, setting the encoding on the String(blob) on the retrieve worked so it's not gibberish.
  1. Helpful
David Peace
  1. David Peace
  2. Thursday, 23 May 2024 08:09 AM UTC
65k is another possible limit!
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 22 May 2024 18:03 PM UTC
  2. PowerBuilder
  3. # 2

Hi Richard;

  I think that I see the problem ...  Change the column type for the DWO source from "Varchar(Max) " to "VarBinary(max)" - then interact with it via the Select/Update Blob commands to save / retrieve the DWO source.  HTH

Regards .. Chris

Comment
Load more comments
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Thursday, 23 May 2024 19:05 PM UTC
It's not the DB Client driver .. it's the wrong DB Column type. You need to be using Long Binary instead. HTH
  1. Helpful
Richard Donagrandi
  1. Richard Donagrandi
  2. Thursday, 23 May 2024 20:03 PM UTC
Chris: There is no Long Binary column type in Microsoft SQL Server. It's VarBinary(MAX). Using VarBinary(MAX) (and MSOLEDBSQL) produces the same results as VarChar(MAX) (and ODBC). The datawindow we're saving is quite large. I've attached a sample to this Q/A thread... We use large blobs to also save RTF definitions out of the TextControl object, so we're going to run into save and retrieve issues with that part of our program as well. And we use BLOBs to save (signature capture) image files although those rarely go over 5k, so I don't anticipate an issue with those. -- I fear that the only solution to this is to break up the data into 32k chunks and save them to the db in separate, non-MAX fields.

  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Thursday, 23 May 2024 20:34 PM UTC
Sorry Richard, you are correct. I was thinking of another DBMS vendor. VarBinary(max) would it for SS.
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Thursday, 23 May 2024 17:14 PM UTC
  2. PowerBuilder
  3. # 3

I don't know anything about your database and whether it allows Unicode 16LE, but you could try to add EncodingUTF8! or EncodingAnsi! to your string() functions that convert to and from blob variables.

Comment
Richard Donagrandi
  1. Richard Donagrandi
  2. Thursday, 23 May 2024 21:26 PM UTC
Yes. The collation on our SQL Server db is a UTF8 variant, and including the encoding in the String() function returned expected data and not jibberish-- just not ALL the expected data.
  1. Helpful
There are no comments made yet.
Richard Donagrandi
Richard Donagrandi Accepted Answer Pending Moderation
  1. Thursday, 23 May 2024 20:15 PM UTC
  2. PowerBuilder
  3. # 4

Here's the datawindow definition we're trying to save. (attached)

Things that I've tried:

using ODBC and either VarChar(MAX) or VarBinary(MAX) field definitions:
-- SELECTBLOB/UPDATEBLOB : RESULT: only saves 'r', only retrieves 65537 bytes.
-- A stored proc to save with a direct call to the stored proc; and a stored proc to retrieve by using SELECTBLOB group1.afn_getmyblob()  RESULT: only saves and retrieves 65537 bytes
-- DECLARE  the SELECTBLOB/UPDATEBLOB statement. RESULT: no results returned.

using MSOLEDBSQL19 and VarChar(MAX)
-- Any call to SELECTBLOB on a varchar(max) field bombs Powerbuilder. ... It literally bombs right on the Select execution. No result is returned (because it bombs).

using MSOLEDBSQL19 and VarBinary(MAX)
-- Same result as using ODBC regardless if using SELECTBLOB/UPDATEBLOB or the stored proc workaround.

Attachments (1)
dw_syntax.zip
Comment
There are no comments made yet.
Arnd Schmidt
Arnd Schmidt Accepted Answer Pending Moderation
  1. Thursday, 23 May 2024 20:51 PM UTC
  2. PowerBuilder
  3. # 5

FYI:

https://community.appeon.com/index.php/qna/q-a/sql-server-odb-odbc-and-pbmaxblobsize-in-pb-21

HTH

Arnd

Comment
Load more comments
Arnd Schmidt
  1. Arnd Schmidt
  2. Thursday, 23 May 2024 22:25 PM UTC
Sorry, but you asked for ODBC and I believed that even PB 2022 R3 should work using the pbobd.ini trick.

I bet Chris is a magician!





  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Friday, 24 May 2024 14:24 PM UTC
PB knows better than to misbehave when I am around - LOL! ;-)
  1. Helpful
Arnd Schmidt
  1. Arnd Schmidt
  2. Friday, 24 May 2024 14:36 PM UTC
In this case it is not PEBCAK but CEBCAK. (C=Chris). ;-)
  1. Helpful 1
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Thursday, 23 May 2024 21:32 PM UTC
  2. PowerBuilder
  3. # 6

Hi Richard;

  FYI: I just did a quick test with my SS 2022 DBMS connecting through from PB via the new MSOLEDBSQL v19 driver.

This used the PB Demo DB's "Products" table which I used the PB PipeLine object to get the data over to SS.  The PL converted the Long Binary into a VarBinary(max) data type.

Then I created a Table Blob column in the test DWO. When running the DWO, the Table Blob was able to access the image automatically. FYI ...

Works with ODBC to SS connection as well. Note though that ODBC driver informs PB that the Column Type  is a generic "Image" data type vs what it really is in SS.

Regards ... Chris/

 

Comment
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.