0

I have a Blob column to stores images. I am using Java to dump the database into an SQL script to use for testing purposes. I have everything but images coming out.

I setup the output of the Blob to byte[] using blobValue.getBytes(1L, blobValue.length()) then convert to a hex string using this SO answer. I then used what was recommended from DERBY-2198 comment to cast hex to blob in the SQL script:

CAST(X'<HEX STRING>' AS BLOB)

I am assuming this is going to work, but if not, then I welcome a better solution.

As it stands, I was unable to insert into the database with the SQL script because I am getting an error that states the string constant is too long.

java.sql.SQLException: A string constant starting with 'X'<First ~60 Chars>&' is too long.

and if I run through NetBeans

[Exception, Error Code 0, SQLState 54002] A string constant starting with ... is too long.

I have tried breaking the string down into smaller chunks of about 58 hex long and using Derby concatenation by use of || between the strings.

This gets me a statement that contains

CAST (X'<58 HEX Length>' || '<Another 58 HEX Length>' || ... AS BLOB)

Doing so gets me another error.

java.sql.SQLSyntaxErrorException: No authorized routine named '||' of type 'FUNCTION' 
    having compatible arguments was found.

I tried looking for other solutions, but I could not find anything to get around this issue.

Is there anything that I can do? Is something missing?

CodeMonkey
  • 1,136
  • 16
  • 31
  • It's a little hard to tell, but since you said "dump the database ... to use ... for testing", are you perhaps looking for http://db.apache.org/derby/docs/10.15/ref/rrefexportproctablelobs.html – Bryan Pendleton Jun 29 '19 at 03:33
  • @BryanPendleton I do not think so. Though I cannot be sure without an example of an output file. But the way that we get the output is by querying the data into a Java object and putting it into an SQL script file of insert commands. – CodeMonkey Jun 30 '19 at 03:02
  • Did you find a solution for the "A string constant starting with ... is too long" error message? – morpheus05 Mar 23 '22 at 08:51

0 Answers0