By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,636 Members | 1,945 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,636 IT Pros & Developers. It's quick & easy.

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

P: n/a
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\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\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\00 1\001\001\001\001\001\001\001\001\001\001\001\001\ 001\001\001\001\001\001\001\001\001\001\001\001\00 1\001\001\001\001\001\001\001\001\001\001\001\001\ 001\001\001\001\001\001\001\001\001\001\001\001\00 1\001\001\001\001\001\001\001\001\001\001\001\001\ 001\001\001\001\001\001\001\001\001\001\001\001\00 1\001\001\001\001\001\001\001\001\001\001\001\001\ 001\001\001\001\001\001\001\001\001\001\001\001\00 1\001\001\001\001\001\001\001\001\001\001\001\001\ 001\001\001\001\001\001\001\001\001\001\001\001\00 1\001\001\001\001\001\001\001\001\001\001\001\001\ 001\001\001\001\001\001\001\001\001\001\001\001\00 1\001\001\001\001\001\001\001\001\001\001\001\001\ 001\001\001\001\001\001\001\001\001\001\001\001\00 1\001\001\001\001\001\001\001\001\001\001\001\001\ 001\001\001\001\001\001\001\001\001\001\001\001\00 1\001\001\001\001\001\001\001\001\001\001\001\001\ 001\001\001\001\001\001\001\001\001\001\001\001\00 1\001\001\001\001\001\001\001\001\001\001\001\001\ 001\001\001\001\001\001\001\001\001\001\001\001\00 1\001\001\001\001\001\001\001\001\001\001\001\001\ 001\001\001\001\001\001\001\001\001\001\001\001\00 1\001\001\001\001\001\001\001\001\001\001\001\001\ 001\001\001\001\001\001\001\001\001\001\001\001\00 1\001\001\001\001\001\001\001\001\001\001\001\001\ 001\001\001\001\001\001\001\001\001\001\001\001\00 1\001\001\001\001\001\001\001\001\001\001\001\001\ 001\001\001\001\001\001\001\001\001\001\001\001\00 1\001\001\001\001\001\001\001\001\001\001\001\001\ 001\001\001\001\001\001\001\001\001\001\001\001\00 1\001\001\001\001\001\001\001\001\001\001\001\001\ 001\001\001\001\001\001\001\001\001\001\001\001\00 1\001\001\001\001\001\001\001\001\001\001\001\001\ 001\001\001\001\001\001\001\001\001\001\001\001\00 1\001\001\001\001\001\001\001\001\001\001\001\001\ 001\001\001\001\001\001\001\001\001\001\001\001\00 1\001\001\001\001\001\001\001\001\001\001\001\001\ 001\001\001\001\001\001\001\001\001\001\001\001\00 1\001\001\001\001\001\001\001\001\001\001\001\001\ 001\001\001\001\001\001\001\001\001\001\001\001\00 1\001\001\001\001\001\001\001\001\001\001\001\001\ 001\001\001\001\001\001\001\001\001\001\001\001\00 1\001\001\001\001\001\001\001\001\001\001\001\001\ 001\001\001\001\001\001\001\001\001\001\001\001\00 1\001\001\001\001\001\001\001\001\001\001\001\001\ 001\001\001\001\001\001\001\001\001\001\001\001\00 1\001\001\001\001\001\001\001\001\001\001\001\001\ 001\001\001\001\001\001\001\001\001\001\001\001\00 1\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\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\001\\001\\001\\001\\001\\001\\001\\001\\001\\0 01\\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

Jan 11 '07 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.