On 2010-02-17 09:49, Alexander Barkov wrote:
Hi Sjoerd,
Sjoerd Mullender wrote:
As promised, I looked at the issue more.
There were at least two problems: - there was no BLOB (or CLOB) support in the ODBC driver (i.e. SQL_LONGVARBINARY and SQL_LONGVARCHAR), - the SQL server refused empty BLOBs (blob '').
I have fixed both issues in the upcoming release (due out later this month).
I created a little program to insert a value according to your description (same table, using SQLBindParameter, SQLPrepare and SQLExecute), and with my fixes that program now runs correctly.
Thank you very much for the good news! I'll be waiting for the next release.
I still have to look into the issue that you had with large amounts of data.
I am integrating mnoGoSearch (http://www.mnogosearch.org) with MonetDB. It looks promising from performance point of view so far (comparing with the same size databases in the other DBMSs). Now it would be nice to test with large amount of data.
I just fixed a bug in the mapi library (underneath odbc) in the code dealing with very large queries. I can now execute your monster query without problem. This fix will also be in the upcoming release. When trying out large databases in MonetDB, make sure you keep address space limitations in mind. All data in all tables that your query touches is loaded or memory mapped into your address space. On a 32 bit architecture that means your tables cannot be larger than 2 or 3 GB (depending on the actual operating system being used). Your database can be larger, but the set of tables touched in a query can not. If you're using a 64 bit architecture (and mserver!) than the limit is too high to be a concern.
Thank you for your help and for the good software!
On 2010-02-15 18:46, Alexander Barkov wrote:
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
------------------------------------------------------------------------------ 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
------------------------------------------------------------------------------ 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
-- Sjoerd Mullender