Recent

Author Topic: large BLOBS load problems with SQLdb  (Read 2431 times)

Mando

  • Full Member
  • ***
  • Posts: 181
large BLOBS load problems with SQLdb
« on: April 25, 2019, 10:44:53 am »
Hi all:
I'm developing an document management system. I want to save documents into a table in MS SQLServer DataBase. I'm using SQLDB.My problem is that I can save files properly, but not read it when the file is lager than 16,777,216 bytes.The data is correctly saved, If I use Zeos, I can read it and save it as a file withoit size restriction.
I tryed several methods:
Directly:
Code: Pascal  [Select][+][-]
  1. TBLOBField(fieldByName('Contenido')).LoadFromFile(fileNames[0]);
With this method, if I try to do inmediatly after save, it works:
Code: Pascal  [Select][+][-]
  1. TBLOBField(fieldByName('Contenido')).LoadFromFile(fileNames[0]);
  2.  Post;
  3.  SQLQuery1.ApplyUpdates;
  4.  datos.trBD.CommitRetaining;
  5.  
  6. // Read. It works
  7. TBLOBField(fieldByName('Contenido')).saveToFile(fileNames[0]+'-Regrabado'+extractFileExt(fileNames[0]) );  
       

With an auxiliar stream:
Code: Pascal  [Select][+][-]
  1. st:= TFileStream.create(sd.FileName, fmCreate);        
  2. TBLOBField(fieldByName('Contenido')).SaveToStream(st);
  3. st.position:=0; st.Free;                              
  4.  
With CreateBlobStream:
Code: Pascal  [Select][+][-]
  1.     bs:=CreateBlobStream(fieldByName('Contenido'),bmread);
  2.     bs.position:=0;
Any suggestion?

Thanks.

Mando

  • Full Member
  • ***
  • Posts: 181
Re: large BLOBS load problems with SQLdb
« Reply #1 on: April 30, 2019, 06:01:51 pm »
Does anyone have the solution?

Regards

john horst

  • Jr. Member
  • **
  • Posts: 68
    • JHorst
Re: large BLOBS load problems with SQLdb
« Reply #2 on: April 30, 2019, 07:42:47 pm »
Looking at the src, you might need to add an optional param for text size.
 
Params - "AutoCommit=true" - if you don't want explicitly commit/rollback transactions
->> "TextSize=16777216" - set maximum size of text/image data returned
"ApplicationName=YourAppName" - Set the app name for the connection. MSSQL 2000 and higher only

rvk

  • Hero Member
  • *****
  • Posts: 6110
Re: large BLOBS load problems with SQLdb
« Reply #3 on: April 30, 2019, 07:55:26 pm »
Looking at the src, you might need to add an optional param for text size.

Ieks... yeah, that's really nuts to have as a default in mssqlconn.pp:
Code: Pascal  [Select][+][-]
  1. if Params.IndexOfName(STextSize) <> -1 then
  2.   Execute('SET TEXTSIZE '+Params.Values[STextSize])
  3. else
  4.   Execute('SET TEXTSIZE 16777216');

dsiders

  • Hero Member
  • *****
  • Posts: 1052
Re: large BLOBS load problems with SQLdb
« Reply #4 on: April 30, 2019, 11:12:25 pm »
Looking at the src, you might need to add an optional param for text size.

Ieks... yeah, that's really nuts to have as a default in mssqlconn.pp:
Code: Pascal  [Select][+][-]
  1. if Params.IndexOfName(STextSize) <> -1 then
  2.   Execute('SET TEXTSIZE '+Params.Values[STextSize])
  3. else
  4.   Execute('SET TEXTSIZE 16777216');

Yes, but not as nuts as Microsoft. Their default size is 4096.

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-textsize-transact-sql?view=sql-server-2017

Preview Lazarus 3.99 documentation at: https://dsiders.gitlab.io/lazdocsnext

rvk

  • Hero Member
  • *****
  • Posts: 6110
Re: large BLOBS load problems with SQLdb
« Reply #5 on: April 30, 2019, 11:22:08 pm »
Looking at the src, you might need to add an optional param for text size.
Ieks... yeah, that's really nuts to have as a default in mssqlconn.pp:

Yes, but not as nuts as Microsoft. Their default size is 4096.

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-textsize-transact-sql?view=sql-server-2017
Yeah, but the client-driver sets it to unlimited as default.
So why would fpc need to force it to 16777216?

Quote
The SQL Server Native Client (10.0 and higher) and ODBC Driver for SQL Server automatically specify -1 (unlimited) when connecting.

dsiders

  • Hero Member
  • *****
  • Posts: 1052
Re: large BLOBS load problems with SQLdb
« Reply #6 on: April 30, 2019, 11:49:37 pm »
Looking at the src, you might need to add an optional param for text size.
Ieks... yeah, that's really nuts to have as a default in mssqlconn.pp:

Yes, but not as nuts as Microsoft. Their default size is 4096.

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-textsize-transact-sql?view=sql-server-2017
Yeah, but the client-driver sets it to unlimited as default.
So why would fpc need to force it to 16777216?

Quote
The SQL Server Native Client (10.0 and higher) and ODBC Driver for SQL Server automatically specify -1 (unlimited) when connecting.

My guess is that it's the Least Common Denominator principle in action. The debugging statements indicate that FreeTDS fails on the larger value.  So they're using the largest value that works for both clients.
Preview Lazarus 3.99 documentation at: https://dsiders.gitlab.io/lazdocsnext

Mando

  • Full Member
  • ***
  • Posts: 181
Re: large BLOBS load problems with SQLdb
« Reply #7 on: May 06, 2019, 01:35:20 pm »
Thanks, rvk.

I set 'TextSize' param to 67108864 and it's works for now.

Regards.

rvk

  • Hero Member
  • *****
  • Posts: 6110
Re: large BLOBS load problems with SQLdb
« Reply #8 on: May 06, 2019, 02:02:15 pm »
It was johns find...  ;)
But I'm glad it worked.

Mando

  • Full Member
  • ***
  • Posts: 181
Re: large BLOBS load problems with SQLdb
« Reply #9 on: May 07, 2019, 12:35:32 pm »
It was johns find...  ;)
But I'm glad it worked.

It's true. Sorry John and thank you too.


 

TinyPortal © 2005-2018