Dear monetdb community,

I am new to monetdb and I am very impressed about its speed and nice features like its csv bulk load command.

I have a question on prepared statements: I noticed that when using prepared statements via ODBC or JDBC strange things happened: when inserting 1000 rows, not only insert statements became very slow, also many inserts silently never reached MonetDB, i.e. a random number of records passes and suddenly - observed with ODBC debugging activated - execs are responded with errors. I tried via ODBC (writing from R's RODBC package) and via JDBC (writing from DBVizualizer). I found out that deactivating autocommit will help (verified for ODBC and JDBC), but the problem reappears - again randomly - if the client tool sends batch commits each x records (verified only in DBVizualizer / JDBC, now with explicit errors).

Is that a known limitation or a bug or am I doing wrong? Further down you find information for replication.

Thanks for any help Kind regards

Dr. Jens Oehlschlägel Munich

P.S. Details

I run MonetDB under WindowsProfessionalXP with 3 GB RAM. I installed: MonetDB5-SQL-Installer-i386-20080624.msi MonetDB-ODBC-Installer-i386-20080812.msi monetdb-1.8-jdbc.jar

Then - as recommended - I created

CREATE USER "voc" WITH PASSWORD 'voc' NAME 'VOC Explorer' SCHEMA "sys"; CREATE SCHEMA "voc" AUTHORIZATION "voc"; ALTER USER "voc" SET SCHEMA "voc";

and added my test table

CREATE TABLE databasetest ( "id" INTEGER , "ubyte1" SMALLINT , "ubyte2" SMALLINT , "ubyte3" SMALLINT , "ubyte4" SMALLINT , "ubyte5" SMALLINT , "ubyte6" SMALLINT , "ubyte7" SMALLINT , "ubyte8" SMALLINT , "ubyte9" SMALLINT , "ubyte10" SMALLINT , "ubyte11" SMALLINT , "ubyte12" SMALLINT , "ubyte13" SMALLINT , "ubyte14" SMALLINT , "ubyte15" SMALLINT , "smallint1" SMALLINT , "smallint2" SMALLINT , "smallint3" SMALLINT , "smallint4" SMALLINT , "smallint5" SMALLINT , "smallint6" SMALLINT , "smallint7" SMALLINT , "smallint8" SMALLINT , "smallint9" SMALLINT , "smallint10" SMALLINT , "smallint11" SMALLINT , "smallint12" SMALLINT , "smallint13" SMALLINT , "smallint14" SMALLINT , "smallint15" SMALLINT , "float1" DOUBLE , "float2" DOUBLE , "float3" DOUBLE , "float4" DOUBLE , "float5" DOUBLE , "float6" DOUBLE , "float7" DOUBLE , "float8" DOUBLE , "float9" DOUBLE , "float10" DOUBLE , "float11" DOUBLE , "float12" DOUBLE , "float13" DOUBLE , "float14" DOUBLE , "float15" DOUBLE , "float16" DOUBLE , "float17" DOUBLE , "float18" DOUBLE , "float19" DOUBLE , "float20" DOUBLE ) ;

The test data contains positive and negative numbers, and the following R script (to be executed with R from www.r-project.org) allows to replicate the problem

# R-script commands for replication of problem with prepared statement inserts in MonetDB # You find the installer for the R interpreter under http://cran.at.r-project.org (windows version 2.8.0) # (linux and sourcecode versions also available) # just install and start R # then install the RODBC package (version 1.2-4)

install.packages("RODBC")

# then paste the following lines

# activate ODBC package library(RODBC)

# only if we deactivate autocommit the prepared statement inserts work correctly (and much faster) # this variable switches autocommit of and sends standard commit at end of prepared statements. noAutoCommit <- TRUE

channel <- odbcDriverConnect(connection = "DSN=MonetDB;UID=voc;PWD=voc;DATABASE=demo;host=localhost;port=50000;" , case="tolower" , believeNRows = TRUE , colQuote='"' , tabQuote = '"' , DBMSencoding = "" , rows_at_time = 1000 , bulk_add = "yes" )

if (noAutoCommit) odbcSetAutoCommit(channel, autoCommit=FALSE)

# test connection #sqlTables(channel) #sqlColumns(channel, "databasetest")

b <- 1L # number of batches n <- 10000L # batch size system.time({ for (i in 1:b){ cat("i=", i, "\n") x <- data.frame( id = ((i-1L)*n+1L):(i*n) # 4-byte integer record identifier , ubyte1 = sample(0:35, n, TRUE, prob=NULL) # signed byte columns (1 byte) , ubyte2 = sample(0:1, n, TRUE, prob=NULL) , ubyte3 = sample(0:3, n, TRUE, prob=NULL) , ubyte4 = sample(0:9, n, TRUE, prob=NULL) , ubyte5 = sample(0:24, n, TRUE, prob=NULL) , ubyte6 = sample(0:99, n, TRUE, prob=NULL) , ubyte7 = sample(0:127, n, TRUE, prob=NULL) , ubyte8 = sample(0:1, n, TRUE, prob=c(0.75, 0.25)) , ubyte9 = sample(0:1, n, TRUE, prob=c(0.9,0.1)) , ubyte10 = sample(0:1, n, TRUE, prob=c(0.96,0.04)) , ubyte11 = sample(0:1, n, TRUE, prob=c(0.99,0.01)) , ubyte12 = sample(0:1, n, TRUE, prob=c(0.996,0.004)) , ubyte13 = sample(0:1, n, TRUE, prob=c(0.999,0.001)) , ubyte14 = sample(0:1, n, TRUE, prob=c(0.9996,0.0004)) , ubyte15 = sample(0:1, n, TRUE, prob=c(0.9999,0.0001)) , smallint1 = sample(1:4000, n, TRUE, prob=NULL) # smallint columns (2 bytes) , smallint2 = sample(1:2, n, TRUE, prob=NULL) , smallint3 = sample(1:4, n, TRUE, prob=NULL) , smallint4 = sample(1:10, n, TRUE, prob=NULL) , smallint5 = sample(1:25, n, TRUE, prob=NULL) , smallint6 = sample(1:100, n, TRUE, prob=NULL) , smallint7 = sample(1:256, n, TRUE, prob=NULL) , smallint8 = sample(1:1000, n, TRUE, prob=NULL) , smallint9 = sample(1:32000, n, TRUE, prob=NULL) , smallint10 = sample(0:1, n, TRUE, prob=c(0.75, 0.25)) , smallint11 = sample(0:1, n, TRUE, prob=c(0.9,0.1)) , smallint12 = sample(0:1, n, TRUE, prob=c(0.96,0.04)) , smallint13 = sample(0:1, n, TRUE, prob=c(0.99,0.01)) , smallint14 = sample(0:1, n, TRUE, prob=c(0.996,0.004)) , smallint15 = sample(0:1, n, TRUE, prob=c(0.999,0.001)) , float1 = round(runif(n, 0, 100), 1) # float columns (4 bytes) , float2 = round(runif(n, 0, 100), 1) , float3 = round(runif(n, 0, 100), 1) , float4 = round(runif(n, 0, 100), 1) , float5 = round(runif(n, 0, 100), 1) , float6 = round(runif(n, 0, 100), 1) , float7 = round(runif(n, 0, 100), 1) , float8 = round(runif(n, 0, 100), 1) , float9 = round(runif(n, 0, 100), 1) , float10 = round(runif(n, 0, 100), 1) , float11 = round(runif(n, 0, 100), 1) , float12 = round(runif(n, 0, 100), 1) , float13 = round(runif(n, 0, 100), 1) , float14 = round(runif(n, 0, 100), 1) , float15 = round(runif(n, 0, 100), 1) , float16 = round(runif(n, 0, 100), 1) , float17 = round(runif(n, 0, 100), 1) , float18 = round(runif(n, 0, 100), 1) , float19 = round(runif(n, 0, 100), 1) , float20 = round(runif(n, 0, 100), 1) ) sqlSave(channel, x, tablename="databasetest", rownames=FALSE, append=TRUE, fast=TRUE) if (noAutoCommit) odbcEndTran(channel, commit=TRUE) print(sqlQuery(channel, 'SELECT count(*) as cc, min(id) as minid, max(id) as maxid from databasetest;')) } })

sqlQuery(channel, 'SELECT count(*) as cc, min(id) as minid, max(id) as maxid from databasetest;')

close(channel)