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

Resolved SQL Server / ODB ODBC and PBMaxBlobSize in PB 21

  1. Issue
0
Votes
Undo
  1. Paul Shue
    Paul Shue
  2. PowerBuilder
  3. Friday, 3 June 2022 14:23 PM UTC

Selectblob is returning only 32767 characters using PB21 and SQL server using ODB ODBC database profiles. I have modified the
C:\Program Files (x86)\Appeon\PowerBuilder 21.0\IniCache\pbodb.ini sections for both [Microsoft Sql Server] and [Sql Server]
to be (see attached ini file):
PBMaxBlobSize='2147483647' (these values were set to '32767' previously when data was saved)
PBMaxTextSize='2147483647'

I made these ini changes and exited PB and logged out of server and logged back in and still no change. Any ideas? 

 

 

Attachments (1)
pbodb.zip
Dynamic RIBBON BAR
IDE showing open script.. under declare variable
Responses (5)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 3 June 2022 15:06 PM UTC
  2. PowerBuilder
  3. # 1

Hi Paul;

  Also, please try these settings in your App's transaction object's DBParm field as well - for example:

SQLCA.DBParm = "ConnectString='DSN=SS2019;UID=sa;PWD=<******>;PBMaxBlobSize=2147483647, PBMaxTextSize=2147483647', ..."

HTH

Regards ... Chris

Comment
Roland Smith
  1. Roland Smith
  2. Friday, 3 June 2022 15:23 PM UTC
According to the help, PBMaxBlobSize dbparm applies to ADO.NET, OLE DB, SNC, and MSOLEDBSQL. It also says the default is 1024000 so that isn't the issue.
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Friday, 3 June 2022 15:49 PM UTC
Hi Roland ... It also works for ODBC - just not well documented. These are actual settings that I use for SS via ODBC when I have Blob processing. ;-)
  1. Helpful
There are no comments made yet.
Roland Smith
Roland Smith Accepted Answer Pending Moderation
  1. Friday, 3 June 2022 15:25 PM UTC
  2. PowerBuilder
  3. # 2

How are you determining that only 32767 bytes are returned?

If you are writing it to disk, make sure you are using FileWriteEx.

Comment
There are no comments made yet.
Paul Shue
Paul Shue Accepted Answer Pending Moderation
  1. Friday, 3 June 2022 16:36 PM UTC
  2. PowerBuilder
  3. # 3
I changed the dbparm as follows - is this the correct format? I copied what you sent as a starting point. Should the last comma
be a semicolon or does it matter?
 
 
ConnectString='DSN=LIMS_test;UID=XXXXX;PWD=psw;PBMaxBlobSize=2147483647, PBMaxTextSize=2147483647'
 
I am using Filewritex and I determine the number of bytes returned by getting length of the blob (ll_blob_len) as follows:
 
NOTE: I checked the length of the column in the database painter (select datalength(ole_object) ... and it is 165530
whereas ll_blob_len is 32767.


selectblob ole_object
into :lblb_blob
from test_attachment
where siteseq = :siteseq and
sampleseq = :il_sampleseq and
testassignseq = :il_testassignseq and
attachseq = :ll_attachment and
version_no = :il_version;
 
if sqlca.sqlcode = 0 then
iblb_pdf[ll_attachment] = lblb_blob
ll_blob_len = len(lblb_blob)
end if 
Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Friday, 3 June 2022 18:11 PM UTC
Hi Paul;

Have you tried using a native SS diver instead (like SNC or MSO)?

Regards ... Chris

  1. Helpful
There are no comments made yet.
Paul Shue
Paul Shue Accepted Answer Pending Moderation
  1. Friday, 3 June 2022 18:23 PM UTC
  2. PowerBuilder
  3. # 4

Chris - thanks for your responses. Was my format for dbparm correct? I am a contractor and we have done a lot of testing on our app so the company is not willing to change from ODB ODBC. Any other ideas>

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Friday, 3 June 2022 18:37 PM UTC
Yes, that looked correct in the DBParm. Between the DBParm and the PBODB.ini setting (both can be used) - one of these should control the maximum data stream size as be default, the ODBC layer restricts you to a 32K normally.
  1. Helpful 1
mike S
  1. mike S
  2. Friday, 3 June 2022 19:18 PM UTC
yeah, we punted on getting it to work with odbc.

Easier to just chunk it.



You chunk it down yourself in whatever size makes sense for you.

for example, this is selecting it an 8000 chunk



SELECTBLOB substring( <blobcolumnname>, :ll_start, 8000)



then either immediately write it out to your streamfile, or append it into a huge blob in PB.



  1. Helpful 2
There are no comments made yet.
Paul Shue
Paul Shue Accepted Answer Pending Moderation
  1. Monday, 6 June 2022 12:16 PM UTC
  2. PowerBuilder
  3. # 5
I found the problem. I am running on Windows server 2019. I changed the file: "C:\Program Files (x86)\Appeon\PowerBuilder 21.0\IniCache\pbodb.ini" I instead needed to change this file : %USERPROFILE%\AppData\Local\Appeon\PowerBuilder 21.0\pbodb.ini
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.