insert picture > 32k into a blob colom with php

[ulric...@googlemail.com]

Hello,

ich have to insert a jpg picture with max 300k into a blob column of a DB2 8.2 Database. With java it works, but with [php + IBM odbc] I get an error SQL0102A (String to long) Ist the picture smaler (15k) then it works. I think the problem is, that the picture converted in HEX ist larger then 32k.

Is it possible to insert the picture in parts of 32k into the blob?
What can be a sulution?

Regards
Ulrich

[Peter H. Coffin]

On Fri, 15 Feb 2013 03:07:51 -0800 (PST), ulric...@googlemail.com
wrote:

Are you using actual formal BLOBs, with formal LOB & locator functions,
or are you just stuffing binary data into a really wide column and
calling it done? If not using LOBs, start with that. They should be good
to 2 147 483 647 bytes.

--
Better to teach a man to fish than to give him a fish. And if he can't
be bothered to learn to fish and starves to death, that's a good enough
outcome for me.
-- Steve VanDevender

[ulric...@googlemail.com]

Hi Steve,

I do this:

$Data = file_get_contents("picture.jpg");
$Data = bin2hex($Data);
$res = odbc_prepare ($dbconn, "insert into tb_picture (EINGANG,DATEN ) values (current timestamp, '".$Data."')");

$result = odbc_execute($res);

If picture.jpg is less the 15k it works. Is the picture larger, I get the error SQL0102N (Sting is to long)

[Peter H. Coffin]

On Fri, 15 Feb 2013 07:27:33 -0800 (PST), ulric...@googlemail.com
wrote:

> Am Freitag, 15. Februar 2013 15:16:27 UTC+1 schrieb Peter H. Coffin:
>
>> On Fri, 15 Feb 2013 03:07:51 -0800 (PST), ulric...@googlemail.com
>>
>> wrote:
>>
>> > Hello,
>>
>>
>> > ich have to insert a jpg picture with max 300k into a blob column
>> > of a DB2 8.2 Database. With java it works, but with [php + IBM
>> > odbc] I get an error SQL0102A (String to long) Ist the picture
>> > smaler (15k) then it works. I think the problem is, that the
>> > picture converted in HEX ist larger then 32k.
>> >
>> > Is it possible to insert the picture in parts of 32k into the blob?
>> >
>> > What can be a sulution?
>>
>>
>>
>> Are you using actual formal BLOBs, with formal LOB & locator
>> functions, or are you just stuffing binary data into a really wide
>> column and calling it done? If not using LOBs, start with that. They
>> should be good to 2 147 483 647 bytes.
>

> I do this:
>
> $Data = file_get_contents("picture.jpg");
> $Data = bin2hex($Data);
> $res = odbc_prepare ($dbconn, "insert into tb_picture (EINGANG,DATEN ) values (current timestamp, '".$Data."')");
>
> $result = odbc_execute($res);
>
> If picture.jpg is less the 15k it works. Is the picture larger, I get
> the error SQL0102N (Sting is to long)

But, please show your table. DESCRIBE TABLE tb_picture; would be a good
start.

--
81. If I am fighting with the hero atop a moving platform, have
disarmed him, and am about to finish him off and he glances behind
me and drops flat, I too will drop flat instead of quizzically
turning around to find out what he saw. --Evil Overlord list

[Helmut Tessarek]

Hi Ulrich,

> I do this:
>
> $Data = file_get_contents("picture.jpg");
> $Data = bin2hex($Data);
> $res = odbc_prepare ($dbconn, "insert into tb_picture (EINGANG,DATEN ) values (current timestamp, '".$Data."')");
>
> $result = odbc_execute($res);
>
> If picture.jpg is less the 15k it works. Is the picture larger, I get the error SQL0102N (Sting is to long)

First of all, I suggest to use the native driver for DB2:

http://pecl.php.net/package/ibm_db2

Example for inserting a binary:

<?php
$stmt = db2_prepare($conn, "INSERT INTO animal_pictures(picture) VALUES (?)");

$picture = "/opt/albums/spook/grooming.jpg";
$rc = db2_bind_param($stmt, 1, "picture", DB2_PARAM_FILE);
$rc = db2_execute($stmt);
?>

You can also look at the 'tests' folder in the package. I wrote some test
cases when I implemented the binary stuff:
http://svn.php.net/viewvc/pecl/ibm_db2/trunk/tests/

Look at these files:

test_045_FetchArrayBinaryData.phpt
test_144_BindParamInsertStmtPARAM_FILE.phpt
test_1551_FetchAssocBinary.phpt
test_163_FetchBothBinary.phpt

Cheers,
Helmut

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

[ulric...@googlemail.com]

Hi Helmut,

sorry no, I use not the PECL ibm_db2 driver but the IBM "odbc Driver ibm-db2" which i compiled in PHP 5.2.5
This driver does not have a bind command like odbc_bind_param()

The Table is:

describe table tb_mms_empfang_daten


name Schema Name Länge stellen zeichen
-------------------- --------- ------------------ -------- -------- -------
NUMMER SYSIBM BIGINT 8 0 Nein
MMS SYSIBM BIGINT 8 0 Nein
EINGANG SYSIBM TIMESTAMP 10 0 Nein
AUSGANG SYSIBM TIMESTAMP 10 0 Ja
MIMETYPE SYSIBM VARCHAR 255 0 Nein
FILENAME SYSIBM VARCHAR 50 0 Nein
DATEN SYSIBM BLOB 1048576 0 Nein

[ulric...@googlemail.com]

Now I have installed the PECL ibm_db2 Driver and with this driver i can insert pictures in my database.
I think because this driver has the bind command. Thanks for your help.

Regards

Ulrich

[Peter H. Coffin]

On Thu, 21 Feb 2013 08:39:27 -0800 (PST), ulric...@googlemail.com
wrote:

I *suspect* that it's less about having a bind command than that
whatever implmentation of ODBC you've got has a 32k query limit, since
that's where it's failing for you. Since that limit is the smallest of
the combination of all the parts (PHP's is settable 64k, some version of
Windows default to 32k, other packages might be as small as 16k or 4k,
DB2's InfoCenter lists (on SQL and XML limits page) about 2 MB for
veriosn 9), it's not easy to determine exactly that this is the issue,
but...

--
The Write Many, Read Never drive. For those people that don't know
their system has a /dev/null already.
-- Rik Steenwinkel, singing the praises of 8mm Exabytes