×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Blob field - new line / carriage return

Blob field - new line / carriage return

Blob field - new line / carriage return

(OP)
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.

RE: Blob field - new line / carriage return

"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


smile

RE: Blob field - new line / carriage return

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

RE: Blob field - new line / carriage return

(OP)
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.

RE: Blob field - new line / carriage return

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

RE: Blob field - new line / carriage return

(OP)
ujb, Thanks I'll try it out.

RE: Blob field - new line / carriage return

(OP)
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.

 

RE: Blob field - new line / carriage return

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

smile

RE: Blob field - new line / carriage return

(OP)
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!

RE: Blob field - new line / carriage return

(OP)
Thanks for all your help!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close