Blob field - new line / carriage return 1

[bmarks]

I want to add a record to a table (through a sql stmt/script) and one of the fields is a blob field. I want to put text in the blob field that has multiple lines. How do I break the lines?

E.g. if only one line, my insert would look like:
INSERT INTO TABLE1(ID, BLOBFIELD)
VALUES (7,F_Str_Blob('This is line 1'))

Instead of:

This is line 1

being in the blob field, I want

This is line 1
This is line 2

I'm thinking I need to do something like:

INSERT INTO TABLE1(ID, BLOBFIELD)
VALUES (7,F_Str_Blob('This is line 1'+??+'This is line 2'))
but I don't know what to replace the ?? with.

 

[unclejimbob]

Can this be done?"
Not natively as InterBase does not allow you to mix DML and DDL statements.

Work arounds

1) Issue drop/create statement pairs as part of a script and ignore any
'procedure <xxx> not found' messages

Here is a code example:

-- Command line used to run the test.sql script to add/modify an SP named 'atemp'
isql -user SYSDBA -password masterkey c:\test\test.ib -input c:\temp\test.sql

-- Procedure stored in file test.sql (note the last 2
-- blank lines)
drop procedure atemp;
commit;
create procedure atemp
returns (i integer)
as
begin
select rdb$relation_id from rdb$database into :i;
suspend;
end
;
commit;


2) Make it a rule that your clients all have _exactly_ the same database structure

 

[unclejimbob]

Sorry, replied to the wrong thread - this is what happens when you have someone pestering you.

 

[bmarks]

Too bad, I was getting excited that I had two Interbase questions answered in the same day.

Thanks for the response on the other question though! Hopefully someone will have an answer for this one.

 

[unclejimbob]

Hi, sorry been at a user group meeting all day so have just managed to get back to it...

OK, downloaded FreeUDFLib and did some experimentation.

Environment: IB 7.0/WinXP

If I use three functions from the library as shown below...

declare external function f_StrBlob
cstring(254),
blob
returns parameter 2
entry_point 'StrBlob' module_name 'FreeUDFLib.dll';

declare external function f_BlobAsPChar
blob
returns cstring(1024) /* free_it */ /* or 32000 or whatever... */
entry_point 'BlobAsPChar' module_name 'FreeUDFLib.dll';

declare external function f_CRLF
returns cstring(3) /* free_it */
entry_point 'CRLF' module_name 'FreeUDFLib.dll';

...and a database structure thus...

/*START SCHEMA*/
SET SQL DIALECT 3;

/* CREATE DATABASE 'c:\temp\temp.ib' PAGE_SIZE 4096

DEFAULT CHARACTER SET */

/* External Function declarations */

DECLARE EXTERNAL FUNCTION F_BLOBASPCHAR
BLOB
RETURNS CSTRING(1024) CHARACTER SET NONE
ENTRY_POINT 'BlobAsPChar' MODULE_NAME 'FreeUDFLib.dll';


DECLARE EXTERNAL FUNCTION F_CRLF

RETURNS CSTRING(3) CHARACTER SET NONE
ENTRY_POINT 'CRLF' MODULE_NAME 'FreeUDFLib.dll';


DECLARE EXTERNAL FUNCTION F_STRBLOB
CSTRING(254) CHARACTER SET NONE, BLOB
RETURNS PARAMETER 2
ENTRY_POINT 'StrBlob' MODULE_NAME 'FreeUDFLib.dll';



/* Table: TEST, Owner: SYSDBA */

CREATE TABLE "TEST"
(
"ID" INTEGER NOT NULL,
"BLOBFIELD" BLOB SUB_TYPE TEXT SEGMENT SIZE 80 NOT NULL
);


/*END SCHEMA*/

Then I have some test SQL statements...

/* Dump everything from the table */
delete from Test;

/* Shove some data into a row. */
insert into test (ID,BLOBFIELD) values (1,f_strblob('Uncle'||f_CRLF()||'Jim'||f_CRLF()||'Bob'||f_CRLF()||'Wuz Hair'));
commit;

/* Retrieve the row. */
select ID,f_BlobAsPChar(BLOBFIELD) from Test;

Note that I am only using IBConsole so I can't really see if it has worked correctly but it may be sufficient to give you a start point.

You will probably have to muck around a bit with the external function declarations.

good luck

ujb

 

[bmarks]

ujb, Thanks I'll try it out.

 

[bmarks]

Found a way to do this.
We have a UDF called f_str_blob and the following syntax works for multiple lines:

insert into tablename
values (f_str_blob('first line'||chr(13)||chr(10)||'second line'));

I believe the UDF came from the library Blob UDF Lib , but there is no documentation on it.

 

[unclejimbob]

The function you are talking about very probably comes from FreeUDFLib...

For example you could declare the function as this:

DECLARE EXTERNAL FUNCTION F_STR_BLOB
CSTRING(32767),
BLOB
RETURNS PARAMETER 2
ENTRY_POINT 'StrBlob' MODULE_NAME 'FreeUDFLib.dll';

rather than how I did it:

DECLARE EXTERNAL FUNCTION F_STRBLOB
CSTRING(254) CHARACTER SET NONE, BLOB
RETURNS PARAMETER 2
ENTRY_POINT 'StrBlob' MODULE_NAME 'FreeUDFLib.dll';

If this is the case (and you can find out by looking at the metadata itself to see how the external function is declared), then you shouldn't have any problems finding documentation because FreeUDFLib comes with some, namely
FreeUDFLib.html within the zip file

http://www.ibexpert.com/download/udf/freeudflib.zip

 

[bmarks]

The function is declared as:

DECLARE EXTERNAL FUNCTION F_STR_BLOB
CSTRING(32765) CHARACTER SET NONE,
BLOB
RETURNS PARAMETER 2
ENTRY_POINT 'StrBlob' MODULE_NAME 'BlobUDFLib.dll'

I'll also check out that link.

Thanks!

 

[unclejimbob]

This is the link to the files you require:

http://www.apostasynow.com/blobudflib/blobudflib.zip

This is the file with the function prototype declarations:
BlobFuncs.pas


hth
ujb

 

[bmarks]

Thanks for all your help!