Subject Re: [firebird-support] Best Way to Pass a Comma Delimited List of Numbers?
Author Helen Borrie
At 07:45 24/07/2008, you wrote:
>We are porting a fair number of stored procedures from SQL Server to
>Firebird. A fair number of these procedures take an input parameter, a
>comma delimited list of integers with a type of NTEXT. (Why the system
>does that would take some explanation, but in short, they are a list
>of object IDs. We have object data mapped to the relational database.)
>I am looking how to pass such a comma delimited list of IDs to a
>Firebird procedure. The VARCHAR(n) data type is really too small for
>the comma delimited list.

32K is too small?

>Is a blob the way to go?

A text blob could work, even if your list string were a lot smaller than 32K.

>If so, what might the code look like? Blobs have a somewhat daunting amount of documentation for a newcomer.

I guess that depends on how you plan to go about it. If you're using a wrapper that has already encapsulated the passing of blobs across the API then you shouldn't have to think about it. Otherwise - yes - you have to figure out your own encapsulation.

On the client side you'll need a way to poke your string into a blob (that's the challenge if you're using the raw API); on the server side, you'll need to write PSQL that parses the contents of the blob. Since Fb 2 there's not much you can't parse in a text blob; and 2.1 has a raft of internal functions implemented that you could call on, without needing to bump into the limits (32K varchar, 64K statement size including parameters, 48K for PSQL blr).