SQLite

[Arne Vajhøj]

SQLite has been mentioned a lot recently.

Here is a little demo.

C:\Work\sqlite>del test.db

C:\Work\sqlite>type cre.sql
CREATE TABLE multi (
id INTEGER PRIMARY KEY,
msg VARCHAR(50)
);

C:\Work\sqlite>sqlite3 test.db 0<cre.sql

C:\Work\sqlite>type test.sql
INSERT INTO multi(msg) VALUES('Hi from CLI tool on Windows');
SELECT id,msg FROM multi;

C:\Work\sqlite>sqlite3 test.db 0<test.sql
1|Hi from CLI tool on Windows

C:\Work\sqlite>type test.py
import sqlite3

con = sqlite3.connect('test.db')
c = con.cursor()
c.execute('INSERT INTO multi(msg) VALUES(?)', ('Hi from Python on
Windows',))
c.execute('SELECT id,msg FROM multi')
for row in c.fetchall():
print('%d %s' % (row[0], row[1]))
con.commit()
con.close()

C:\Work\sqlite>python test.py
1 Hi from CLI tool on Windows
2 Hi from Python on Windows

C:\Work\sqlite>type test.c
#include <stdio.h>
#include <string.h>
#include <stdlib.h>

#include "sqlite3.h"

static char *DB = "test.db";
static char *INS = "INSERT INTO multi(msg) VALUES(?)";
static char *SEL = "SELECT id,msg FROM multi";
static char *TXT = "Hi from C on Windows";

int main()
{
sqlite3 *con;
sqlite3_stmt *ins, *sel;
int stat;
stat = sqlite3_open(DB, &con);
if(stat)
{
printf("Error in open: %s\n", sqlite3_errmsg(con));
exit(1);
}
stat = sqlite3_prepare(con, INS, strlen(INS), &ins, NULL);
if(stat)
{
printf("Error in prepare: %s\n", sqlite3_errmsg(con));
exit(1);
}
stat = sqlite3_bind_text(ins, 1, TXT, strlen(TXT), NULL);
if(stat)
{
printf("Error in bind: %s\n", sqlite3_errmsg(con));
exit(1);
}
stat = sqlite3_step(ins);
if(stat != SQLITE_DONE)
{
printf("Error in step: %s\n", sqlite3_errmsg(con));
exit(1);
}
sqlite3_finalize(ins);
stat = sqlite3_prepare(con, SEL, strlen(SEL), &sel, NULL);
if(stat)
{
printf("Error in prepare: %s\n", sqlite3_errmsg(con));
exit(1);
}
while(sqlite3_step(sel) == SQLITE_ROW)
{
printf("%d %s\n", sqlite3_column_int(sel, 0),
sqlite3_column_text(sel, 1));
}
sqlite3_finalize(sel);
sqlite3_close(con);
return 0;
}

C:\Work\sqlite>gcc -IC:\DivNative\32bit\sqlite3 test.c
C:\DivNative\32bit\sqlite
3\sqlite3.c -o test.exe

C:\Work\sqlite>test
1 Hi from CLI tool on Windows
2 Hi from Python on Windows
3 Hi from C on Windows

C:\Work\sqlite>type test.pas
program test;

uses
SQLDB, SQLite3Conn;

var
con : TSQLite3Connection;
tx : TSQLTransaction;
ins, sel : TSQLQuery;

begin
con := TSQLite3Connection.Create(nil);
tx := TSQLTransaction.Create(con);
con.DatabaseName := 'test.db';
con.Transaction := tx;
con.Open;
tx.StartTransaction;
ins := TSQLQuery.Create(nil);
ins.DataBase := con;
ins.SQL.Text := 'INSERT INTO multi(msg) VALUES(:msg)';
ins.Params.ParamByName('msg').AsString := 'Hi from Pascal on Windows';
ins.ExecSQL;
if ins.RowsAffected <> 1 then begin
writeln('INSERT error');
halt;
end;
ins.Close;
ins.Free;
tx.Commit;
sel:= TSQLQuery.Create(nil);
sel.DataBase := con;
sel.SQL.Text := 'SELECT id,msg FROM multi';
sel.Open;
while not sel.EOF do begin
writeln(sel.FieldByName('id').AsInteger:1,'
',sel.FieldByName('msg').AsString);
sel.Next;
end;
sel.Close;
sel.Free;
tx.Free;
con.Close;
con.Free;
end.

C:\Work\sqlite>fpc test.pas
Free Pascal Compiler version 3.0.4 [2019/10/27] for x86_64
Copyright (c) 1993-2017 by Florian Klaempfl and others
Target OS: Win64 for x64
Compiling test.pas
Linking test.exe
43 lines compiled, 0.7 sec, 503552 bytes code, 16228 bytes data

C:\Work\sqlite>test
1 Hi from CLI tool on Windows
2 Hi from Python on Windows
3 Hi from C on Windows
4 Hi from Pascal on Windows

C:\Work\sqlite>type test.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Test {
private static final String CONURL = "jdbc:sqlite:test.db";
public static void main(String[] args) throws Exception {
Class.forName("org.sqlite.JDBC");
Connection con = DriverManager.getConnection(CONURL);
PreparedStatement ins = con.prepareStatement("INSERT INTO
multi(msg) VALUES(?)");
ins.setString(1, "Hi from Java on Windows");
ins.executeUpdate();
PreparedStatement sel = con.prepareStatement("SELECT id,msg
FROM multi");
ResultSet rs = sel.executeQuery();
while(rs.next()) {
System.out.printf("%d %s\n", rs.getInt(1), rs.getString(2));
}
con.close();
}
}

C:\Work\sqlite>javac Test.java

C:\Work\sqlite>java -cp .;C:\DivJava\sqlite-jdbc-3.31.1\target\* Test
1 Hi from CLI tool on Windows
2 Hi from Python on Windows
3 Hi from C on Windows
4 Hi from Pascal on Windows
5 Hi from Java on Windows

C:\Work\sqlite>type test.cs
using System;

using System.Data;
using System.Data.SQLite;

public class Test
{
public static void Main(string[] args)
{
using(SQLiteConnection con = new SQLiteConnection("Data
Source=test.db"))
{
con.Open();
using(SQLiteCommand ins = new SQLiteCommand("INSERT INTO
multi(msg) VALUES(@msg)", con))
{
ins.Parameters.Add("@msg", DbType.String, 50);
ins.Parameters["@msg"].Value = "Hi from C# on Windows";
ins.ExecuteNonQuery();
}
using(SQLiteCommand sel = new SQLiteCommand("SELECT id,msg
FROM multi", con))
{
using(SQLiteDataReader rdr = sel.ExecuteReader())
{
while(rdr.Read())
{
Console.WriteLine("{0} {1}", rdr["id"],
rdr["msg"]);
}
}
}
}
}
}

C:\Work\sqlite>csc /r:System.Data.SQLite.dll test.cs
Microsoft (R) Visual C# Compiler version 3.5.0-beta4-20153-05 (20b9af91)
Copyright (C) Microsoft Corporation. All rights reserved.

C:\Work\sqlite>test
1 Hi from CLI tool on Windows
2 Hi from Python on Windows
3 Hi from C on Windows
4 Hi from Pascal on Windows
5 Hi from Java on Windows
6 Hi from C# on Windows

C:\Work\sqlite>sqlite3 test.db 0<test.sql
1|Hi from CLI tool on Windows
2|Hi from Python on Windows
3|Hi from C on Windows
4|Hi from Pascal on Windows
5|Hi from Java on Windows
6|Hi from C# on Windows
7|Hi from CLI tool on Windows

C:\Work\sqlite>ftp -s:upload.dat 192.168.0.10
Connected to 192.168.0.10.
220 arne1 HGFTP server V3.2-4 ready.
User (192.168.0.10:(none)):
331 Username "arne" Okay, need password.

230-User "ARNE" logged in, 17-JUN-2020 14:09:23 -0400, proceed.
230 Connection closes if idle for 5 min.
ftp> cd sqlite
250 Current directory /disk2/arne/sqlite, completed.
ftp> bin
200 TYPE I Okay.
ftp> put test.db
200 Port 192,168,0,140,235,32 Okay.
150 Binary Store of TEST.DB;3 Started; Opening data connection. (0 bytes)
226 File transfer Okay; Closing data connection.
ftp: 8192 bytes sent in 0.00Seconds 8192000.00Kbytes/sec.
ftp> quit
221 Service closing control connection.



$ type test.sql
INSERT INTO multi(msg) VALUES('Hi from CLI tool on VMS');
SELECT id,msg FROM multi;
$ sqlite3 test.db -init test.sql
1|Hi from CLI tool on Windows
2|Hi from Python on Windows
3|Hi from C on Windows
4|Hi from Pascal on Windows
5|Hi from Java on Windows
6|Hi from C# on Windows
7|Hi from CLI tool on Windows
8|Hi from CLI tool on VMS
$ type test.py
import sqlite3

con = sqlite3.connect('test.db')
c = con.cursor()
c.execute('INSERT INTO multi(msg) VALUES(?)', ('Hi from Python on VMS',))
c.execute('SELECT id,msg FROM multi')
for row in c.fetchall():
print('%d %s' % (row[0], row[1]))
con.commit()
con.close()
$ python test.py
1 Hi from CLI tool on Windows
2 Hi from Python on Windows
3 Hi from C on Windows
4 Hi from Pascal on Windows
5 Hi from Java on Windows
6 Hi from C# on Windows
7 Hi from CLI tool on Windows
8 Hi from CLI tool on VMS
9 Hi from Python on VMS
$ type test.c
#include <stdio.h>
#include <string.h>
#include <stdlib.h>

#include "sqlite3.h"

static char *DB = "test.db";
static char *INS = "INSERT INTO multi(msg) VALUES(?)";
static char *SEL = "SELECT id,msg FROM multi";
static char *TXT = "Hi from C on VMS";

int main()
{
sqlite3 *con;
sqlite3_stmt *ins, *sel;
int stat;
stat = sqlite3_open(DB, &con);
if(stat)
{
printf("Error in open: %s\n", sqlite3_errmsg(con));
exit(1);
}
stat = sqlite3_prepare(con, INS, strlen(INS), &ins, NULL);
if(stat)
{
printf("Error in prepare: %s\n", sqlite3_errmsg(con));
exit(1);
}
stat = sqlite3_bind_text(ins, 1, TXT, strlen(TXT), NULL);
if(stat)
{
printf("Error in bind: %s\n", sqlite3_errmsg(con));
exit(1);
}
stat = sqlite3_step(ins);
if(stat != SQLITE_DONE)
{
printf("Error in step: %s\n", sqlite3_errmsg(con));
exit(1);
}
sqlite3_finalize(ins);
stat = sqlite3_prepare(con, SEL, strlen(SEL), &sel, NULL);
if(stat)
{
printf("Error in prepare: %s\n", sqlite3_errmsg(con));
exit(1);
}
while(sqlite3_step(sel) == SQLITE_ROW)
{
printf("%d %s\n", sqlite3_column_int(sel, 0),
sqlite3_column_text(sel, 1));
}
sqlite3_finalize(sel);
sqlite3_close(con);
return 0;
}
$ cc/include=sqlite3_include: test.c
$ link test+sys$input/opt
sqlite3shr/share
$
$ run test
1 Hi from CLI tool on Windows
2 Hi from Python on Windows
3 Hi from C on Windows
4 Hi from Pascal on Windows
5 Hi from Java on Windows
6 Hi from C# on Windows
7 Hi from CLI tool on Windows
8 Hi from CLI tool on VMS
9 Hi from Python on VMS
10 Hi from C on VMS
$ type test.pas
[inherit('sqlite', 'psqlite')]
program test(input, output);

var
con : sqlite_ptr;
ins, sel : sqlite_stmt_ptr;

begin
con := psqlite_open('test.db');
if con = 0 then begin
writeln('Error in open: ', psqlite_errmsg(con));
halt;
end;
ins := psqlite_prepare(con, 'INSERT INTO multi(msg) VALUES(?)');
if ins = 0 then begin
writeln('Error in prepare: ', psqlite_errmsg(con));
halt;
end;
if not psqlite_bind_text(ins, 1, 'Hi from Pascal on VMS') then begin
writeln('Error in bind: ', psqlite_errmsg(con));
halt;
end;
if not psqlite_step_nonquery(ins) then begin
writeln('Error in step: ', psqlite_errmsg(con));
halt;
end;
sel := psqlite_prepare(con, 'SELECT id,msg FROM multi');
if sel = 0 then begin
writeln('Error in prepare: ', psqlite_errmsg(con));
halt;
end;
while psqlite_step_query(sel) do begin
writeln(psqlite_column_int(sel, 0):1, ' ',
psqlite_column_text(sel, 1));
end;
psqlite_finalize(sel);
psqlite_close(con);
end.
$ pas test
$ link test + psqlite/opt + sqlite/opt
psqlite
sqlite
sqlite3shr/share
$ run test
1 Hi from CLI tool on Windows
2 Hi from Python on Windows
3 Hi from C on Windows
4 Hi from Pascal on Windows
5 Hi from Java on Windows
6 Hi from C# on Windows
7 Hi from CLI tool on Windows
8 Hi from CLI tool on VMS
9 Hi from Python on VMS
10 Hi from C on VMS
11 Hi from Pascal on VMS
$ sqlite3 test.db -init test.sql
1|Hi from CLI tool on Windows
2|Hi from Python on Windows
3|Hi from C on Windows
4|Hi from Pascal on Windows
5|Hi from Java on Windows
6|Hi from C# on Windows
7|Hi from CLI tool on Windows
8|Hi from CLI tool on VMS
9|Hi from Python on VMS
10|Hi from C on VMS
11|Hi from Pascal on VMS
12|Hi from CLI tool on VMS

Arne

[Arne Vajhøj]

Post by Arne Vajhøj
1|Hi from CLI tool on Windows
2|Hi from Python on Windows
3|Hi from C on Windows
4|Hi from Pascal on Windows
5|Hi from Java on Windows
6|Hi from C# on Windows
7|Hi from CLI tool on Windows
8|Hi from CLI tool on VMS
9|Hi from Python on VMS
10|Hi from C on VMS
11|Hi from Pascal on VMS
12|Hi from CLI tool on VMS

And in case someone wonders.

VMS Pascal is done using a very thin Pascal
wrapper around the C API. I will make it available
as soon as I get it zipped up.

There is no Java example on VMS, because newer JDBC
drivers does not work on VMS (they are type 2 not type 4)
and a very old (type 4) driver did not work either
for reasons I could not figure out.

Arne

[hb]

Post by Arne Vajhøj
There is no Java example on VMS, because newer JDBC
drivers does not work on VMS (they are type 2 not type 4)
and a very old (type 4) driver did not work either
for reasons I could not figure out.

Which one?

$ sqlite3 test.db
SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
sqlite> CREATE TABLE multi (id INTEGER PRIMARY KEY, msg VARCHAR(50));
sqlite> INSERT INTO multi(msg) VALUES('Hi from CLI tool on Linux');
sqlite> SELECT id,msg FROM multi;
1|Hi from CLI tool on Linux
sqlite>
$ scp test.db eisner:

$ java -version
java version "1.5.0"
Java(TM) 2 Runtime Environment, Standard Edition
Fast VM (build 1.5.0-8, build J2SDK.v.1.5.0:02/08/2012-17:12, native
threads, jit_150)
$
$! changed "Java on Windows" to "Java on VMS"
$ javac Test.java
$ java -cp ".:sqlite-jdbc-3.7.17-vms.jar" Test
1 Hi from CLI tool on Linux
2 Hi from Java on VMS
$

Yes, old. Didn't try with newer versions, sqlite3 and/or java.

[Arne Vajhøj]

Post by hb

Post by Arne Vajhøj
There is no Java example on VMS, because newer JDBC
drivers does not work on VMS (they are type 2 not type 4)
and a very old (type 4) driver did not work either
for reasons I could not figure out.

Which one?
$ sqlite3 test.db
SQLite version 3.30.1 2019-10-10 20:19:45
Enter ".help" for usage hints.
sqlite> CREATE TABLE multi (id INTEGER PRIMARY KEY, msg VARCHAR(50));
sqlite> INSERT INTO multi(msg) VALUES('Hi from CLI tool on Linux');
sqlite> SELECT id,msg FROM multi;
1|Hi from CLI tool on Linux
sqlite>
$ java -version
java version "1.5.0"
Java(TM) 2 Runtime Environment, Standard Edition
Fast VM (build 1.5.0-8, build J2SDK.v.1.5.0:02/08/2012-17:12, native
threads, jit_150)
$
$! changed "Java on Windows" to "Java on VMS"
$ javac Test.java
$ java -cp ".:sqlite-jdbc-3.7.17-vms.jar" Test
1 Hi from CLI tool on Linux
2 Hi from Java on VMS
$
Yes, old. Didn't try with newer versions, sqlite3 and/or java.

I had tried:
* STANDARD 3.7.x, which as expected did not work, because it does
not have the VMS native code
* 3.5.9 and the original Zentus with "-Dsqlite.purejava=true" gave
me a funky out of memory error

Using the VMS 3.7.x version (with the VMS native code) then everything
worked.

:-)

$ type test.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Test {
private static final String CONURL = "jdbc:sqlite:test.db";
public static void main(String[] args) throws Exception {
Class.forName("org.sqlite.JDBC");
Connection con = DriverManager.getConnection(CONURL);
PreparedStatement ins = con.prepareStatement("INSERT INTO
multi(msg) VALUES(?)");
ins.setString(1, "Hi from Java on VMS");
ins.executeUpdate();
PreparedStatement sel = con.prepareStatement("SELECT id,msg
FROM multi");
ResultSet rs = sel.executeQuery();
while(rs.next()) {
System.out.printf("%d %s\n", rs.getInt(1), rs.getString(2));
}
con.close();
}
}
$ javac "Test.java"
$ java -cp .:sqlite-jdbc-3_14_1-vms.jar "Test"
1 Hi from CLI tool on Windows
2 Hi from Python on Windows
3 Hi from C on Windows
4 Hi from Pascal on Windows
5 Hi from Java on Windows
6 Hi from C# on Windows
7 Hi from CLI tool on Windows
8 Hi from CLI tool on VMS
9 Hi from Python on VMS
10 Hi from C on VMS
11 Hi from Pascal on VMS
12 Hi from Java on VMS
$ sqlite3 test.db -init test.sql
1|Hi from CLI tool on Windows
2|Hi from Python on Windows
3|Hi from C on Windows
4|Hi from Pascal on Windows
5|Hi from Java on Windows
6|Hi from C# on Windows
7|Hi from CLI tool on Windows
8|Hi from CLI tool on VMS
9|Hi from Python on VMS
10|Hi from C on VMS
11|Hi from Pascal on VMS
12|Hi from Java on VMS
13|Hi from CLI tool on VMS

Thanks for the help.

Arne

[Arne Vajhøj]

Post by Arne Vajhøj
Using the VMS 3.7.x version (with the VMS native code) then everything
worked.

After making sure that SYS$SCRATCH was an ODS-5 disk with appropriate
logicals set to allow for multi dot temporary file name.

Apropos another thread here.

Arne

[Arne Vajhøj]

Post by Arne Vajhøj

Post by Arne Vajhøj
1|Hi from CLI tool on Windows
2|Hi from Python on Windows
3|Hi from C on Windows
4|Hi from Pascal on Windows
5|Hi from Java on Windows
6|Hi from C# on Windows
7|Hi from CLI tool on Windows
8|Hi from CLI tool on VMS
9|Hi from Python on VMS
10|Hi from C on VMS
11|Hi from Pascal on VMS
12|Hi from CLI tool on VMS

VMS Pascal is done using a very thin Pascal
wrapper around the C API. I will make it available
as soon as I get it zipped up.

https://www.vajhoej.dk/arne/opensource/vms/ and get vmspsqlite*.zip

Arne

[o***@gmail.com]

Post by Arne Vajhøj
SQLite has been mentioned a lot recently.
Here is a little demo.
C:\Work\sqlite>del test.db
C:\Work\sqlite>type cre.sql
CREATE TABLE multi (
id INTEGER PRIMARY KEY,
msg VARCHAR(50)
);

SQLite is loose about data types, the column definition sets an affinity for values in that column but doesn't enforce what type value is stored there. For
compatibility with other SQL dialects, VARCHAR(n) is mapped to TEXT affinity.
A column defined with "INTEGER PRIMARY KEY" is treated specially and made an alias for the normally hidden rowid column every table (normally) has. The rowid
column is AUTOINCREMENT, which is why the inserts give the id column unique
values.