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