Hello,
having recently gotten my hands on Stata 17, I wanted to try if it is possible to work with sqlite databases (basically, a db embedded in a single file) via the new JDBC functionality.
Using the jar file from https://github.com/xerial/sqlite-jdbc, I am able to connect to a file, list tables, insert data, but loading data from a table fails with r(683) "failed to load resultset column".
I was thus wondering if maybe somebody has an idea what the problem is, or has been able to use JDBC with sqlite.
Example code (insert simulated data into temporary file and try to load them):
having recently gotten my hands on Stata 17, I wanted to try if it is possible to work with sqlite databases (basically, a db embedded in a single file) via the new JDBC functionality.
Using the jar file from https://github.com/xerial/sqlite-jdbc, I am able to connect to a file, list tables, insert data, but loading data from a table fails with r(683) "failed to load resultset column".
I was thus wondering if maybe somebody has an idea what the problem is, or has been able to use JDBC with sqlite.
Example code (insert simulated data into temporary file and try to load them):
Code:
clear
set obs 1000
gen double x = rnormal()
gen str1 y = cond(runiform()<0.5, "A", "B")
// jar source: https://github.com/xerial/sqlite-jdbc/releases/download/3.36.0.3/sqlite-jdbc-3.36.0.3.jar
local jar "sqlite-jdbc-3.36.0.3.jar"
local driverc "org.sqlite.JDBC"
tempfile tmpdb
local url "jdbc:sqlite:`tmpdb'"
local user "user"
local pass "password"
jdbc connect, jar("`jar'") driverclass("`driverc'") url("`url'") user("`user'") password("`pass'")
jdbc exec "CREATE TABLE tbl1 (x REAL, y TEXT);"
jdbc insert, table("tbl1")
jdbc showtables
jdbc describe "tbl1"
clear
jdbc load, table("tbl1")
Comment