Commented by: @asfernandes

There is already a way, with parameters.

It's ISQL (or any other client tool) that should read the stream and put in the blob parameter.

Commented by: Stefan Heymann (stefanheymann)

But you can't have the blob data directly in the SQL script, as ASCII text.
(BTW, how does that parameter thing work, is there a description somewhere?)

Commented by: @asfernandes

Looks like you didn't understood me. Parameters: question marks in SQL string.
Blob parameters are passed as a blob id, and you could create it and save large content.

It requires the client tool to do minimal parse (like it already does to separate statements). Think on:
SQL> insert into t (b) values (?);
PARAM> BASE64(blob bytes in base64)

or

SQL> insert into t (b) values (?);
PARAM> file(/tmp/photo.jpg)

This is much more useful than expect sql strings of gigabytes.

Commented by: Stefan Heymann (stefanheymann)

ISQL 2.5 doesn't do what you show here.
And I don't care about the size of my SQL scripts when I am able to put everything I need into one atomic SQL script (memory is cheap).
It would also be possible to write a backup program that creates an SQL script (and not a binary file like GBAK) that can build up the whole database including all the Blobs.

Commented by: @asfernandes

> ISQL 2.5 doesn't do what you show here.

I didn't said it. I said it's prefered approach.

> And I don't care about the size of my SQL scripts when I am able to put everything I need into one atomic SQL script (memory is cheap).

32 bit address space too?

> It would also be possible to write a backup program that creates an SQL script (and not a binary file like GBAK) that can build up the whole database including all the Blobs.

And that script should be readable by a tool too. Firebird engine does not accept multi-statements in one SQL string.

Commented by: Stefan Heymann (stefanheymann)

>I didn't said it. I said it's prefered approach.
What do you mean by "Preferred Approach"? Preferred by what/who? Why do you present a solution that does not work?

>>[...] (memory is cheap).
> 32 bit address space too?
By the time this gets implemented, 32 bit will be declining. There is already a 64-bit Firebird.

>> It would also be possible to write a backup program that creates an SQL script (and not a binary file like GBAK) that can build up the whole database including all the Blobs.
> And that script should be readable by a tool too.
Yes, ISQL -i for example.

Commented by: @asfernandes

> What do you mean by "Preferred Approach"? Preferred by what/who? Why do you present a solution that does not work?

It's IMO preferred solution for the problem from FB POV.

> By the time this gets implemented, 32 bit will be declining. There is already a 64-bit Firebird.

So let enforce bad practices... This "unlimited" sql string is even security risk, and may be used for DoS attacks.

If you want continue discuss it, go to the list please. Here is not the place.

Commented by: @hvlad

Ability to set parameters in SQL script is useful but it is not the same as current limitation on the text literals length.
I see no reason to decline any of this two distinct features.

Commented by: @asfernandes

I'm not against extend max. length of sql string. I'm just saying its proposal should not be based to pass long blobs, and there should be a reasonable (not considering large blobs) limit.

Commented by: @pcisar

Stefan, do you ask for support of huge text parameters in DSQL or just in scripts (ISQL) ? I doubt that support for them in DSQL is viable, and if you're asking for scripts, then it could be easily solved by tools (if there isn't any that supports that already, it's really easy to create one).

Commented by: Stefan Heymann (stefanheymann)

Pavel, this is about ISQL. The idea (as stated in my original description) is to insert or update blob images (e.g. JPEG) with an ISQL script. So you can have /everything/ in /one/ handy script (the DDL that creates tables/views/triggers/etc, and the DML that pre-fills the tables). Think of a software that creates a database and pre-fills it with demo data. Inserting Blob values by way of an ISQL script is currently not possible, even when you have them as binary files. The size of such a script file is not an issue nowadays. Of course that can be done with tools but I like to use the tools that come with Firebird so I don't have to install even more tools on the user's computer. Using multiple concatenations is working (i tried that), but it bloats the database file.

Commented by: @pcisar

Stefan, while I understand that you want simple solution that doesn't have big dependencies, I don't understand what value would have to support potentially huge (GB) blobs in TEXT scripts? It would beat their primary value as human *readable* and *editable* formats. If you're looking for a simple tool (small single executable, multiplatform, open source) that you can easily deploy with your application to load demo data including huge BLOBs from single dump file, then take a look at fbexport (http://fbexport.sourceforge.net/). I even dare to say that you should rather ask us to negotiate with Milan Babuškov (the author of fbexport and fbcopy) the possibility to include these two tools into Firebird project and core FB distribution instead asking for GB values in isql scripts.

Commented by: Stefan Heymann (stefanheymann)

Two points:
(1) I don't think that being readable/editable by humans is the only purpose of script files. Script files are a sequence of commands to be executed by a script processor. Such a file could also be used as a kind of backup format that you can edit with a text editor (you can't edit .fbk files, can you?) Even script files with large "binary" portions (that would be in a readable ASCII representation, no matter if you use base64 or the Firebird hex syntax) can be edited with a text editor and the non-blob areas can of course be read and edited by humans.
(2) I have discussed this feature with Vlad on the above mentioned Conference. After that, he asked me to put that into the tracker.
Making FbExport a part of Firebird itself doesn't look like a bad idea either.

Commented by: @MartinKoeditz

I'd like to agree to Stefan. This is not just a minor problem. I think this is an important issue.

In our business we had to move from Firebird to another database because of that problem. Handling of large texts (protocols for example) is not possible by our used framework. It would be very great to have such a (core) feature.

Greetings
Martin

Commented by: madaleno (madaleno)

Hi,

Any news on this? Maybe for FB3?

This is not only important for binary blobs.
I have this problem with text blobs, when the text contains special chars.
"CHAR(16) CHARACTER SET OCTETS" for example. In this case we can't have this kind of fields in a SQL script.
Text blobs containing ASCII 13,10 and 9 which are common can also mess up a SQL script file.

It would be very important to allow blob fields either as HEX or BASE64.
MS SQL Server does this, at least with HEX encoded data.

Thanks.

Luis Madaleno

Commented by: @asfernandes

LOL, I entered here to submit a comment, then I saw I already submited it. :D

Also, in FB 3 statement length was increased to 10 MB AFAIR.

--------
There is already a way, with parameters.

It's ISQL (or any other client tool) that should read the stream and put in the blob parameter.
--------

Commented by: Nick (nick)

Preffered way for user is:
insert into T(BinaryBlob) values(HexToBin('FEFEEFEFEFEFEEFEEFEF'));
insert into T(BinaryBlob) values(Base64Decode('fdsnjerhufds73fdsj'));
-- and so on.
-- with big limit, defined in firebird.conf or databases.conf (1M, 1G, 16G - let the user decide)