From Newsgroup: comp.lang.tcl


Hello

I'm using sqlite3.51.0 under windows.

I read a png-file in binary mode and get:

set c1
‰PNG

...

After write and read with database:

$d eval {create table t1(i integer, c BLOB);}
$d eval {insert into t1(1,$c1)}
set c2 [$d eval {select c from t1 where i=1}]

I get the following:

set c2
PNG\r\n\n

How could this happen?

Regards
Rene
--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

rene <[email protected]d> wrote:

Hello

I'm using sqlite3.51.0 under windows.

I read a png-file in binary mode and get:

set c1
?PNG

^Z...

After write and read with database:

$d eval {create table t1(i integer, c BLOB);}
$d eval {insert into t1(1,$c1)}
set c2 [$d eval {select c from t1 where i=1}]

I get the following:

set c2
PNG\r\n^Z\n

How could this happen?

Several possibilities:

1) you did not actually read the file in binary mode as you believe you
did;

2) you did something with the binary string from reading the file that
changed the string before you inserted it;

3) for 'blob' columns, it can be better to use the @ syntax to insert
the variable, as the @ syntax forces insertion as a "blob":

eval {insert into t1(1,@c1)}

4) you did something to the c2 variable before displaying it that ended
up changing it.

As you didn't post a complete working code segment that messes the PNG
up, we have nothing to go on to suggest which possible alternative may
be the most probable.

--- Synchronet 3.21d-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl


Rich <[email protected]d> posted:

As you didn't post a complete working code segment that messes the PNG
up, we have nothing to go on to suggest which possible alternative may
be the most probable.

Here is a working script. The problem is the same.

::sqlite3 ::d :memory:
set f [::open t.png r]
::fconfigure $f -translation binary
set c1 [::read $f]
::close $f
::d eval {create table t1(i integer, c BLOB);}
::d eval {insert into t1 values(1,$c1)}
set c2 [::d eval {select c from t1 where i=1}]


--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

* rene <[email protected]d>
| Rich <[email protected]d> posted:

| > As you didn't post a complete working code segment that messes the PNG
| > up, we have nothing to go on to suggest which possible alternative may
| > be the most probable.
| >
| Here is a working script. The problem is the same.

| ::sqlite3 ::d :memory:
| set f [::open t.png r]
| ::fconfigure $f -translation binary
| set c1 [::read $f]
| ::close $f
| ::d eval {create table t1(i integer, c BLOB);}
| ::d eval {insert into t1 values(1,$c1)}
| set c2 [::d eval {select c from t1 where i=1}]

I can confirm that the contents in the DB is altered after reading it
back:

# read from t.png
% foreach c [split [string range $c1 0 10] ""] { puts [format %02x [scan $c %c]] }
89
50
4e
47
0d
0a
1a
0a
00
00
00

# read back from sqlite
% foreach c [split [string range $c2 0 10] ""] { puts [format %02x [scan $c %c]] }
89
50
4e
47
5c
72
5c
6e
1a
5c
6e
%

I.e. the binary \r\n (0x0d 0x0a) in c1 has been changed into
literal \ r \ n (0x5c 0x72 0x5c 0x6e) in c2.

I don't know nothing about blobs in sqlite, and whether the above method
is the correct way to handle binary content in the database, but if it
is, something somewhere in the line or processing translates the binary
content into the ASCII representation.

R'
--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl


Hello Ralf

Could you please try to confirm with "@c1" instead of "$c1" in the insert statement?
It was an advice from Richard Hipp, but it was not working on my side.

Thank you
Rene
--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

On 3/23/26 08:28, rene wrote:

...
Here is a working script. The problem is the same.

::sqlite3 ::d :memory:
set f [::open t.png r]
::fconfigure $f -translation binary
set c1 [::read $f]
::close $f
::d eval {create table t1(i integer, c BLOB);}
::d eval {insert into t1 values(1,$c1)}
set c2 [::d eval {select c from t1 where i=1}]

The select statement returns a result set (a list with zero to
many elements), thus change the last line to

set c2 [lindex [::d eval {select c from t1 where i=1}] 0]

HTH,
Christian
--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl


Thanks, that was the solution.

Rene
--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

* rene <[email protected]d>
| Could you please try to confirm with "@c1" instead of "$c1" in the insert statement?
| It was an advice from Richard Hipp, but it was not working on my side.

Same result, it does not make a difference whether I use $c1 or @c1 in
the sql.

If I store the database in a file (instead of memory) I can see the
correct binary PNG content in the disk file. So I guess it is the
readback which does the transformation.

R'
--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

Ralf Fassel <[email protected]> wrote:

* rene <[email protected]d>
| Could you please try to confirm with "@c1" instead of "$c1" in the insert statement?
| It was an advice from Richard Hipp, but it was not working on my side.

Same result, it does not make a difference whether I use $c1 or @c1 in
the sql.

If I store the database in a file (instead of memory) I can see the
correct binary PNG content in the disk file. So I guess it is the
readback which does the transformation.

The change was occurring because 'eval' always returns a list, and rene
was omitting the required [lindex [] 0] to extract element zero, so he
was getting the "list serialization format" instead of the raw binary
data.
--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

* Rich <[email protected]d>
| Ralf Fassel <[email protected]> wrote:
--<snip-snip>--
| > If I store the database in a file (instead of memory) I can see the
| > correct binary PNG content in the disk file. So I guess it is the
| > readback which does the transformation.

| The change was occurring because 'eval' always returns a list, and rene
| was omitting the required [lindex [] 0] to extract element zero, so he
| was getting the "list serialization format" instead of the raw binary
| data.

Yes, right *after* posting my response I saw the other message
by Christian explaining the problem :-)

R'
--- Synchronet 3.21f-Linux NewsLink 1.2