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

N

NM

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\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\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\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\001\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\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\001\\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;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

WCG Stats Saturday 19 October 2013 6
WCG Stats Friday 23 March 2007 7
WCG Stats Thursday 22 March 2007 11
WCG Stats Wednesday 21 March 2007 4
WCG Stats Wednesday 19 February 2014 6
WCG Stats Sunday 03 November 2013 5
FS LARGE FORMAT scanners , INKJET printers , inkjet plotters (hp designjet) , 11x17 color laser $199 0
WCG Stats Wednesday 16 October 2013 6

Top