large BLOBS load problems with SQLdb lazarus / 16-Databases ---------------------------------------------------------------------- Mando · 2019-04-25 08:44 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i TBLOBField(fieldByName('Contenido')).LoadFromFile(fileNames[0]);With this method, if I try to do inmediatly after save, it works:Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i TBLOBField( fieldByName('Contenido')).LoadFromFile(fileNames[0]); Post; SQLQuery1. ApplyUpdates; datos.trBD.CommitRetaining; // Read. It worksTBLOBField( fieldByName('Contenido')).saveToFile(fileNames[0]+'- Regrabado'+extractFileExt(fileNames[0]) );         With an auxiliar stream:Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i st:= TFileStream.create(sd.FileName, fmCreate);         TBLOBField(fieldByName('Contenido')).SaveToStream(st); st.position:=0; st.Free;                                With CreateBlobStream: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i     bs:=CreateBlobStream(fieldByName('Contenido'),bmread);    bs.position:=0; Any suggestion? Thanks. ---------------------------------------------------------------------- Mando · 2019-04-30 16:01 Does anyone have the solution? Regards ---------------------------------------------------------------------- john horst · 2019-04-30 17:42 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 · 2019-04-30 17:55 Quote from: john horst on April 30, 2019, 07:42:47 pmLooking 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i if Params.IndexOfName(STextSize) <> -1 then  Execute('SET TEXTSIZE '+Params.Values[STextSize])else  Execute('SET TEXTSIZE 16777216'); ---------------------------------------------------------------------- dsiders · 2019-04-30 21:12 Quote from: rvk on April 30, 2019, 07:55:26 pmQuote from: john horst on April 30, 2019, 07:42:47 pmLooking 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i if Params.IndexOfName(STextSize) <> -1 then  Execute('SET TEXTSIZE '+Params.Values[STextSize])else  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 ---------------------------------------------------------------------- rvk · 2019-04-30 21:22 Quote from: dsiders on April 30, 2019, 11:12:25 pmQuote from: rvk on April 30, 2019, 07:55:26 pmQuote from: john horst on April 30, 2019, 07:42:47 pmLooking 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? QuoteThe SQL Server Native Client (10.0 and higher) and ODBC Driver for SQL Server automatically specify -1 (unlimited) when connecting. ---------------------------------------------------------------------- dsiders · 2019-04-30 21:49 Quote from: rvk on April 30, 2019, 11:22:08 pmQuote from: dsiders on April 30, 2019, 11:12:25 pmQuote from: rvk on April 30, 2019, 07:55:26 pmQuote from: john horst on April 30, 2019, 07:42:47 pmLooking 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? QuoteThe 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. ---------------------------------------------------------------------- Mando · 2019-05-06 11:35 Thanks, rvk. I set 'TextSize' param to 67108864 and it's works for now. Regards. ---------------------------------------------------------------------- rvk · 2019-05-06 12:02 It was johns find...  But I'm glad it worked. ---------------------------------------------------------------------- Mando · 2019-05-07 10:35 Quote from: rvk on May 06, 2019, 02:02:15 pmIt was johns find...  But I'm glad it worked. It's true. Sorry John and thank you too.