https://gitlab.synchro.net/main/sbbs/-/merge_requests/538#note_7208

I would like to get @Ragnarok's input on this. e.g. Can we eliminate the 'stmt' property?

---
þ Synchronet þ Vertrauen þ Home of Synchronet þ [vert/cvs/bbs].synchro.net

https://gitlab.synchro.net/main/sbbs/-/merge_requests/538#note_7209

This MR is related to issue #118

---
þ Synchronet þ Vertrauen þ Home of Synchronet þ [vert/cvs/bbs].synchro.net

https://gitlab.synchro.net/main/sbbs/-/merge_requests/538#note_7211

I left a message on his BBS to come take a look.

---
þ Synchronet þ Vertrauen þ Home of Synchronet þ [vert/cvs/bbs].synchro.net

https://gitlab.synchro.net/main/sbbs/-/merge_requests/538#note_7216

I don't remember exactly, I think it was because I had first used SQLite2 and understood that I should use it.
Then, when I switched to SQLite3, it remained in the code.
But it was just a proof of concept that ultimately worked. But I didn't continue using it for much longer.
I'm very glad that this piece of code can be useful.

---
þ Synchronet þ Vertrauen þ Home of Synchronet þ [vert/cvs/bbs].synchro.net

https://gitlab.synchro.net/main/sbbs/-/merge_requests/538#note_7217

I just review the code, I think that stmt is not needed... because the sql string can be passed as parameter to exec() directly.

---
þ Synchronet þ Vertrauen þ Home of Synchronet þ [vert/cvs/bbs].synchro.net

https://gitlab.synchro.net/main/sbbs/-/merge_requests/538#note_7217

I just review the code, I think that stmt is not needed... because the sql string can be passed as parameter to exec() directly.

I think I had put it to have both forms/options.
The first, prepare the stmt and just call exec().
And the second, pass the SQL string as a parameter.
Therefore, it is redundant.

---
þ Synchronet þ Vertrauen þ Home of Synchronet þ [vert/cvs/bbs].synchro.net

https://gitlab.synchro.net/main/sbbs/-/merge_requests/538#note_7218

I believe the SQLite documentation indicates that using direct exec is more optimal for queries that do not return results (inset/update/delete/create table/etc.)
and using prepare/step/finalize for queries that return data using "select"

Perhaps that was what the idea of ​​implementing both forms of use at that time was based on.

---
ï¿­ Synchronet ï¿­ Vertrauen ï¿­ Home of Synchronet ï¿­ [vert/cvs/bbs].synchro.net

https://gitlab.synchro.net/main/sbbs/-/merge_requests/538#note_7219

So you're saying that if we want to use PREPARE then we'd be better off using db.stmt?
I was wondering exactly how we would construct a prepare then execute.

---
þ Synchronet þ Vertrauen þ Home of Synchronet þ [vert/cvs/bbs].synchro.net

https://gitlab.synchro.net/main/sbbs/-/merge_requests/538#note_7220

see this example (from chatgpt sorry)
for INSERT/CREATE TABLE it-s use exec method
and for SELECT use prepare

Other libraries I remember, such as using MySQL for PHP, had db.exec() for queries that didn't return results, and db.query() for those that did return rows and iterate through them.


```c++
#include <stdio.h>
#include <sqlite3.h>

int main() {
sqlite3 *db;
char *errMsg = 0;
int rc;

// Abrir (o crear) la base de datos
rc = sqlite3_open("ejemplo.db", &db);
if (rc) {
fprintf(stderr, "No se puede abrir la base de datos: %s\n", sqlite3_errmsg(db));
return 1;
} else {
printf("Base de datos abierta exitosamente\n");
}

// Crear tabla
const char *sqlCreateTable = "CREATE TABLE IF NOT EXISTS personas (id INTEGER PRIMARY KEY, nombre TEXT);";
rc = sqlite3_exec(db, sqlCreateTable, 0, 0, &errMsg);
if (rc != SQLITE_OK) {
fprintf(stderr, "Error al crear tabla: %s\n", errMsg);
sqlite3_free(errMsg);
}

// Insertar datos
const char *sqlInsert = "INSERT INTO personas (nombre) VALUES ('Juan'), ('Ana');";
rc = sqlite3_exec(db, sqlInsert, 0, 0, &errMsg);
if (rc != SQLITE_OK) {
fprintf(stderr, "Error al insertar datos: %s\n", errMsg);
sqlite3_free(errMsg);
}

// Consultar datos
const char *sqlSelect = "SELECT id, nombre FROM personas;";
sqlite3_stmt *stmt;

rc = sqlite3_prepare_v2(db, sqlSelect, -1, &stmt, 0);
if (rc == SQLITE_OK) {
while (sqlite3_step(stmt) == SQLITE_ROW) {
int id = sqlite3_column_int(stmt, 0);
const unsigned char *nombre = sqlite3_column_text(stmt, 1);
printf("ID: %d, Nombre: %s\n", id, nombre);
}
} else {
fprintf(stderr, "Error al preparar la consulta: %s\n", sqlite3_errmsg(db));
}

sqlite3_finalize(stmt);
sqlite3_close(db);
return 0;
}
```

---
þ Synchronet þ Vertrauen þ Home of Synchronet þ [vert/cvs/bbs].synchro.net

https://gitlab.synchro.net/main/sbbs/-/merge_requests/538#note_7221

Unfortunately, your English is better than my Spanish :) I don't see how this is a prepare though.
I would expect to see something like prepare("SELECT name,age from mytable WHERE age>?")
and that would be followed by execute(17)
in perl you'd construct your db connection with $dbh and then my $sth=dbh->prepare("whatever..."); and then $sth->execute(17); I believe if you just want a select you'd use do or go $dbh->do("TRUNCATE mytable");

Maybe we need to make the js interface into the library a little better?

---
þ Synchronet þ Vertrauen þ Home of Synchronet þ [vert/cvs/bbs].synchro.net

https://gitlab.synchro.net/main/sbbs/-/merge_requests/538#note_7222

No, my English is pretty bad, this is a assisted translation, ha!

Regarding the interface, it's true... Perl uses the same idea... do() for queries without results, and prepare() and execute() for queries that return rows.
I think this would work (perhaps without needing the prepare method as a separate method).

Could something like this work? What do you think? (from js side)

```javascript

var ret; //return object array
var row; //row object

db = new SQLite("/tmp/base1");
db. debug = false;
if (!db. open())
writeln ("i can't open it: " + db. errormsg);

if (db. exec("create table test (a int, b text)"))
writeln ("OK Create");
else
writeln("ops " + db.errormsg);

for (i=1;i<10;i++)
if(db.exec("insert into test values ​​(" + i + ",'hello')"))
writeln("OK inserted ");
else
writeln("ops " + db.errormsg);

writeln("querying data:");

if (ret = db.query("select a,b from test")) {
for (row in ret) {
writeln (ret[row].a + " " + ret[row].b);
}
} else {
writeln("ops: " + db.errormsg);
}

writeln("deleting data:");

if (ret = db.exec("delete from test")) {
writeln("OK delete");
} else {
writeln("ops: " + db.errormsg);
}

```

---
ï¿­ Synchronet ï¿­ Vertrauen ï¿­ Home of Synchronet ï¿­ [vert/cvs/bbs].synchro.net

https://gitlab.synchro.net/main/sbbs/-/merge_requests/538#note_7222

No, my English is pretty bad, this is a assisted translation, ha!

Regarding the interface, it's true... Perl uses the same idea... do() for queries without results, and prepare() and execute() for queries that return rows.
I think this would work (perhaps without needing the prepare method as a separate method).

Just using exec() and query()

Could something like this work? What do you think? (from js side)

```javascript

var ret; //return object array
var row; //row object

db = new SQLite("/tmp/base1");
db. debug = false;
if (!db. open())
writeln ("i can't open it: " + db. errormsg);

if (db. exec("create table test (a int, b text)"))
writeln ("OK Create");
else
writeln("ops " + db.errormsg);

for (i=1;i<10;i++)
if(db.exec("insert into test values ​​(" + i + ",'hello')"))
writeln("OK inserted ");
else
writeln("ops " + db.errormsg);

writeln("querying data:");

if (ret = db.query("select a,b from test")) {
for (row in ret) {
writeln (ret[row].a + " " + ret[row].b);
}
} else {
writeln("ops: " + db.errormsg);
}

writeln("deleting data:");

if (ret = db.exec("delete from test")) {
writeln("OK delete");
} else {
writeln("ops: " + db.errormsg);
}

```

---
ï¿­ Synchronet ï¿­ Vertrauen ï¿­ Home of Synchronet ï¿­ [vert/cvs/bbs].synchro.net

Re: Inital commit of sqlite support.
By: Fernando Toledo to GitLab note in main/sbbs on Fri May 09 2025 07:07 am

No, my English is pretty bad, this is a assisted translation, ha!

oh come on, you speak better english than me or a lot of english speaking people. btw, trump is changing the termonology to 'American'.
---
þ Synchronet þ ::: BBSES.info - free BBS services :::

https://gitlab.synchro.net/main/sbbs/-/merge_requests/538#note_7224

This doesn't demonstrate how to do a prepare though.

For example, in perl, we'd do something like

```
my $dbh = DBI->connect("dbi:SQLite:dbname=/sbbs/data/mydatabase.db", "", "", {
RaiseError => 1,
AutoCommit => 1,
}) or die $DBI::errstr;
my $try = $dbh->prepare("UPDATE queue SET retry=retry+1 WHERE site=?");
my $reset = $dbh->do("UPDATE queue SET retry=0");
my $comp = $dbh->prepare("UPDATE queue SET complete=1 WHERE site=?");


$try->execute("endofthelinebbs.com");
$comp->execute("vert.synchro.net");
$reset->execute();
```

So db.query would be the same as $dbh->do

So do we need to create db.prepare ?

I'm really not sure where to go with this.

---
þ Synchronet þ Vertrauen þ Home of Synchronet þ [vert/cvs/bbs].synchro.net

https://gitlab.synchro.net/main/sbbs/-/merge_requests/538#note_7225

What I'm trying to say is that from JavaScript, just use exec() or query() as needed, without needing to do a prepare.

Because the prepare is done directly within the C code.

So both methods in JS receive the string with the SQL as parameters.
The difference is that exec() can return true/false (or the number of rows affected) and query() can return an array of result rows.

Each method must be implemented in the C code, for example.

```c++
static JSBool
js_sqlite_exec(JSContext *cx, uintN argc, jsval *arglist)
{
JSObject *obj = JS_THIS_OBJECT(cx, arglist);
jsval *argv = JS_ARGV(cx, arglist);
char *sql;
sqlite3 *db;
char *errmsg = NULL;
int rc;

if (!JS_ConvertArguments(cx, argc, argv, "s", &sql))
return JS_FALSE;

// Obtener la conexión a la base de datos desde el objeto JavaScript
db = JS_GetPrivate(cx, obj);
if (!db)
return JS_FALSE;

rc = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
if (rc != SQLITE_OK) {
JS_ReportError(cx, "Error al ejecutar SQL: %s", errmsg);
sqlite3_free(errmsg);
return JS_FALSE;
}

JS_SET_RVAL(cx, arglist, JSVAL_TRUE);
return JS_TRUE;
}
```
and query method

```c++
static JSBool
js_sqlite_query(JSContext *cx, uintN argc, jsval *arglist)
{
JSObject *obj = JS_THIS_OBJECT(cx, arglist);
jsval *argv = JS_ARGV(cx, arglist);
char *sql;
sqlite3 *db;
sqlite3_stmt *stmt;
int rc;

if (!JS_ConvertArguments(cx, argc, argv, "s", &sql))
return JS_FALSE;

// Obtener la conexión a la base de datos desde el objeto JavaScript
db = JS_GetPrivate(cx, obj);
if (!db)
return JS_FALSE;

rc = sqlite3_prepare_v2(db, sql, -1, &stmt, NULL);
if (rc != SQLITE_OK) {
JS_ReportError(cx, "Error al preparar la consulta: %s", sqlite3_errmsg(db));
return JS_FALSE;
}

JSObject *resultArray = JS_NewArrayObject(cx, 0, NULL);
int index = 0;

while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
int colCount = sqlite3_column_count(stmt);
JSObject *rowObj = JS_NewObject(cx, NULL, NULL, NULL);

for (int i = 0; i < colCount; i++) {
const char *colName = sqlite3_column_name(stmt, i);
const char *colValue = (const char *)sqlite3_column_text(stmt, i);
jsval val = STRING_TO_JSVAL(JS_NewStringCopyZ(cx, colValue ? colValue : ""));
JS_SetProperty(cx, rowObj, colName, &val);
}

jsval rowVal = OBJECT_TO_JSVAL(rowObj);
JS_SetElement(cx, resultArray, index++, &rowVal);
}

sqlite3_finalize(stmt);

if (rc != SQLITE_DONE) {
JS_ReportError(cx, "Error al ejecutar la consulta: %s", sqlite3_errmsg(db));
return JS_FALSE;
}

JS_SET_RVAL(cx, arglist, OBJECT_TO_JSVAL(resultArray));
return JS_TRUE;
}
```

---
ï¿­ Synchronet ï¿­ Vertrauen ï¿­ Home of Synchronet ï¿­ [vert/cvs/bbs].synchro.net

https://gitlab.synchro.net/main/sbbs/-/merge_requests/538#note_7226

Hello! Please check this commit at my sqlite branch

https://gitlab.synchro.net/Ragnarok/sbbs/-/commit/2e7de43846adfe741b5b55f3b8e20e3feae05da6

here is a test example to use (notice that stmt is not use anymore here)

```javsacript
var ret; //return object array
var row; //row object

db = new Sqlite("/tmp/base1");
db.debug = true;
if (!db.open())
writeln ("i cant open it: " + db.errormsg);

if(db.exec("create table test (a int , b text)"))
writeln ("OK Create");
else
writeln ("ops " + db.errormsg);

for (i=1;i<10;i++)
if(db.exec("insert into test values (" + i + ",'hola')"))
writeln ("OK inserted ");
else
writeln ("ops " + db.errormsg);

writeln ("queryng data:");

if (ret = db.query("select a,b from test")) {
for (row in ret) {
writeln (ret[row].a + " " + ret[row].b);
}
}
else {
writeln ("ops: " + db.errormsg);
}

writeln ("deleting data:");

if (ret = db.exec("delete from test")) {
writeln("OK delete");
}
else {
writeln ("ops: " + db.errormsg);
}

db.close();

```

---
þ Synchronet þ Vertrauen þ Home of Synchronet þ [vert/cvs/bbs].synchro.net

https://gitlab.synchro.net/main/sbbs/-/merge_requests/538#note_7226

Hello! Please check this commit at my sqlite branch

https://gitlab.synchro.net/Ragnarok/sbbs/-/commit/2e7de43846adfe741b5b55f3b8e20e3feae05da6

here is a test example to use (notice that stmt is not use anymore here)

```javascript
var ret; //return object array
var row; //row object

db = new Sqlite("/tmp/base1");
db.debug = true;
if (!db.open())
writeln ("i cant open it: " + db.errormsg);

if(db.exec("create table test (a int , b text)"))
writeln ("OK Create");
else
writeln ("ops " + db.errormsg);

for (i=1;i<10;i++)
if(db.exec("insert into test values (" + i + ",'hola')"))
writeln ("OK inserted ");
else
writeln ("ops " + db.errormsg);

writeln ("queryng data:");

if (ret = db.query("select a,b from test")) {
for (row in ret) {
writeln (ret[row].a + " " + ret[row].b);
}
}
else {
writeln ("ops: " + db.errormsg);
}

writeln ("deleting data:");

if (ret = db.exec("delete from test")) {
writeln("OK delete");
}
else {
writeln ("ops: " + db.errormsg);
}

db.close();

```

---
þ Synchronet þ Vertrauen þ Home of Synchronet þ [vert/cvs/bbs].synchro.net

https://gitlab.synchro.net/main/sbbs/-/merge_requests/538#note_7226

Hello! Please check this commit at my sqlite branch

https://gitlab.synchro.net/Ragnarok/sbbs/-/commit/2e7de43846adfe741b5b55f3b8e20e3feae05da6

It probably still needs better validations and verifications, but that's the idea I was trying to tell you.

Here is a js test example to use (notice that stmt is not use anymore here)

```javascript
var ret; //return object array
var row; //row object

db = new Sqlite("/tmp/base1");
db.debug = true;
if (!db.open())
writeln ("i cant open it: " + db.errormsg);

if(db.exec("create table test (a int , b text)"))
writeln ("OK Create");
else
writeln ("ops " + db.errormsg);

for (i=1;i<10;i++)
if(db.exec("insert into test values (" + i + ",'hola')"))
writeln ("OK inserted ");
else
writeln ("ops " + db.errormsg);

writeln ("queryng data:");

if (ret = db.query("select a,b from test")) {
for (row in ret) {
writeln (ret[row].a + " " + ret[row].b);
}
}
else {
writeln ("ops: " + db.errormsg);
}

writeln ("deleting data:");

if (ret = db.exec("delete from test")) {
writeln("OK delete");
}
else {
writeln ("ops: " + db.errormsg);
}

db.close();

```

---
þ Synchronet þ Vertrauen þ Home of Synchronet þ [vert/cvs/bbs].synchro.net

https://gitlab.synchro.net/main/sbbs/-/merge_requests/538#note_7228

Using exec() for insert/update/delete queries should be a little more optimal than query()

---
þ Synchronet þ Vertrauen þ Home of Synchronet þ [vert/cvs/bbs].synchro.net

https://gitlab.synchro.net/main/sbbs/-/merge_requests/538#note_7231

if 'stmt' is always passed as an argument to the methods, there's no need store it ('stmt' member) in the private structure and then have to worry about freeing it in the finalizer.

---
þ Synchronet þ Vertrauen þ Home of Synchronet þ [vert/cvs/bbs].synchro.net