Hi Sjoerd, Thank you very much for your reply! See details below: Sjoerd Mullender wrote:
On 2010-02-13 14:00, Alexander Barkov wrote:
Hello,
I am a new in MonetDB.
I'm testing MonetDB + unixODBC with an application which inserts long BLOB values, and I'm having some problems.
Can you please help me?
1. When I use
rc= SQLBindParameter(hstmt, position, SQL_PARAM_INPUT, SQL_C_DEFAULT, SQL_LONGVARBINARY, 0, 0, (char*) data, data_size, &BindBuf[position].size);
I get error "Optional feature is not implemented".
If I change SQL_LONGVARBINARY to SQL_VARCHAR, it works, however it wants the data in HEX notation, instead of raw binary buffer. That means I need 2*data_size extra memory to encode my binary data in hex, and then send the result send to SQLBindParameter.
Is there a way to bind raw binary data, to avoid hex encoding?
Looking at the code (and not actually trying it), I'd think that you should be able to use raw binary data when using SQL_VARCHAR. However, you do need to specify the actual length and not rely on SQL_NTS.
I'll look into this more later.
My program works fine with Mimer, MySQL, Oracle, PostgeSQL, Sybase and Virtuoso unixODBC drivers. However, I use SQL_LONGVARBINARY as a bind type in my program. I just tried your suggestion to use SQL_VARCHAR with MonetDB, and I got "General error". Some information: This is my table structure: CREATE TABLE bdicti ( url_id INT NOT NULL, state INT NOT NULL, intag00 BLOB NOT NULL, intag01 BLOB NOT NULL, intag02 BLOB NOT NULL, intag03 BLOB NOT NULL, intag04 BLOB NOT NULL, intag05 BLOB NOT NULL, intag06 BLOB NOT NULL, intag07 BLOB NOT NULL, intag08 BLOB NOT NULL, intag09 BLOB NOT NULL, intag0A BLOB NOT NULL, intag0B BLOB NOT NULL, intag0C BLOB NOT NULL, intag0D BLOB NOT NULL, intag0E BLOB NOT NULL, intag0F BLOB NOT NULL, intag10 BLOB NOT NULL, intag11 BLOB NOT NULL, intag12 BLOB NOT NULL, intag13 BLOB NOT NULL, intag14 BLOB NOT NULL, intag15 BLOB NOT NULL, intag16 BLOB NOT NULL, intag17 BLOB NOT NULL, intag18 BLOB NOT NULL, intag19 BLOB NOT NULL, intag1A BLOB NOT NULL, intag1B BLOB NOT NULL, intag1C BLOB NOT NULL, intag1D BLOB NOT NULL, intag1E BLOB NOT NULL, intag1F BLOB NOT NULL ); CREATE INDEX bdicti_url_id ON bdicti (url_id); CREATE INDEX bdicti_state ON bdicti (state); This is what happens step by step: - The program executes SQLPrepare() with 32 placeholders (see the query below). - Then the program binds 32 binary parameters using: SQLBindParameter(hstm, position, SQL_PARAM_INPUT, SQL_C_DEFAULT, SQL_VARCHAR, /* usually I put SQL_LONGVARBINARY here*/ 0, 0, data, data_length); "data_length" is actual data size (not SQL_NTS), "data" is a pointer to the binary string. For example: on position 2, the program binds a binary string with length 9. (see the log below) Note: Some of the parameters are 0-byte long. In this case "data" points to some initialized memory block, and "data_length" is set 0. For example, parameter 1 is an empty string. (see the log below) - After binding all 32 parameter, the program calls: rc= SQLExecute(hstm); which it returns -1 (SQL_ERROR), and diagnostics return "General error". This is the debug log written by the program, so you can guess how the query and the parameters look like: [23569] Prepare {dbmode-blob.c:2240}: INSERT INTO bdicti VALUES(44380,1,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) [23569] BindParameter {dbmode-blob.c:2250} pos=1 VARCHAR(0)'' [23569] BindParameter {dbmode-blob.c:2250} pos=2 VARCHAR(9)'share\x00\x07\x02\x06' [23569] BindParameter {dbmode-blob.c:2250} pos=3 VARCHAR(0)'' [23569] BindParameter {dbmode-blob.c:2250} pos=4 VARCHAR(0)'' [23569] BindParameter {dbmode-blob.c:2250} pos=5 VARCHAR(0)'' [23569] BindParameter {dbmode-blob.c:2250} pos=6 VARCHAR(0)'' [23569] BindParameter {dbmode-blob.c:2250} pos=7 VARCHAR(7)'doc\x00\x07\x03\x05' [23569] BindParameter {dbmode-blob.c:2250} pos=8 VARCHAR(0)'' [23569] BindParameter {dbmode-blob.c:2250} pos=9 VARCHAR(17)'libsamplerate\x00\x07\x04\x04' [23569] BindParameter {dbmode-blob.c:2250} pos=10 VARCHAR(0)'' [23569] BindParameter {dbmode-blob.c:2250} pos=11 VARCHAR(0)'' [23569] BindParameter {dbmode-blob.c:2250} pos=12 VARCHAR(0)'' [23569] BindParameter {dbmode-blob.c:2250} pos=13 VARCHAR(0)'' [23569] BindParameter {dbmode-blob.c:2250} pos=14 VARCHAR(0)'' [23569] BindParameter {dbmode-blob.c:2250} pos=15 VARCHAR(0)'' [23569] BindParameter {dbmode-blob.c:2250} pos=16 VARCHAR(0)'' [23569] BindParameter {dbmode-blob.c:2250} pos=17 VARCHAR(0)'' [23569] BindParameter {dbmode-blob.c:2250} pos=18 VARCHAR(0)'' [23569] BindParameter {dbmode-blob.c:2250} pos=19 VARCHAR(12)'0\x00\x07\x05\x03\x00\x002\x00\x07\x07\x01' [23569] BindParameter {dbmode-blob.c:2250} pos=20 VARCHAR(0)'' [23569] BindParameter {dbmode-blob.c:2250} pos=21 VARCHAR(0)'' [23569] BindParameter {dbmode-blob.c:2250} pos=22 VARCHAR(7)'usr\x00\x07\x01\x07' [23569] BindParameter {dbmode-blob.c:2250} pos=23 VARCHAR(0)'' [23569] BindParameter {dbmode-blob.c:2250} pos=24 VARCHAR(0)'' [23569] BindParameter {dbmode-blob.c:2250} pos=25 VARCHAR(0)'' [23569] BindParameter {dbmode-blob.c:2250} pos=26 VARCHAR(5)'1\x00\x07\x06\x02' [23569] BindParameter {dbmode-blob.c:2250} pos=27 VARCHAR(8)'file\x00\x09\x01\x01' [23569] BindParameter {dbmode-blob.c:2250} pos=28 VARCHAR(0)'' [23569] BindParameter {dbmode-blob.c:2250} pos=29 VARCHAR(0)'' [23569] BindParameter {dbmode-blob.c:2250} pos=30 VARCHAR(0)'' [23569] BindParameter {dbmode-blob.c:2250} pos=31 VARCHAR(0)'' [23569] BindParameter {dbmode-blob.c:2250} pos=32 VARCHAR(0)'' [23569] 0.00 Execute {dbmode-blob.c:2253} [23569]{01} [SQLSTATE:S1000][unixODBC][MonetDB][ODBC Driver 1.0]General error
2. When I insert blob data using rc= SQLExecDirect(hstms, "INSERT INTO t1 (blob_column)" "VALUES " "('<long hex-string>')", SQL_NTS);
It works fine for short data, but it returns this error when hex-string is long enough:
[SQLSTATE:S1000][unixODBC][MonetDB][ODBC Driver 1.0]current transaction is aborted (please ROLLBACK)
I guess I hit some maximum possible query length limit, or maybe maximum possible transaction size.
Is there a way to configure MonetDB to allow longer values?
How large is your data? I'm not aware of any limit in the server.
Please see the SQL script here: http://myoffice.izhnet.ru/~bar/monetdb-sql.txt The long INSERT query itself works fine, however, the next query after it fails with this error: [SQLSTATE:S1000][unixODBC][MonetDB][ODBC Driver 1.0]current transaction is aborted (please ROLLBACK) The INSERT query 400Kb in this example. With shorter INSERT queries the entire transaction works fine.
3. Does the native MonetDB API have the same limitations with the bind types and maximum query(or transaction) size?
The ODBC driver is built on top of the MonetDB API so inherits any limitations the API may have. Having said that, I can't think of any limitation off the top of my head. But again, how much data are we talking about?
Well, it fails with hundreds kilobytes for me at the moment. It would be nice to be able to INSERT hundred megabytes.
Thank you very much!
------------------------------------------------------------------------------ SOLARIS 10 is the OS for Data Centers - provides features such as DTrace, Predictive Self Healing and Award Winning ZFS. Get Solaris 10 NOW http://p.sf.net/sfu/solaris-dev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
------------------------------------------------------------------------
------------------------------------------------------------------------------ SOLARIS 10 is the OS for Data Centers - provides features such as DTrace, Predictive Self Healing and Award Winning ZFS. Get Solaris 10 NOW http://p.sf.net/sfu/solaris-dev2dev
------------------------------------------------------------------------
_______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users