Is it possible to write large strings to Firebird

问题:

The documentation for Firebird implies that you can write large (> 60K) strings to a blob value in a table. So if you have this:

CREATE TABLE MyBlobTable (
theId int PRIMARY KEY NOT NULL,
theBlob BLOB SUB_TYPE 1
)

Then this should work:

insert into MyBlobTable (theId, theBlob) values (1, '[60K characters in a string]')

(example inspired by http://web.firebirdsql.org/dotnetfirebird/blob-sub_type-1-reading-example-csharp.html)

But I've found that neither C# drivers nor FlameRobin can write this value. You get 'Unexpected end of command' (pointing to a spot about 32K into the string, which is a little suspicious)

I figure there is a special way to quote or escape the data values, or maybe a C# equivalent of this Java code (http://www.firebirdfaq.org/faq372/) where a binary file is read directly into the statement. I'm not doing anything fancy with the text data so I'm open to storing it as a binary blob if needed.

Thanks!

Update: "parameterized queries" is the phrase that I was looking for. What I'm doing:

FbParameter param = new FbParameter("@blobVal", FbDbType.Text);
param.Value = myLargeString;
String query = "insert into MyBlobTable (theId, theBlob) values (1, @blobVal)";
using (FbConnection conn = [something from my pool]) {
    using (FbCommand cmd = new FbCommand(query, conn)) {
        cmd.Parameters.Add(param);
        cmd.ExecuteNonQuery();
    }
}

回答1:

You are adding text inline in the query. Then there are limits: first query text in Firebird is limited to 64 kB, there is also a limit to the varchar size.

If you want to add more data to a blob, you need to stream it to the blob using parametrized queries. See for an example: http://www.firebirdsql.org/en/net-examples-of-use/#update_text_blob_field

回答2:

I think that you're hitting the max length of varchar(n) type limit - 32767 bytes. Althougth the destination field is BLOB, the API will treat the value as varchar as you're providing it as a string literal. So the solution should be to use parametrized query (sorry, I don't use C# myself and thus can't provide example).

回答3:

Possibly this large string is to much for the SQL statement. I also read here that blobs within Firebird are not accessible through SQL for writing (but i'm not sure whether this information is correct).

Anyway, you can use streams for writing / reading blobs. For example, from SO: c# - reading/writing blob from firebird, using a byte array for storage, but it should also work with strings.