1. Alan Rice
  2. PowerBuilder
  3. Tuesday, 18 December 2018 15:16 PM UTC

Hi,

I'm having an issue uploading a image Blobs to a MS SQL Server and can't figure out where I'm going wrong (Using Powerbuilder 2017 R3, both in Desktop App and Appeon Mobile).

The Upload Blob is working and creating the database records but it looks like its getting cut off only a small way through the insert.
This cut off is more dramatic the larger the image.

e.g.

 

I've updated the pbodb170.ini PBMaxBlobSize under each of the database sections and also included it in the database script

 

INI:
PBMaxBlobSize='1073741824'
PBMaxTextSize='1073741824'
Case IS_USE_SCRIPT							

	as_dbms			= "SNC SQL Native Client(OLE DB)"
	as_database		= ""
	as_userid		= ""
	as_dbpass		= ""
	as_logid		= "myDBbUserID"
	as_logpass		= "myDBUserPassword"
	as_server		= "myDbServer"
	as_dbparm		= "Database='MyDB';PBMaxBlobSize=1073741824"
	as_lock			= ""
	as_autocommit	= "true"

 

Database Table is in MS SQL 2014 Express


The field in the database is varbinary(max)

This is the code being used to save the blob to the database

 

Long 		l_file_handle, ll_blob_id
blob   		l_blob_file
str_response 	l_str_response
String 		ls_error

SQLCA.AutoCommit = TRUE

// Get File Handle
l_file_handle = FileOpen(as_filepath, StreamMode!)

IF l_file_handle <> -1 THEN

	// Read Blob into memory
	FileRead(l_file_handle, l_blob_file)
	FileClose(l_file_handle)

	// Add Storage Blob Main Record - must add blob seperately using updateblob
	INSERT INTO storage_blobs
	(
		Filename,
		CreatedBy
	)
	VALUES
	( 
		'cat2.jpg',
		'AR'
	)
	USING sqlca;

	IF SQLCA.SQLNRows > 0 THEN
		COMMIT USING SQLCA;
	Else
		l_str_response.success = false
		l_str_response.message = 'Cannot insert blob record to database.' 			+&
											'SQL error ' + String(SQLCA.SQLDBCode) + ' ' 	+&
											 SQLCA.SQLErrText
		return l_str_response
	END IF

	// Get Inserted Record ID
	SELECT SCOPE_IDENTITY()
	INTO :ll_blob_id 
	FROM stud
	USING SQLCA;

	l_str_response.result = ll_blob_id
	
	// Upload Blob Into Record
	UPDATEBLOB storage_blobs
	SET FileBlob = :l_blob_file
	WHERE storage_blobs.ID = :ll_blob_id
	USING SQLCA;
	
	IF SQLCA.SQLNRows > 0 THEN
		COMMIT USING SQLCA;
	Else
		l_str_response.success = false
		l_str_response.message = 'Cannot upload blob to database.' 						+&
											'SQL error ' + String(SQLCA.SQLDBCode) + ' ' 	+&
											 SQLCA.SQLErrText
		return l_str_response
	END IF

ELSE
	l_str_response.success = false
	l_str_response.message = 'Cannot get handle on file'
	return l_str_response
END IF

l_str_response.success = true
l_str_response.message = ''
return l_str_response

 

Any ideas?

 

 

Attachments (1)
Accepted Answer
Marco Meoni Accepted Answer Pending Moderation
  1. Tuesday, 18 December 2018 16:10 PM UTC
  2. PowerBuilder
  3. # Permalink

Hi Alan,

you may want to replace FileRead() with FileReadEx() in order to overcome blob size limitations.

Best,

Marco

Comment
  1. Alan Rice
  2. Tuesday, 18 December 2018 16:26 PM UTC
Hi Marco,



Thanks for this. I am going to mark this as the accepted answer.



This fixes the issue for me in PB2017 R3 desktop but I'm still having an issue with the App when deployed to Mobile Powerserver (I know there can be other web config files here) but as my sample code wasn't working in the desktop either I just wanted to make sure that was okay first before start digging into the Mobile side.
  1. Helpful
There are no comments made yet.
Markus Eckert Accepted Answer Pending Moderation
  1. Tuesday, 18 December 2018 16:02 PM UTC
  2. PowerBuilder
  3. # 1

Hi Alan

Just as a sanity check: If you save the blob variable back to disk, it's still the complete picture? There's nothing strange happening before the upload?

Now, I've had a somewhat similar problem when trying to use a blob as a parameter for a stored procedure. Back then, I had to add "BindSPInput=1" to the DBParm, otherwise the last 10-20 bytes of the blob parameter were cut off.

So maybe in your case the DisableBind setting might be the culprit. (Though changing that will have far-reaching consequences as far as how SQL statements are executed)

Hope that was somewhat helpful.

Markus

Comment
  1. Alan Rice
  2. Tuesday, 18 December 2018 16:23 PM UTC
Hi Markus,



Thanks for the reply. It was actually going wrong before it was inserted into the database, I should have checked that! I should have used a loop with FileRead or just use FileReadEx like Marco suggested.



Thanks for your help.
  1. Helpful
There are no comments made yet.
Marco Meoni Accepted Answer Pending Moderation
  1. Tuesday, 18 December 2018 19:06 PM UTC
  2. PowerBuilder
  3. # 2

...and, FWIW and for those who can stay on hold, PowerServer 2018 is already "ready", it just needs to be bundled by Appeon.

HTTPclient and fellow objects have been presented in PowerServer 2018 demo at Elevate 2018:

https://www.appeon.com/developers/library/videos/powerserver-2018-sneak-peek.html

Best,

.m 

Comment
  1. Chris Pollach @Appeon
  2. Tuesday, 18 December 2018 19:25 PM UTC
Hi Marco;

FYI: PS2018 is now being planned for release along side of PB2019 in Q2 2019. I just completed the next beta in mid-December and found some IWA issues. I am not sure what else the other beta testers found. So I think that Engineering will want to address these before a GA release. If I hear anything more about this, I'll post back here.

Regards ... Chris
  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.