Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 10 total
Thread How to insert a Blob
Wed, Jul 19 2017 12:21 PMPermanent Link

Arthur Williams

Zarksoft

Avatar

I'm trying to use SQL in DBISAM and am having an issue with the INSERT. I'm issuing a

INSERT INTO TRAINERS (field1,field2,field3) VALUES(1,2,'big string of data');

statement with a TDBISAMQuery component, where field3 is a BLOB. The insert fails with a parse error saying that the data for field3 is in error, expecting a value, memo or Blob Expression.

I tried changing field3 to a Memo but the same error occurs. So how do you insert blob data using the INSERT statement this way, or is it just not possible ? Normally I do it with parameters, but that's not an option in this particular case.
Wed, Jul 19 2017 1:29 PMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Arthur

>I'm trying to use SQL in DBISAM and am having an issue with the INSERT. I'm issuing a
>
>INSERT INTO TRAINERS (field1,field2,field3) VALUES(1,2,'big string of data');
>
>statement with a TDBISAMQuery component, where field3 is a BLOB. The insert fails with a parse error saying that the data for field3 is in error, expecting a value, memo or Blob Expression.
>
>I tried changing field3 to a Memo but the same error occurs. So how do you insert blob data using the INSERT statement this way, or is it just not possible ? Normally I do it with parameters, but that's not an option in this particular case.

Just tried it here in 4.25 in DBSys using two numeric and one memo field and its fine.

First thing to have a look at is if you're pointing at the table you altered the blob to a memo and not to a different table / database.

I've forgotten how to say its binary data I think its putting an X in front of the first quite but I'm probably wrong.

Roy Lambert
Fri, Jul 21 2017 1:02 PMPermanent Link

Arthur Williams

Zarksoft

Avatar

I'm only inserting text data, so it should be simple. How did you specify the data in the Values field for your memo column ? The parser doesn't allow just 'sometext' with apostrophes, so how did you specify it ?



>>
Roy Lambert wrote:


Just tried it here in 4.25 in DBSys using two numeric and one memo field and its fine.
Sat, Jul 22 2017 2:50 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Arthur


>I'm only inserting text data, so it should be simple. How did you specify the data in the Values field for your memo column ? The parser doesn't allow just 'sometext' with apostrophes, so how did you specify it

Just typed a quote delimited string. If you have embedded quotes / apostrophes you have to double up on them just like in a delphi string.

If you want to post an example (including table) to the binaries I'm happy to try it out here

Roy
Mon, Jul 24 2017 10:42 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Arthur,

<< I'm trying to use SQL in DBISAM and am having an issue with the INSERT. I'm issuing a

INSERT INTO TRAINERS (field1,field2,field3) VALUES(1,2,'big string of data');

statement with a TDBISAMQuery component, where field3 is a BLOB. The insert fails with a parse error saying that the data for field3 is in error, expecting a value, memo or Blob Expression. >>

Unfortunately, you can't do what you want with a constant in DBISAM.  Instead, you'll have to use a parameterized INSERT statement and specify the BLOB data as a parameter.

Tim Young
Elevate Software
www.elevatesoft.com
Mon, Jul 24 2017 10:43 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Roy,

<< I've forgotten how to say its binary data I think its putting an X in front of the first quite but I'm probably wrong. >>

ElevateDB supports binary constants, but not DBISAM.

Tim Young
Elevate Software
www.elevatesoft.com
Mon, Jul 24 2017 10:43 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Arthur,

Also forgot: the other alternative is to alter the table so that you're using a Memo field type instead of a generic BLOB field type, but that may not be possible.

Tim Young
Elevate Software
www.elevatesoft.com
Sun, Jul 30 2017 8:29 PMPermanent Link

Arthur Williams

Zarksoft

Avatar

I tried that, changed the field to Memo, but the insert fails with the same message.

>>>>>>>>>>>>>>
Tim Young [Elevate Software] wrote:

Arthur,

Also forgot: the other alternative is to alter the table so that you're using a Memo field type instead of a generic BLOB field type, but that may not be possible.

Tim Young
Elevate Software
www.elevatesoft.com
Mon, Jul 31 2017 9:59 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Arthur,

<< I tried that, changed the field to Memo, but the insert fails with the same message. >>

And you're doing something like this, correct ?

CREATE TABLE IF NOT EXISTS "insertmemo"
(
  "MemoField" MEMO,
PRIMARY KEY ("RecordID") COMPRESS NONE
LOCALE CODE 0
USER MAJOR VERSION 1
);

INSERT INTO insertMemo VALUES('big string of data');

The above works fine for me in DBISAM 4.44 B2.

Tim Young
Elevate Software
www.elevatesoft.com
Mon, Jul 31 2017 10:00 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Arthur,

Is it possible for you to post the complete string that you're trying to insert ?  Is it possible that the string contains embedded single quotes, and *that* is what is fouling up the parsing ?

Tim Young
Elevate Software
www.elevatesoft.com
Image