Search Postgresql Archives

storing SMALL large objects to postgres with C# (.NET ODBC layer)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 

Hello,

I've got a problem inserting binary objects into the postgres database.
I have binary objects (e.g. images or smth else) of any size which I
want to insert into the database. Funny is it works for files larger
than 8000 Bytes. If a file is less than 1000 Bytes I get the following
message:
Error message:
--> invalid input syntax for type oid: "\074\077......";

If a file is between 1000 and 8000 Bytes my program just crashes
silently. This is a really strange behaviour since the binary files
vary in sizes (big and small onces). It works with C++ using the
SQLPutData class provided by the ODBC layer. In C# I don't have
anything like that (right?).

So, now the question is: Is that an error in my program, my way of
accessing the database, of creating the insert command, of the C# .NET
code, or the postgreSQL ODBC driver I use? I doubt it is the last one,
since it works with C++. Can anyone help me please??

Technical output (source code, logs, etc):
--------------------------------------------------------------------------

I use:
- WinXP SP2
- VS 2005
- Database version: 8.2.0
- PostgreSQL ANSI Driver Version 8.02.02.00
- I create a ODBC DSN entry
  - standard settings
  - bytea as LO unchecked
  - unchecked LF<> conversion

Here is my C# code to write the binary object:

public int InsertBLOB(byte[] blob) {
OdbcDataAdapter adapter = null;
            try {
                adapter = new OdbcDataAdapter();
                adapter.InsertCommand = new OdbcCommand();
                adapter.InsertCommand.CommandText = "INSERT INTO
binary_object (object) VALUES (?)";
                adapter.InsertCommand.Connection = new
OdbcConnection(this._connection.ConnectionString);  // my DSN
connection string

                // here the binary parameter is defined
                adapter.InsertCommand.Parameters.Add("blob",
OdbcType.VarBinary);
                adapter.InsertCommand.Parameters["blob"].Direction =
ParameterDirection.Input;
                adapter.InsertCommand.Parameters["blob"].Value = blob;
            } catch (Exception e) {
                LogError(e);
            }

            try {
                adapter.InsertCommand.Connection.Open();
                int rowsAffected =
adapter.InsertCommand.ExecuteNonQuery();
                adapter.InsertCommand.Connection.Close();
            } catch (Exception e) {
                LogError(e);
            }
}


Here is my database log from the insert statement from an insert when
the file is larger than 8000 Bytes:
--> obviously the LO with the oid 121156 was created successfully
2007-01-11 17:44:35 LOCATION:  exec_simple_query, postgres.c:811
2007-01-11 17:44:41 LOG:  00000: statement: set DateStyle to 'ISO'
2007-01-11 17:44:41 LOCATION:  exec_simple_query, postgres.c:811
2007-01-11 17:44:41 LOG:  00000: statement: set geqo to 'OFF'
2007-01-11 17:44:41 LOCATION:  exec_simple_query, postgres.c:811
2007-01-11 17:44:41 LOG:  00000: statement: set extra_float_digits to 2
2007-01-11 17:44:41 LOCATION:  exec_simple_query, postgres.c:811
2007-01-11 17:44:41 LOG:  00000: statement: select oid, typbasetype
from pg_type where typname = 'lo'
2007-01-11 17:44:41 LOCATION:  exec_simple_query, postgres.c:811
2007-01-11 17:44:41 LOG:  00000: statement: select pg_client_encoding()
2007-01-11 17:44:41 LOCATION:  exec_simple_query, postgres.c:811
2007-01-11 17:44:41 LOG:  00000: statement: BEGIN
2007-01-11 17:44:41 LOCATION:  exec_simple_query, postgres.c:811
2007-01-11 17:44:41 LOG:  00000: fastpath function call: "lo_creat"
(OID 957)
2007-01-11 17:44:41 LOCATION:  HandleFunctionRequest, fastpath.c:333
2007-01-11 17:44:41 LOG:  00000: fastpath function call: "lo_open" (OID
952)
2007-01-11 17:44:41 LOCATION:  HandleFunctionRequest, fastpath.c:333
2007-01-11 17:44:41 LOG:  00000: fastpath function call: "lo_lseek"
(OID 956)
2007-01-11 17:44:41 LOCATION:  HandleFunctionRequest, fastpath.c:333
2007-01-11 17:44:41 LOG:  00000: fastpath function call: "lowrite" (OID
955)
2007-01-11 17:44:41 LOCATION:  HandleFunctionRequest, fastpath.c:333
2007-01-11 17:44:41 LOG:  00000: fastpath function call: "lo_close"
(OID 953)
2007-01-11 17:44:41 LOCATION:  HandleFunctionRequest, fastpath.c:333
2007-01-11 17:44:41 LOG:  00000: statement: COMMIT
2007-01-11 17:44:41 LOCATION:  exec_simple_query, postgres.c:811
2007-01-11 17:44:41 LOG:  00000: statement: INSERT INTO binary_object
(object) VALUES ('121156'::lo)
2007-01-11 17:44:41 LOCATION:  exec_simple_query, postgres.c:811
2007-01-11 17:44:48 LOG:  00000: statement: set DateStyle to 'ISO'
2007-01-11 17:44:48 LOCATION:  exec_simple_query, postgres.c:811
2007-01-11 17:44:48 LOG:  00000: statement: set geqo to 'OFF'
2007-01-11 17:44:48 LOCATION:  exec_simple_query, postgres.c:811
2007-01-11 17:44:48 LOG:  00000: statement: set extra_float_digits to 2
2007-01-11 17:44:48 LOCATION:  exec_simple_query, postgres.c:811
2007-01-11 17:44:48 LOG:  00000: statement: select oid, typbasetype
from pg_type where typname = 'lo'
2007-01-11 17:44:48 LOCATION:  exec_simple_query, postgres.c:811
2007-01-11 17:44:48 LOG:  00000: statement: select pg_client_encoding()
2007-01-11 17:44:48 LOCATION:  exec_simple_query, postgres.c:811
2007-01-11 17:44:48 LOG:  00000: statement: set DateStyle to 'ISO'
2007-01-11 17:44:48 LOCATION:  exec_simple_query, postgres.c:811
2007-01-11 17:44:48 LOG:  00000: statement: set geqo to 'OFF'
2007-01-11 17:44:48 LOCATION:  exec_simple_query, postgres.c:811
2007-01-11 17:44:48 LOG:  00000: statement: set extra_float_digits to 2
2007-01-11 17:44:48 LOCATION:  exec_simple_query, postgres.c:811
2007-01-11 17:44:48 LOG:  00000: statement: select oid, typbasetype
from pg_type where typname = 'lo'
2007-01-11 17:44:48 LOCATION:  exec_simple_query, postgres.c:811
2007-01-11 17:44:48 LOG:  00000: statement: select pg_client_encoding()
2007-01-11 17:44:48 LOCATION:  exec_simple_query, postgres.c:811
.............


This is a database log file when the XML file is small (here a test
with a few "1" values as bytes:
--> it seems here to switch to insert a bytea object, which of course
is not the case.

2007-01-11 18:14:17 LOG:  00000: statement: set DateStyle to 'ISO'
2007-01-11 18:14:17 LOCATION:  exec_simple_query, postgres.c:811
2007-01-11 18:14:17 LOG:  00000: statement: set geqo to 'OFF'
2007-01-11 18:14:17 LOCATION:  exec_simple_query, postgres.c:811
2007-01-11 18:14:17 LOG:  00000: statement: set extra_float_digits to 2
2007-01-11 18:14:17 LOCATION:  exec_simple_query, postgres.c:811
2007-01-11 18:14:17 LOG:  00000: statement: select oid, typbasetype
from pg_type where typname = 'lo'
2007-01-11 18:14:17 LOCATION:  exec_simple_query, postgres.c:811
2007-01-11 18:14:17 LOG:  00000: statement: select pg_client_encoding()
2007-01-11 18:14:17 LOCATION:  exec_simple_query, postgres.c:811
2007-01-11 18:14:17 WARNING:  22P06: nonstandard use of \\ in a string
literal at character 44
2007-01-11 18:14:17 HINT:  Use the escape string syntax for
backslashes, e.g., E'\\'.
2007-01-11 18:14:17 LOCATION:  check_string_escape_warning, scan.l:932
2007-01-11 18:14:17 LOG:  00000: statement: INSERT INTO binary_object
(object) VALUES
('\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0
 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\
 001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\
 \001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001')
2007-01-11 18:14:17 LOCATION:  exec_simple_query, postgres.c:811
2007-01-11 18:14:17 ERROR:  22P02: invalid input syntax for type oid:
"\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\
 001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\0
 01\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001"
2007-01-11 18:14:17 LOCATION:  oidin_subr, oid.c:64
2007-01-11 18:14:17 STATEMENT:  INSERT INTO binary_object (object)
VALUES
('\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0
 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\
 001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\
 \001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001')


My table binary_object looks like this: ......................

CREATE TABLE binary_object
(
  id serial NOT NULL,
  "object" lo NOT NULL,
  CONSTRAINT binary_object_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;
ALTER TABLE binary_object OWNER TO postgresadmin;
GRANT ALL ON TABLE binary_object TO postgresadmin;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE binary_object TO machine;

--------------------------------------------------------------------------

Thanks for any answers!!
NM
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux