Re: HOW TO insert binary data into IMAGE column using db-lib

On Fri, 16 Sep 2011 12:47:40 +0200
LacaK <[email protected]> wrote:

> Now I catch problem with sending sql INSERT statement with binary
> data > 4KB I use dbcmd + dbsqlexec (because AFAIK db-lib does not
> support parameter binding only result columns binding)
> ATM I send sql command like this:
> "insert into table1 (col1, image_col) values(1, 0x...)"
> but only first 4096 bytes are writen into image column ?

I am unable to reproduce your problem.  Perhaps you're being fooled
into thinking you sent only 4 KB because your TEXTSIZE is 4096?  

I did the following:

1.  Picking a handy PDF document, I converted it to a long hexadecimal
string:

	$ hexdump -C NewFeatures.sdk.pdf | cut -b11-60 | tr \\n ' ' |
sed -E 's/ +//g' > insert_data.hex
	
	$ wc insert_data.hex 
	       1       1 1659435 insert_data.hex

2.  convert to an insert statement:

	$ printf "insert image_test values (0x%s)\n" \
		$(cat insert_data.hex) \
		> insert_data.sql

3.  send to the server, and fetch back: 

	$ bsqldb -S$S -U$U -P$P -i insert_data.sql
	$ bsqldb -S$S -U$U -P$P -o t \
	<<< 'set textsize 2000000; select * from image_test'

4.  voila, a PDF:

	$ file t
	t: PDF document, version 1.4

I'm filing this under not-a-bug.  :-)

> I know, that there is family of db-lib functions, which works with 
> IMAGE/TEXT data, but they require "text or image pointer", which is 
> unknown at insertion time.

Correct.  

> Are there any plans for extending db-lib to support parameter binding 
> for sql statements (something like ODBC has)?

I have none.  Stored procedures work, and db-lib supports them, as you
know.  General parameterized queries introduce complexity in the
library and hence in client programs.  

At one time, stored procedure parameters could not be of type
text/image.  That was a server limitation and is no longer the case, at
least with SQL Server 2008.  (I see Sybase ASE 15 still has the old
limitation.)  

I know some database programmers can't use stored procedures, either
because they're not allowed, or because they don't know how.  But
those aren't problems the library should "solve" (read: compound)
by giving the client pseudo-procedures.  

If you or someone else would like to offer a sprited defense of
parameterized queries on a TDS server, please do.  I'd like to hear
it, really.  To me they're anathema.  They make sense on other servers
that ODBC supports, that lack either BCP or stored procedures.  But on
a SQL Server, parameterized queries re-create what stored procedures
were designed to eliminate: uncontrolled, open SQL in the client, with
the concomitant security and release-engineering problems.  

--jkl