Testing ODBC branch

gchiu · 2017-06-01 01:03:35 UTC

Ok, set up a user DSN which works when tested from the 64bit ODBC panel

>> do %odbc-test.reb
Script: ODBC Test Script Version: Date:
** Error: [ODBC Firebird Driver][Firebird]Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 14
`
** Where: insert-odbc --anonymous-- insert for-each do catch either either --anonymous-- do
** Near: reduce compose [(sql)] ??
** File: C:\Projects\ren-c\src\extensions\odbc\mod-odbc.c
** Line: 945

ⓘ Note: use WHY for more error information

So, looks like I need to make some changes to the test script. It doesn’t like back ticks.

Console Testing

connection: open odbc://REBOL
insert statement {select rdb$relation_id from rdb$database;}
== [RDB$RELATION_ID]
>> rows: copy statement
== [[128]]

hostilefork · 2017-06-01 01:19:28 UTC

I’m not sure how deep of a standardization ODBC covers, but it probably doesn’t cover variances in SQL syntax between the databases.

This might be an argument against trying to build a better dialect than strings, if not even the syntax itself has a standard form.

gchiu · 2017-06-01 01:27:06 UTC

I found the simple dialect that Rebol/command used was quite sufficient.

And if you wanted something different, you could create that yourself as long as you got the sql statement correct for your DB before you inserted it into the port.

gchiu · 2017-06-01 03:27:44 UTC

Anyway, it would be good if we had a common database dialect for mysql, odbc and mongodb or whatever people want to implement.

gchiu · 2017-06-01 08:21:10 UTC

I’ve changed the sql server ODBC test script to run against Firebird 3.0

There are a number of datatypes that are not supported which I’ve commented out.

And all the tests fail because I need to fix the equality test but at least it’s now completing without errors except the memory leak one

DROP TABLE "bit"
name: => bit
sqltype: => "BOOLEAN"
content: => ["TRUE" "FALSE"]
CREATE TABLE "bit" (id integer generated by default as identity primary key, "value" BOOLEAN NOT NULL)
INSERT INTO "bit" ("value") VALUES (TRUE)
INSERT INTO "bit" ("value") VALUES (FALSE)
SELECT * FROM "bit"
[1 2]
["TRUE" "FALSE"]
QUERY DID NOT MATCH ORIGINAL DATA

DROP TABLE "smallint_s"
name: => smallint_s
sqltype: => "SMALLINT"
content: => [-32768 -10 0 10 32767]
CREATE TABLE "smallint_s" (id integer generated by default as identity primary key, "value" SMALLINT NOT NULL)
INSERT INTO "smallint_s" ("value") VALUES (-32768)
INSERT INTO "smallint_s" ("value") VALUES (-10)
INSERT INTO "smallint_s" ("value") VALUES (0)
INSERT INTO "smallint_s" ("value") VALUES (10)
INSERT INTO "smallint_s" ("value") VALUES (32767)
SELECT * FROM "smallint_s"
[1 2 3 4 5]
[-32768 -10 0 10 32767]
QUERY DID NOT MATCH ORIGINAL DATA

DROP TABLE "integer_s"
name: => integer_s
sqltype: => "INT"
content: => [-2147483648 -10 0 10 2147483647]
CREATE TABLE "integer_s" (id integer generated by default as identity primary key, "value" INT NOT NULL)
INSERT INTO "integer_s" ("value") VALUES (-2147483648)
INSERT INTO "integer_s" ("value") VALUES (-10)
INSERT INTO "integer_s" ("value") VALUES (0)
INSERT INTO "integer_s" ("value") VALUES (10)
INSERT INTO "integer_s" ("value") VALUES (2147483647)
SELECT * FROM "integer_s"
[1 2 3 4 5]
[-2147483648 -10 0 10 2147483647]
QUERY DID NOT MATCH ORIGINAL DATA

DROP TABLE "bigint_s"
name: => bigint_s
sqltype: => "BIGINT"
content: => [-9223372036854775808 -10 0 10 9223372036854775807]
CREATE TABLE "bigint_s" (id integer generated by default as identity primary key, "value" BIGINT NOT NULL)
INSERT INTO "bigint_s" ("value") VALUES (-9223372036854775808)
INSERT INTO "bigint_s" ("value") VALUES (-10)
INSERT INTO "bigint_s" ("value") VALUES (0)
INSERT INTO "bigint_s" ("value") VALUES (10)
INSERT INTO "bigint_s" ("value") VALUES (9223372036854775807)
SELECT * FROM "bigint_s"
[1 2 3 4 5]
[-9223372036854775808 -10 0 10 9223372036854775807]
QUERY DID NOT MATCH ORIGINAL DATA

DROP TABLE "double"
name: => double
sqltype: => "DOUBLE PRECISION"
content: => [-3.4 -1.2 0.0 5.6 7.8]
CREATE TABLE "double" (id integer generated by default as identity primary key, "value" DOUBLE PRECISION NOT NULL)
INSERT INTO "double" ("value") VALUES (-3.4)
INSERT INTO "double" ("value") VALUES (-1.2)
INSERT INTO "double" ("value") VALUES (0.0)
INSERT INTO "double" ("value") VALUES (5.6)
INSERT INTO "double" ("value") VALUES (7.8)
SELECT * FROM "double"
[1 2 3 4 5]
[-3.4 -1.2 0.0 5.6 7.8]
QUERY DID NOT MATCH ORIGINAL DATA

DROP TABLE "float"
name: => float
sqltype: => "FLOAT(20)"
content: => [-3.4 -1.2 0.0 5.6 7.8]
CREATE TABLE "float" (id integer generated by default as identity primary key, "value" FLOAT(20) NOT NULL)
INSERT INTO "float" ("value") VALUES (-3.4)
INSERT INTO "float" ("value") VALUES (-1.2)
INSERT INTO "float" ("value") VALUES (0.0)
INSERT INTO "float" ("value") VALUES (5.6)
INSERT INTO "float" ("value") VALUES (7.8)
SELECT * FROM "float"
[1 2 3 4 5]
[-3.4 -1.2 0.0 5.6 7.8]
QUERY DID NOT MATCH ORIGINAL DATA

DROP TABLE "numeric"
name: => numeric
sqltype: => "NUMERIC(18,2)"
content: => [-3.4 -1.2 0.0 5.6 7.8]
CREATE TABLE "numeric" (id integer generated by default as identity primary key, "value" NUMERIC(18,2) NOT NULL)
INSERT INTO "numeric" ("value") VALUES (-3.4)
INSERT INTO "numeric" ("value") VALUES (-1.2)
INSERT INTO "numeric" ("value") VALUES (0.0)
INSERT INTO "numeric" ("value") VALUES (5.6)
INSERT INTO "numeric" ("value") VALUES (7.8)
SELECT * FROM "numeric"
[1 2 3 4 5]
[-3.4 -1.2 0.0 5.6 7.8]
QUERY DID NOT MATCH ORIGINAL DATA

DROP TABLE "decimal"
name: => decimal
sqltype: => "DECIMAL(3,2)"
content: => [-3.4 -1.2 0.0 5.6 7.8]
CREATE TABLE "decimal" (id integer generated by default as identity primary key, "value" DECIMAL(3,2) NOT NULL)
INSERT INTO "decimal" ("value") VALUES (-3.4)
INSERT INTO "decimal" ("value") VALUES (-1.2)
INSERT INTO "decimal" ("value") VALUES (0.0)
INSERT INTO "decimal" ("value") VALUES (5.6)
INSERT INTO "decimal" ("value") VALUES (7.8)
SELECT * FROM "decimal"
[1 2 3 4 5]
[-3.4 -1.2 0.0 5.6 7.8]
QUERY DID NOT MATCH ORIGINAL DATA

DROP TABLE "date"
name: => date
sqltype: => "TIMESTAMP"
content: => ["'12-Dec-2012'" "'21-Apr-1975'"]
CREATE TABLE "date" (id integer generated by default as identity primary key, "value" TIMESTAMP NOT NULL)
INSERT INTO "date" ("value") VALUES ('12-Dec-2012')
INSERT INTO "date" ("value") VALUES ('21-Apr-1975')
SELECT * FROM "date"
[1 2]
["'12-Dec-2012'" "'21-Apr-1975'"]
QUERY DID NOT MATCH ORIGINAL DATA

DROP TABLE "time"
name: => time
sqltype: => "TIME"
content: => ["'10:00'" "'03:04:00.123'"]
CREATE TABLE "time" (id integer generated by default as identity primary key, "value" TIME NOT NULL)
INSERT INTO "time" ("value") VALUES ('10:00')
INSERT INTO "time" ("value") VALUES ('03:04:00.123')
SELECT * FROM "time"
[1 2]
["'10:00'" "'03:04:00.123'"]
QUERY DID NOT MATCH ORIGINAL DATA

DROP TABLE "timestamp"
name: => timestamp
sqltype: => "TIMESTAMP"
content: => ["'30-May-2017 14:23:08'" "'12-Dec-2012'"]
CREATE TABLE "timestamp" (id integer generated by default as identity primary key, "value" TIMESTAMP NOT NULL)
INSERT INTO "timestamp" ("value") VALUES ('30-May-2017 14:23:08')
INSERT INTO "timestamp" ("value") VALUES ('12-Dec-2012')
SELECT * FROM "timestamp"
[1 2]
["'30-May-2017 14:23:08'" "'12-Dec-2012'"]
QUERY DID NOT MATCH ORIGINAL DATA

DROP TABLE "char"
name: => char
sqltype: => "CHAR(3)"
content: => ["'abc'" "'def'" "'ghi'"]
CREATE TABLE "char" (id integer generated by default as identity primary key, "value" CHAR(3) NOT NULL)
INSERT INTO "char" ("value") VALUES ('abc')
INSERT INTO "char" ("value") VALUES ('def')
INSERT INTO "char" ("value") VALUES ('ghi')
SELECT * FROM "char"
[1 2 3]
["'abc'" "'def'" "'ghi'"]
QUERY DID NOT MATCH ORIGINAL DATA

DROP TABLE "varchar"
name: => varchar
sqltype: => "VARCHAR(10)"
content: => ["''" "'abc'" "'defgh'" "'jklmnopqrs'"]
CREATE TABLE "varchar" (id integer generated by default as identity primary key, "value" VARCHAR(10) NOT NULL)
INSERT INTO "varchar" ("value") VALUES ('')
INSERT INTO "varchar" ("value") VALUES ('abc')
INSERT INTO "varchar" ("value") VALUES ('defgh')
INSERT INTO "varchar" ("value") VALUES ('jklmnopqrs')
SELECT * FROM "varchar"
[1 2 3 4]
["''" "'abc'" "'defgh'" "'jklmnopqrs'"]
QUERY DID NOT MATCH ORIGINAL DATA

DROP TABLE "nchar"
name: => nchar
sqltype: => "NCHAR(3)"
content: => ["'abc'" "'ταБ'" "'ghi'"]
CREATE TABLE "nchar" (id integer generated by default as identity primary key, "value" NCHAR(3) NOT NULL)
INSERT INTO "nchar" ("value") VALUES ('abc')
INSERT INTO "nchar" ("value") VALUES ('ταБ')
INSERT INTO "nchar" ("value") VALUES ('ghi')
SELECT * FROM "nchar"
[1 2 3]
["'abc'" "'ταБ'" "'ghi'"]
QUERY DID NOT MATCH ORIGINAL DATA

DROP TABLE "nvarchar"
name: => nvarchar
sqltype: => "VARCHAR(10)"
content: => ["''" "'abc'" "'ταБЬℓσ'" "'٩(●̮̮̃•̃)۶'"]
CREATE TABLE "nvarchar" (id integer generated by default as identity primary key, "value" VARCHAR(10) NOT NULL)
INSERT INTO "nvarchar" ("value") VALUES ('')
INSERT INTO "nvarchar" ("value") VALUES ('abc')
INSERT INTO "nvarchar" ("value") VALUES ('ταБЬℓσ')
INSERT INTO "nvarchar" ("value") VALUES ('٩(●̮̮̃•̃)۶')
SELECT * FROM "nvarchar"
[1 2 3 4]
["''" "'abc'" "'ταБЬℓσ'" "'٩(●̮̮̃•̃)۶'"]
QUERY DID NOT MATCH ORIGINAL DATA

DROP TABLE "binary"
name: => binary
sqltype: => "CHAR(3)"
content: => ["x'00'" "x'010203'" "x'FFFFFF'"]
CREATE TABLE "binary" (id integer generated by default as identity primary key, "value" CHAR(3) NOT NULL)
INSERT INTO "binary" ("value") VALUES (x'00')
INSERT INTO "binary" ("value") VALUES (x'010203')
INSERT INTO "binary" ("value") VALUES (x'FFFFFF')
SELECT * FROM "binary"
[1 2 3]
["x'00'" "x'010203'" "x'FFFFFF'"]
QUERY DID NOT MATCH ORIGINAL DATA

DROP TABLE "varbinary"
name: => varbinary
sqltype: => "char(10)"
content: => ["x''" "x'010203'" "x'DECAFBADCAFE'"]
CREATE TABLE "varbinary" (id integer generated by default as identity primary key, "value" char(10) NOT NULL)
INSERT INTO "varbinary" ("value") VALUES (x'')
INSERT INTO "varbinary" ("value") VALUES (x'010203')
INSERT INTO "varbinary" ("value") VALUES (x'DECAFBADCAFE')
SELECT * FROM "varbinary"
[1 2 3]
["x''" "x'010203'" "x'DECAFBADCAFE'"]
QUERY DID NOT MATCH ORIGINAL DATA

DROP TABLE "blob"
name: => blob
sqltype: => "BLOB(10)"
content: => ["x''" "x'010203'" "x'DECAFBADCAFE'"]
CREATE TABLE "blob" (id integer generated by default as identity primary key, "value" BLOB(10) NOT NULL)
INSERT INTO "blob" ("value") VALUES (x'')
INSERT INTO "blob" ("value") VALUES (x'010203')
INSERT INTO "blob" ("value") VALUES (x'DECAFBADCAFE')
SELECT * FROM "blob"
[1 2 3]
["x''" "x'010203'" "x'DECAFBADCAFE'"]
QUERY DID NOT MATCH ORIGINAL DATA

*** PG_Mem_Usage = 2147483896 ***
Memory accounting imbalance: Rebol internally tracks how much
memory it uses to know when to garbage collect, etc.  For
some reason this accounting did not balance to zero on exit.
Run under Valgrind with --leak-check=full --track-origins=yes
to find out why this is happening.

gchiu · 2017-06-01 08:29:56 UTC

The other issue I’ve noted is that a lock on the database file is present even though the connection is closed. I have to quit rebol before the lock is released.

gchiu · 2017-06-01 20:41:05 UTC

I’ve updated the test script for Firebird so that it converts time! date! binary! string! logic! into the format needed for odbc

hostilefork · 2017-06-01 21:28:46 UTC

dump name
dump sqltype
dump content

Or you could just dump [name sqltype content]

gchiu · 2017-06-01 21:31:24 UTC

Good point … I forget about these things.

gchiu · 2017-06-02 04:51:46 UTC

Updated the script again, and it’s getting better

Failed on:
failures: => ["TIME" "NCHAR(3)" "VARCHAR(10)" "CHAR(3)" "CHAR(10)"]
Success on:
success: => ["BOOLEAN" "SMALLINT" "INT" "BIGINT" "DOUBLE PRECISION" "FLOAT(20)" "NUMERIC(18,2)" "DECIMAL(3,2)" "TIMESTAMP" "TIMESTAMP" "CHAR(3)" "VARCHAR(10)" "BLOB(10)"]

So, the failures on:

Time - loss of fractional seconds,
NCHAR(3) - “ταБ”
VARCHAR(10) - “ταБЬℓσ” “٩(●̮̮̃•̃)۶”
BINARY [#{000000} #{010203} #{FFFFFF}]
VARBINARY [#{} #{010203} #{DECAFBADCAFE}]

but the binary values were okay when using a BLOB field.

gchiu · 2017-06-02 09:04:21 UTC

Latest log with the latest script.

   DROP TABLE "BIT"
    name: => bit
    sqltype: => "BOOLEAN"
    content: => [true false true]
    CREATE TABLE "BIT" (ID integer generated by default as identity primary key, "SQLVALUE" BOOLEAN NOT NULL)
    s: => {INSERT INTO "BIT" ("SQLVALUE") VALUES (TRUE)}
    s: => {INSERT INTO "BIT" ("SQLVALUE") VALUES (FALSE)}
    s: => {INSERT INTO "BIT" ("SQLVALUE") VALUES (TRUE)}
    SELECT "SQLVALUE" FROM "BIT"
    [true false true]
    [true false true]
    QUERY MATCHED ORIGINAL DATA

DROP TABLE "SMALLINT_S"
name: => smallint_s
sqltype: => "SMALLINT"
content: => [-32768 -10 0 10 32767]
CREATE TABLE "SMALLINT_S" (ID integer generated by default as identity primary key, "SQLVALUE" SMALLINT NOT NULL)
s: => {INSERT INTO "SMALLINT_S" ("SQLVALUE") VALUES (-32768)}
s: => {INSERT INTO "SMALLINT_S" ("SQLVALUE") VALUES (-10)}
s: => {INSERT INTO "SMALLINT_S" ("SQLVALUE") VALUES (0)}
s: => {INSERT INTO "SMALLINT_S" ("SQLVALUE") VALUES (10)}
s: => {INSERT INTO "SMALLINT_S" ("SQLVALUE") VALUES (32767)}
SELECT "SQLVALUE" FROM "SMALLINT_S"
[-32768 -10 0 10 32767]
[-32768 -10 0 10 32767]
QUERY MATCHED ORIGINAL DATA

DROP TABLE "INTEGER_S"
name: => integer_s
sqltype: => "INT"
content: => [-2147483648 -10 0 10 2147483647]
CREATE TABLE "INTEGER_S" (ID integer generated by default as identity primary key, "SQLVALUE" INT NOT NULL)
s: => {INSERT INTO "INTEGER_S" ("SQLVALUE") VALUES (-2147483648)}
s: => {INSERT INTO "INTEGER_S" ("SQLVALUE") VALUES (-10)}
s: => {INSERT INTO "INTEGER_S" ("SQLVALUE") VALUES (0)}
s: => {INSERT INTO "INTEGER_S" ("SQLVALUE") VALUES (10)}
s: => {INSERT INTO "INTEGER_S" ("SQLVALUE") VALUES (2147483647)}
SELECT "SQLVALUE" FROM "INTEGER_S"
[-2147483648 -10 0 10 2147483647]
[-2147483648 -10 0 10 2147483647]
QUERY MATCHED ORIGINAL DATA

DROP TABLE "BIGINT_S"
name: => bigint_s
sqltype: => "BIGINT"
content: => [-9223372036854775808 -10 0 10 9223372036854775807]
CREATE TABLE "BIGINT_S" (ID integer generated by default as identity primary key, "SQLVALUE" BIGINT NOT NULL)
s: => {INSERT INTO "BIGINT_S" ("SQLVALUE") VALUES (-9223372036854775808)}
s: => {INSERT INTO "BIGINT_S" ("SQLVALUE") VALUES (-10)}
s: => {INSERT INTO "BIGINT_S" ("SQLVALUE") VALUES (0)}
s: => {INSERT INTO "BIGINT_S" ("SQLVALUE") VALUES (10)}
s: => {INSERT INTO "BIGINT_S" ("SQLVALUE") VALUES (9223372036854775807)}
SELECT "SQLVALUE" FROM "BIGINT_S"
[-9223372036854775808 -10 0 10 9223372036854775807]
[-9223372036854775808 -10 0 10 9223372036854775807]
QUERY MATCHED ORIGINAL DATA

DROP TABLE "DOUBLE"
name: => double
sqltype: => "DOUBLE PRECISION"
content: => [-3.4 -1.2 0.0 5.6 7.8]
CREATE TABLE "DOUBLE" (ID integer generated by default as identity primary key, "SQLVALUE" DOUBLE PRECISION NOT NULL)
s: => {INSERT INTO "DOUBLE" ("SQLVALUE") VALUES (-3.4)}
s: => {INSERT INTO "DOUBLE" ("SQLVALUE") VALUES (-1.2)}
s: => {INSERT INTO "DOUBLE" ("SQLVALUE") VALUES (0.0)}
s: => {INSERT INTO "DOUBLE" ("SQLVALUE") VALUES (5.6)}
s: => {INSERT INTO "DOUBLE" ("SQLVALUE") VALUES (7.8)}
SELECT "SQLVALUE" FROM "DOUBLE"
[-3.4 -1.2 0.0 5.6 7.8]
[-3.4 -1.2 0.0 5.6 7.8]
QUERY MATCHED ORIGINAL DATA

DROP TABLE "FLOAT"
name: => float
sqltype: => "FLOAT(20)"
content: => [-3.4 -1.2 0.0 5.6 7.8]
CREATE TABLE "FLOAT" (ID integer generated by default as identity primary key, "SQLVALUE" FLOAT(20) NOT NULL)
s: => {INSERT INTO "FLOAT" ("SQLVALUE") VALUES (-3.4)}
s: => {INSERT INTO "FLOAT" ("SQLVALUE") VALUES (-1.2)}
s: => {INSERT INTO "FLOAT" ("SQLVALUE") VALUES (0.0)}
s: => {INSERT INTO "FLOAT" ("SQLVALUE") VALUES (5.6)}
s: => {INSERT INTO "FLOAT" ("SQLVALUE") VALUES (7.8)}
SELECT "SQLVALUE" FROM "FLOAT"
[-3.4 -1.2 0.0 5.6 7.8]
[-3.4 -1.2 0.0 5.6 7.8]
QUERY MATCHED ORIGINAL DATA

DROP TABLE "NUMERIC"
name: => numeric
sqltype: => "NUMERIC(18,2)"
content: => [-3.4 -1.2 0.0 5.6 7.8]
CREATE TABLE "NUMERIC" (ID integer generated by default as identity primary key, "SQLVALUE" NUMERIC(18,2) NOT NULL)
s: => {INSERT INTO "NUMERIC" ("SQLVALUE") VALUES (-3.4)}
s: => {INSERT INTO "NUMERIC" ("SQLVALUE") VALUES (-1.2)}
s: => {INSERT INTO "NUMERIC" ("SQLVALUE") VALUES (0.0)}
s: => {INSERT INTO "NUMERIC" ("SQLVALUE") VALUES (5.6)}
s: => {INSERT INTO "NUMERIC" ("SQLVALUE") VALUES (7.8)}
SELECT "SQLVALUE" FROM "NUMERIC"
[-3.4 -1.2 0.0 5.6 7.8]
[-3.4 -1.2 0.0 5.6 7.8]
QUERY MATCHED ORIGINAL DATA

DROP TABLE "DECIMAL"
name: => decimal
sqltype: => "DECIMAL(3,2)"
content: => [-3.4 -1.2 0.0 5.6 7.8]
CREATE TABLE "DECIMAL" (ID integer generated by default as identity primary key, "SQLVALUE" DECIMAL(3,2) NOT NULL)
s: => {INSERT INTO "DECIMAL" ("SQLVALUE") VALUES (-3.4)}
s: => {INSERT INTO "DECIMAL" ("SQLVALUE") VALUES (-1.2)}
s: => {INSERT INTO "DECIMAL" ("SQLVALUE") VALUES (0.0)}
s: => {INSERT INTO "DECIMAL" ("SQLVALUE") VALUES (5.6)}
s: => {INSERT INTO "DECIMAL" ("SQLVALUE") VALUES (7.8)}
SELECT "SQLVALUE" FROM "DECIMAL"
[-3.4 -1.2 0.0 5.6 7.8]
[-3.4 -1.2 0.0 5.6 7.8]
QUERY MATCHED ORIGINAL DATA

DROP TABLE "DATE"
name: => date
sqltype: => "TIMESTAMP"
content: => [12-Dec-2012 21-Apr-1975]
CREATE TABLE "DATE" (ID integer generated by default as identity primary key, "SQLVALUE" TIMESTAMP NOT NULL)
s: => {INSERT INTO "DATE" ("SQLVALUE") VALUES ('12-Dec-2012')}
s: => {INSERT INTO "DATE" ("SQLVALUE") VALUES ('21-Apr-1975')}
SELECT "SQLVALUE" FROM "DATE"
[12-Dec-2012/0:00 21-Apr-1975/0:00]
[12-Dec-2012 21-Apr-1975]
QUERY MATCHED ORIGINAL DATA

DROP TABLE "TIME"
name: => time
sqltype: => "TIME"
content: => [10:00 11:01:12 12:13:14.1 12:13:14.12 3:04:00.123]
CREATE TABLE "TIME" (ID integer generated by default as identity primary key, "SQLVALUE" TIME NOT NULL)
s: => {INSERT INTO "TIME" ("SQLVALUE") VALUES ('10:00')}
s: => {INSERT INTO "TIME" ("SQLVALUE") VALUES ('11:01:12')}
s: => {INSERT INTO "TIME" ("SQLVALUE") VALUES ('12:13:14.1')}
s: => {INSERT INTO "TIME" ("SQLVALUE") VALUES ('12:13:14.12')}
s: => {INSERT INTO "TIME" ("SQLVALUE") VALUES ('3:04:00.123')}
SELECT "SQLVALUE" FROM "TIME"
[10:00 11:01:12 12:13:14 12:13:14 3:04]
[10:00 11:01:12 12:13:14.1 12:13:14.12 3:04:00.123]
QUERY DID NOT MATCH ORIGINAL DATA

DROP TABLE "TIMESTAMP"
name: => timestamp
sqltype: => "TIMESTAMP"
content: => [30-May-2017/14:23:08 12-Dec-2012]
CREATE TABLE "TIMESTAMP" (ID integer generated by default as identity primary key, "SQLVALUE" TIMESTAMP NOT NULL)
s: => {INSERT INTO "TIMESTAMP" ("SQLVALUE") VALUES ('30-May-2017 14:23:08')}
s: => {INSERT INTO "TIMESTAMP" ("SQLVALUE") VALUES ('12-Dec-2012')}
SELECT "SQLVALUE" FROM "TIMESTAMP"
[30-May-2017/14:23:08 12-Dec-2012/0:00]
[30-May-2017/14:23:08 12-Dec-2012]
QUERY MATCHED ORIGINAL DATA

DROP TABLE "CHAR"
name: => char
sqltype: => "CHAR(3)"
content: => ["abc" "def" "ghi"]
CREATE TABLE "CHAR" (ID integer generated by default as identity primary key, "SQLVALUE" CHAR(3) NOT NULL)
s: => {INSERT INTO "CHAR" ("SQLVALUE") VALUES ('abc')}
s: => {INSERT INTO "CHAR" ("SQLVALUE") VALUES ('def')}
s: => {INSERT INTO "CHAR" ("SQLVALUE") VALUES ('ghi')}
SELECT "SQLVALUE" FROM "CHAR"
["abc" "def" "ghi"]
["abc" "def" "ghi"]
QUERY MATCHED ORIGINAL DATA

DROP TABLE "VARCHAR"
name: => varchar
sqltype: => "VARCHAR(10)"
content: => ["" "abc" "defgh" "jklmnopqrs"]
CREATE TABLE "VARCHAR" (ID integer generated by default as identity primary key, "SQLVALUE" VARCHAR(10) NOT NULL)
s: => {INSERT INTO "VARCHAR" ("SQLVALUE") VALUES ('')}
s: => {INSERT INTO "VARCHAR" ("SQLVALUE") VALUES ('abc')}
s: => {INSERT INTO "VARCHAR" ("SQLVALUE") VALUES ('defgh')}
s: => {INSERT INTO "VARCHAR" ("SQLVALUE") VALUES ('jklmnopqrs')}
SELECT "SQLVALUE" FROM "VARCHAR"
["" "abc" "defgh" "jklmnopqrs"]
["" "abc" "defgh" "jklmnopqrs"]
QUERY MATCHED ORIGINAL DATA

DROP TABLE "NCHAR"
name: => nchar
sqltype: => "NCHAR(3)"
content: => ["abc" "ταБ" "ghi"]
CREATE TABLE "NCHAR" (ID integer generated by default as identity primary key, "SQLVALUE" NCHAR(3) NOT NULL)
s: => {INSERT INTO "NCHAR" ("SQLVALUE") VALUES ('abc')}
s: => {INSERT INTO "NCHAR" ("SQLVALUE") VALUES ('ταБ')}
s: => {INSERT INTO "NCHAR" ("SQLVALUE") VALUES ('ghi')}
SELECT "SQLVALUE" FROM "NCHAR"
["abc" "ta?" "ghi"]
["abc" "ταБ" "ghi"]
QUERY DID NOT MATCH ORIGINAL DATA

DROP TABLE "NVARCHAR"
name: => nvarchar
sqltype: => "VARCHAR(10)"
content: => ["" "abc" "ταБЬℓσ" "٩(●̮̮̃•̃)۶"]
CREATE TABLE "NVARCHAR" (ID integer generated by default as identity primary key, "SQLVALUE" VARCHAR(10) NOT NULL)
s: => {INSERT INTO "NVARCHAR" ("SQLVALUE") VALUES ('')}
s: => {INSERT INTO "NVARCHAR" ("SQLVALUE") VALUES ('abc')}
s: => {INSERT INTO "NVARCHAR" ("SQLVALUE") VALUES ('ταБЬℓσ')}
s: => {INSERT INTO "NVARCHAR" ("SQLVALUE") VALUES ('٩(●̮̮̃•̃)۶')}
SELECT "SQLVALUE" FROM "NVARCHAR"
["" "abc" "ta??ls" "?(???~•~)?"]
["" "abc" "ταБЬℓσ" "٩(●̮̮̃•̃)۶"]
QUERY DID NOT MATCH ORIGINAL DATA

DROP TABLE "BINARY"
name: => binary
sqltype: => "CHAR(3)"
content: => [#{000000} #{010203} #{FFFFFF}]
CREATE TABLE "BINARY" (ID integer generated by default as identity primary key, "SQLVALUE" CHAR(3) NOT NULL)
s: => {INSERT INTO "BINARY" ("SQLVALUE") VALUES (x'000000')}
s: => {INSERT INTO "BINARY" ("SQLVALUE") VALUES (x'010203')}
s: => {INSERT INTO "BINARY" ("SQLVALUE") VALUES (x'FFFFFF')}
SELECT "SQLVALUE" FROM "BINARY"
["   " "^A^B^C" "ÿÿÿ"]
[#{000000} #{010203} #{FFFFFF}]
QUERY DID NOT MATCH ORIGINAL DATA

DROP TABLE "VARBINARY"
name: => varbinary
sqltype: => "CHAR(10)"
content: => [#{} #{010203} #{DECAFBADCAFE}]
CREATE TABLE "VARBINARY" (ID integer generated by default as identity primary key, "SQLVALUE" CHAR(10) NOT NULL)
s: => {INSERT INTO "VARBINARY" ("SQLVALUE") VALUES (x'')}
s: => {INSERT INTO "VARBINARY" ("SQLVALUE") VALUES (x'010203')}
s: => {INSERT INTO "VARBINARY" ("SQLVALUE") VALUES (x'DECAFBADCAFE')}
SELECT "SQLVALUE" FROM "VARBINARY"
["          " "^A^B^C       " "ÞÊûÊþ    "]
[#{} #{010203} #{DECAFBADCAFE}]
QUERY DID NOT MATCH ORIGINAL DATA

DROP TABLE "BLOB"
name: => blob
sqltype: => "BLOB(10)"
content: => [#{} #{010203} #{DECAFBADCAFE}]
CREATE TABLE "BLOB" (ID integer generated by default as identity primary key, "SQLVALUE" BLOB(10) NOT NULL)
s: => {INSERT INTO "BLOB" ("SQLVALUE") VALUES (x'')}
s: => {INSERT INTO "BLOB" ("SQLVALUE") VALUES (x'010203')}
s: => {INSERT INTO "BLOB" ("SQLVALUE") VALUES (x'DECAFBADCAFE')}
SELECT "SQLVALUE" FROM "BLOB"
[#{} #{010203} #{DECAFBADCAFE}]
[#{} #{010203} #{DECAFBADCAFE}]
QUERY MATCHED ORIGINAL DATA

Failed on:
failures: => ["TIME" "NCHAR(3)" "VARCHAR(10)" "CHAR(3)" "CHAR(10)"]
Success on:
success: => ["BOOLEAN" "SMALLINT" "INT" "BIGINT" "DOUBLE PRECISION" "FLOAT(20)" "NUMERIC(18,2)" "DECIMAL(3,2)" "TIMESTAMP" "TIMESTAMP" "CHAR(3)" "VARCHAR(10)" "BLOB(10)"]
*** PG_Mem_Usage = 2147483824 ***
Memory accounting imbalance: Rebol internally tracks how much
memory it uses to know when to garbage collect, etc.  For
some reason this accounting did not balance to zero on exit.
Run under Valgrind with --leak-check=full --track-origins=yes
to find out why this is happening.

gchiu · 2017-06-02 23:48:59 UTC

Using Rebol/View

>> db: open odbc://rebol
>> db-port: first db
>> insert db-port {select * from REBTIME}
>> probe copy db-port
[[1 10:00] [2 11:01:12] [3 12:13:14.1] [4 12:13:14.12] [5 3:04:00.123]]
== [[1 10:00] [2 11:01:12] [3 12:13:14.1] [4 12:13:14.12] [5 3:04:00.123]]

but Rebol/View doesn’t support unicode so I’ll have to find another client for the unicode tests.

gchiu · 2017-06-03 00:06:45 UTC

The tools I’ve downloaded to browse the database are just too old and I don’t think come with unicode support.

So, I’m going to bite the bullet and download eval versions of Interbase and DB2

gchiu · 2017-06-03 21:15:36 UTC

Using latest build of Interbase

C:\Users\Graham\rebol3\odbc>r3-core odb-test.reb
s: => {DROP TABLE "REBBIT"}
name: => bit
sqltype: => "BOOLEAN"
content: => [true false true]
s: => {CREATE TABLE "REBBIT" (ID integer, "SQLVALUE" BOOLEAN NOT NULL)}
s: => {CREATE GENERATOR "INCBIT"}
s: => {CREATE TRIGGER autoinc FOR REBBIT ACTIVE BEFORE INSERT POSITION 0 AS BEGIN NEW.ID = GEN_ID(incbit, 1); END}
s: => {INSERT INTO "REBBIT" ("SQLVALUE") VALUES (TRUE)}
s: => {INSERT INTO "REBBIT" ("SQLVALUE") VALUES (FALSE)}
s: => {INSERT INTO "REBBIT" ("SQLVALUE") VALUES (TRUE)}
s: => {SELECT "SQLVALUE" FROM "REBBIT"}
VAL_TYPE() called on unreadable BLANK!
C Source File C:\Projects\ren-c\src\core\m-pools.c, Line 1528
REBVAL init on tick #126914 at C:\Projects\ren-c\src\extensions\odbc\mod-odbc.c:1105
Kind=32
Containing series for value pointer found, panicking it:
unmanaged series was likely created during evaluator tick: 126914

gchiu · 2017-06-03 21:56:54 UTC

I think I need to find something better documented.

Now to install DB2

gchiu · 2017-06-03 23:58:13 UTC

Using DB2 Express

C:\Users\Graham\rebol3\odbc>r3-core db2-test.reb
s: => {DROP TABLE "REBBIT"}
name: => bit
sqltype: => "CHAR(1)"
content: => [true false true]
s: => {CREATE TABLE "REBBIT" (ID integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) , "SQLVALUE" CHAR(1) NOT NULL)}
s: => {INSERT INTO "REBBIT" ("SQLVALUE") VALUES (1)}
s: => {INSERT INTO "REBBIT" ("SQLVALUE") VALUES (0)}
s: => {INSERT INTO "REBBIT" ("SQLVALUE") VALUES (1)}
s: => {SELECT "SQLVALUE" FROM "REBBIT"}
["1" "0" "1"]
[true false true]
QUERY DID NOT MATCH ORIGINAL DATA

s: => {DROP TABLE "REBSMALLINT_S"}
name: => smallint_s
sqltype: => "SMALLINT"
content: => [-32768 -10 0 10 32767]
s: => {CREATE TABLE "REBSMALLINT_S" (ID integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) , "SQLVALUE" SMALLINT NOT NULL)}
s: => {INSERT INTO "REBSMALLINT_S" ("SQLVALUE") VALUES (-32768)}
s: => {INSERT INTO "REBSMALLINT_S" ("SQLVALUE") VALUES (-10)}
s: => {INSERT INTO "REBSMALLINT_S" ("SQLVALUE") VALUES (0)}
s: => {INSERT INTO "REBSMALLINT_S" ("SQLVALUE") VALUES (10)}
s: => {INSERT INTO "REBSMALLINT_S" ("SQLVALUE") VALUES (32767)}
s: => {SELECT "SQLVALUE" FROM "REBSMALLINT_S"}
[-32768 -10 0 10 32767]
[-32768 -10 0 10 32767]
QUERY MATCHED ORIGINAL DATA

s: => {DROP TABLE "REBINTEGER_S"}
name: => integer_s
sqltype: => "INT"
content: => [-2147483648 -10 0 10 2147483647]
s: => {CREATE TABLE "REBINTEGER_S" (ID integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) , "SQLVALUE" INT NOT NULL)}
s: => {INSERT INTO "REBINTEGER_S" ("SQLVALUE") VALUES (-2147483648)}
s: => {INSERT INTO "REBINTEGER_S" ("SQLVALUE") VALUES (-10)}
s: => {INSERT INTO "REBINTEGER_S" ("SQLVALUE") VALUES (0)}
s: => {INSERT INTO "REBINTEGER_S" ("SQLVALUE") VALUES (10)}
s: => {INSERT INTO "REBINTEGER_S" ("SQLVALUE") VALUES (2147483647)}
s: => {SELECT "SQLVALUE" FROM "REBINTEGER_S"}
[-2147483648 -10 0 10 2147483647]
[-2147483648 -10 0 10 2147483647]
QUERY MATCHED ORIGINAL DATA

s: => {DROP TABLE "REBBIGINT_S"}
name: => bigint_s
sqltype: => "BIGINT"
content: => [-9223372036854775808 -10 0 10 9223372036854775807]
s: => {CREATE TABLE "REBBIGINT_S" (ID integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) , "SQLVALUE" BIGINT NOT NULL)}
s: => {INSERT INTO "REBBIGINT_S" ("SQLVALUE") VALUES (-9223372036854775808)}
s: => {INSERT INTO "REBBIGINT_S" ("SQLVALUE") VALUES (-10)}
s: => {INSERT INTO "REBBIGINT_S" ("SQLVALUE") VALUES (0)}
s: => {INSERT INTO "REBBIGINT_S" ("SQLVALUE") VALUES (10)}
s: => {INSERT INTO "REBBIGINT_S" ("SQLVALUE") VALUES (9223372036854775807)}
s: => {SELECT "SQLVALUE" FROM "REBBIGINT_S"}
[-9223372036854775808 -10 0 10 9223372036854775807]
[-9223372036854775808 -10 0 10 9223372036854775807]
QUERY MATCHED ORIGINAL DATA

s: => {DROP TABLE "REBDOUBLE"}
name: => double
sqltype: => "DOUBLE PRECISION"
content: => [-3.4 -1.2 0.0 5.6 7.8]
s: => {CREATE TABLE "REBDOUBLE" (ID integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) , "SQLVALUE" DOUBLE PRECISION NOT NULL)}
s: => {INSERT INTO "REBDOUBLE" ("SQLVALUE") VALUES (-3.4)}
s: => {INSERT INTO "REBDOUBLE" ("SQLVALUE") VALUES (-1.2)}
s: => {INSERT INTO "REBDOUBLE" ("SQLVALUE") VALUES (0.0)}
s: => {INSERT INTO "REBDOUBLE" ("SQLVALUE") VALUES (5.6)}
s: => {INSERT INTO "REBDOUBLE" ("SQLVALUE") VALUES (7.8)}
s: => {SELECT "SQLVALUE" FROM "REBDOUBLE"}
[-3.4 -1.2 0.0 5.6 7.8]
[-3.4 -1.2 0.0 5.6 7.8]
QUERY MATCHED ORIGINAL DATA

s: => {DROP TABLE "REBFLOAT"}
name: => float
sqltype: => "FLOAT(20)"
content: => [-3.4 -1.2 0.0 5.6 7.8]
s: => {CREATE TABLE "REBFLOAT" (ID integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) , "SQLVALUE" FLOAT(20) NOT NULL)}
s: => {INSERT INTO "REBFLOAT" ("SQLVALUE") VALUES (-3.4)}
s: => {INSERT INTO "REBFLOAT" ("SQLVALUE") VALUES (-1.2)}
s: => {INSERT INTO "REBFLOAT" ("SQLVALUE") VALUES (0.0)}
s: => {INSERT INTO "REBFLOAT" ("SQLVALUE") VALUES (5.6)}
s: => {INSERT INTO "REBFLOAT" ("SQLVALUE") VALUES (7.8)}
s: => {SELECT "SQLVALUE" FROM "REBFLOAT"}
[-3.4000000953674316 -1.2000000476837158 0.0 5.599999904632568 7.800000190734863]
[-3.4 -1.2 0.0 5.6 7.8]
QUERY DID NOT MATCH ORIGINAL DATA

s: => {DROP TABLE "REBNUMERIC"}
name: => numeric
sqltype: => "NUMERIC(18,2)"
content: => [-3.4 -1.2 0.0 5.6 7.8]
s: => {CREATE TABLE "REBNUMERIC" (ID integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) , "SQLVALUE" NUMERIC(18,2) NOT NULL)}
s: => {INSERT INTO "REBNUMERIC" ("SQLVALUE") VALUES (-3.4)}
s: => {INSERT INTO "REBNUMERIC" ("SQLVALUE") VALUES (-1.2)}
s: => {INSERT INTO "REBNUMERIC" ("SQLVALUE") VALUES (0.0)}
s: => {INSERT INTO "REBNUMERIC" ("SQLVALUE") VALUES (5.6)}
s: => {INSERT INTO "REBNUMERIC" ("SQLVALUE") VALUES (7.8)}
s: => {SELECT "SQLVALUE" FROM "REBNUMERIC"}
[-3.4 -1.2 0.0 5.6 7.8]
[-3.4 -1.2 0.0 5.6 7.8]
QUERY MATCHED ORIGINAL DATA

s: => {DROP TABLE "REBDECIMAL"}
name: => decimal
sqltype: => "DECIMAL(3,2)"
content: => [-3.4 -1.2 0.0 5.6 7.8]
s: => {CREATE TABLE "REBDECIMAL" (ID integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) , "SQLVALUE" DECIMAL(3,2) NOT NULL)}
s: => {INSERT INTO "REBDECIMAL" ("SQLVALUE") VALUES (-3.4)}
s: => {INSERT INTO "REBDECIMAL" ("SQLVALUE") VALUES (-1.2)}
s: => {INSERT INTO "REBDECIMAL" ("SQLVALUE") VALUES (0.0)}
s: => {INSERT INTO "REBDECIMAL" ("SQLVALUE") VALUES (5.6)}
s: => {INSERT INTO "REBDECIMAL" ("SQLVALUE") VALUES (7.8)}
s: => {SELECT "SQLVALUE" FROM "REBDECIMAL"}
[-3.4 -1.2 0.0 5.6 7.8]
[-3.4 -1.2 0.0 5.6 7.8]
QUERY MATCHED ORIGINAL DATA

s: => {DROP TABLE "REBDATE"}
name: => date
sqltype: => "TIMESTAMP"
content: => [12-Dec-2012 21-Apr-1975]
s: => {CREATE TABLE "REBDATE" (ID integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) , "SQLVALUE" TIMESTAMP NOT NULL)}
value: => 12-Dec-2012
s: => {INSERT INTO "REBDATE" ("SQLVALUE") VALUES ('2012-12-12')}
value: => 21-Apr-1975
s: => {INSERT INTO "REBDATE" ("SQLVALUE") VALUES ('1975-4-21')}
s: => {SELECT "SQLVALUE" FROM "REBDATE"}
[12-Dec-2012/0:00 21-Apr-1975/0:00]
[12-Dec-2012 21-Apr-1975]
QUERY MATCHED ORIGINAL DATA

s: => {DROP TABLE "REBTIME"}
name: => time
sqltype: => "TIME"
content: => [10:00 11:01:12]
s: => {CREATE TABLE "REBTIME" (ID integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) , "SQLVALUE" TIME NOT NULL)}
s: => {INSERT INTO "REBTIME" ("SQLVALUE") VALUES ('10:00')}
s: => {INSERT INTO "REBTIME" ("SQLVALUE") VALUES ('11:01:12')}
s: => {SELECT "SQLVALUE" FROM "REBTIME"}
[10:00 11:01:12]
[10:00 11:01:12]
QUERY MATCHED ORIGINAL DATA

s: => {DROP TABLE "REBTIMESTAMP"}
name: => timestamp
sqltype: => "TIMESTAMP"
content: => [30-May-2017/14:23:08 12-Dec-2012]
s: => {CREATE TABLE "REBTIMESTAMP" (ID integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) , "SQLVALUE" TIMESTAMP NOT NULL)}
value: => 30-May-2017/14:23:08
s: => {INSERT INTO "REBTIMESTAMP" ("SQLVALUE") VALUES (TIMESTAMP_FORMAT('2017-5-30 14:23:08', 'YYYY-MM-DD HH24:MI:SS'))}
value: => 12-Dec-2012
s: => {INSERT INTO "REBTIMESTAMP" ("SQLVALUE") VALUES ('2012-12-12')}
s: => {SELECT "SQLVALUE" FROM "REBTIMESTAMP"}
[30-May-2017/14:23:08 12-Dec-2012/0:00]
[30-May-2017/14:23:08 12-Dec-2012]
QUERY MATCHED ORIGINAL DATA

s: => {DROP TABLE "REBCHAR"}
name: => char
sqltype: => "CHAR(3)"
content: => ["abc" "def" "ghi"]
s: => {CREATE TABLE "REBCHAR" (ID integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) , "SQLVALUE" CHAR(3) NOT NULL)}
s: => {INSERT INTO "REBCHAR" ("SQLVALUE") VALUES ('abc')}
s: => {INSERT INTO "REBCHAR" ("SQLVALUE") VALUES ('def')}
s: => {INSERT INTO "REBCHAR" ("SQLVALUE") VALUES ('ghi')}
s: => {SELECT "SQLVALUE" FROM "REBCHAR"}
["abc" "def" "ghi"]
["abc" "def" "ghi"]
QUERY MATCHED ORIGINAL DATA

s: => {DROP TABLE "REBVARCHAR"}
name: => varchar
sqltype: => "VARCHAR(10)"
content: => ["" "abc" "defgh" "jklmnopqrs"]
s: => {CREATE TABLE "REBVARCHAR" (ID integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) , "SQLVALUE" VARCHAR(10) NOT NULL)}
s: => {INSERT INTO "REBVARCHAR" ("SQLVALUE") VALUES ('')}
s: => {INSERT INTO "REBVARCHAR" ("SQLVALUE") VALUES ('abc')}
s: => {INSERT INTO "REBVARCHAR" ("SQLVALUE") VALUES ('defgh')}
s: => {INSERT INTO "REBVARCHAR" ("SQLVALUE") VALUES ('jklmnopqrs')}
s: => {SELECT "SQLVALUE" FROM "REBVARCHAR"}
["" "abc" "defgh" "jklmnopqrs"]
["" "abc" "defgh" "jklmnopqrs"]
QUERY MATCHED ORIGINAL DATA

s: => {DROP TABLE "REBNCHAR"}
name: => nchar
sqltype: => "NCHAR(3)"
content: => ["abc" "ταБ" "ghi"]
s: => {CREATE TABLE "REBNCHAR" (ID integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) , "SQLVALUE" NCHAR(3) NOT NULL)}
s: => {INSERT INTO "REBNCHAR" ("SQLVALUE") VALUES ('abc')}
s: => {INSERT INTO "REBNCHAR" ("SQLVALUE") VALUES ('ταБ')}
s: => {INSERT INTO "REBNCHAR" ("SQLVALUE") VALUES ('ghi')}
s: => {SELECT "SQLVALUE" FROM "REBNCHAR"}
["abc" "ταБ" "ghi"]
["abc" "ταБ" "ghi"]
QUERY MATCHED ORIGINAL DATA

s: => {DROP TABLE "REBNVARCHAR"}
name: => nvarchar
sqltype: => "VARCHAR(20)"
content: => ["" "abc" "ταБЬℓσ" "٩(●̮̮̃•̃)۶"]
s: => {CREATE TABLE "REBNVARCHAR" (ID integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) , "SQLVALUE" VARCHAR(20) NOT NULL)}
s: => {INSERT INTO "REBNVARCHAR" ("SQLVALUE") VALUES ('')}
s: => {INSERT INTO "REBNVARCHAR" ("SQLVALUE") VALUES ('abc')}
s: => {INSERT INTO "REBNVARCHAR" ("SQLVALUE") VALUES ('ταБЬℓσ')}
s: => {INSERT INTO "REBNVARCHAR" ("SQLVALUE") VALUES ('٩(●̮̮̃•̃)۶')}
s: => {SELECT "SQLVALUE" FROM "REBNVARCHAR"}
["" "abc" "ταБЬℓσ" "٩(●̮̮̃•̃)۶"]
["" "abc" "ταБЬℓσ" "٩(●̮̮̃•̃)۶"]
QUERY MATCHED ORIGINAL DATA

s: => {DROP TABLE "REBBINARY"}
name: => binary
sqltype: => "CHAR(3)"
content: => [#{000000} #{010203} #{FFFFFF}]
s: => {CREATE TABLE "REBBINARY" (ID integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) , "SQLVALUE" CHAR(3) NOT NULL)}
s: => {INSERT INTO "REBBINARY" ("SQLVALUE") VALUES (x'000000')}
s: => {INSERT INTO "REBBINARY" ("SQLVALUE") VALUES (x'010203')}
s: => {INSERT INTO "REBBINARY" ("SQLVALUE") VALUES (x'FFFFFF')}
s: => {SELECT "SQLVALUE" FROM "REBBINARY"}
["" "^A^B^C" "���"]
[#{000000} #{010203} #{FFFFFF}]
QUERY DID NOT MATCH ORIGINAL DATA

s: => {DROP TABLE "REBVARBINARY"}
name: => varbinary
sqltype: => "CHAR(10)"
content: => [#{} #{010203} #{DECAFBADCAFE}]
s: => {CREATE TABLE "REBVARBINARY" (ID integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) , "SQLVALUE" CHAR(10) NOT NULL)}
s: => {INSERT INTO "REBVARBINARY" ("SQLVALUE") VALUES (x'')}
s: => {INSERT INTO "REBVARBINARY" ("SQLVALUE") VALUES (x'010203')}
s: => {INSERT INTO "REBVARBINARY" ("SQLVALUE") VALUES (x'DECAFBADCAFE')}
s: => {SELECT "SQLVALUE" FROM "REBVARBINARY"}
["          " "^A^B^C       " "����    "]
[#{} #{010203} #{DECAFBADCAFE}]
QUERY DID NOT MATCH ORIGINAL DATA

s: => {DROP TABLE "REBBLOB"}
name: => blob
sqltype: => "BLOB(10)"
content: => [#{} #{010203} #{DECAFBADCAFE}]
s: => {CREATE TABLE "REBBLOB" (ID integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) , "SQLVALUE" BLOB(10) NOT NULL)}
insert blob data
s: => {INSERT INTO "REBBLOB" ("SQLVALUE") VALUES (blob(x''))}
s: => {INSERT INTO "REBBLOB" ("SQLVALUE") VALUES (blob(x'010203'))}
s: => {INSERT INTO "REBBLOB" ("SQLVALUE") VALUES (blob(x'DECAFBADCAFE'))}
s: => {SELECT "SQLVALUE" FROM "REBBLOB"}
** Error: Unknown column SQL_XXX type
** Where: insert-odbc --anonymous-- insert insert-sql for-each do catch either either --anonymous-- do trap either --anonymous--
** Near: reduce compose [(sql)] ??
** File: C:\Projects\ren-c\src\extensions\odbc\mod-odbc.c
** Line: 837
*** PG_Mem_Usage = 200 ***
Memory accounting imbalance: Rebol internally tracks how much
memory it uses to know when to garbage collect, etc.  For
some reason this accounting did not balance to zero on exit.
Run under Valgrind with --leak-check=full --track-origins=yes
to find out why this is happening.

gchiu · 2017-06-04 00:02:21 UTC

I didn’t test the datatypes I had removed from the Interbase and Firebird tests
So I guess I could put them back in again.

github.com

gchiu/Rebol3/blob/master/scripts/db2-test.reb

Rebol [
    Title: "ODBC Test Script for DB2 Express"
    Description: {
        This script does some basic table creation, assuming you have
        configured an ODBC connection with the DSN "Ren" that has a "test"
        database inside it.  Then it queries to make sure it can get the
        data back out.
    }
    version: 0.0.6
    date: 4-June-2017
    notes: { add a format-sql function
             avoid using reserved names for tables
             don't drop tables so we can examine in another DB browser
             use a insert-sql function to debug
             added format-sql/blob for DB2 and date formatting string
             DB2 - doesn't have unsigned numbers
             binaries need to be cast using HEX when selecting data
    }
]

This file has been truncated. show original

gchiu · 2017-06-04 01:16:40 UTC

DB2 update:

Failed on:
failures: => [“CHAR(1)” “FLOAT(20)” “CHAR(3)” “CHAR(10)” “BLOB(10)”]
Success on:
success: => [“SMALLINT” “SMALLINT” “INT” “BIGINT” “DOUBLE” “DOUBLE PRECISION” “NUMERIC(18,2)” “DECIMAL(3,2)” “TIMESTAMP” “TIME” “TIMESTAMP” “CHAR(3)” “VARCHAR(10)” “NCHAR(3)” “VARCHAR(20)”]

Float

[-3.4000000953674316 -1.2000000476837158 0.0 5.599999904632568 7.800000190734863]
[-3.4 -1.2 0.0 5.6 7.8]

VARBINARY

["20202020202020202020" "01020320202020202020" "DECAFBADCAFE20202020"]
[#{} #{010203} #{DECAFBADCAFE}]

CHAR(10)

["20202020202020202020" "01020320202020202020" "DECAFBADCAFE20202020"]
[#{} #{010203} #{DECAFBADCAFE}]

BLOB

["" "010203" "DECAFBADCAFE"]
[#{} #{010203} #{DECAFBADCAFE}]

gchiu · 2017-06-04 04:37:44 UTC

So, there may need to be different functions to insert and select data from an ODBC database or else the user has to learn about these variations.

gchiu · 2017-09-01 02:46:47 UTC

3 posts were split to a new topic: OBDC, Excel and memory allocation problems