title: “DB::SQL Function” date: 2024-08-09 author: “laplante@plcb.ca” version: “1.0.1”
db::sql — execute SQL statements and retrieve results
db::sql or sql(expression[, parameters, error: bool, next_row:bool, single: bool, want_colname:bool, want_array:bool, exec:bool, query:bool, tableName: string])
The db::sql function executes an SQL query and returns results depending on the parameters provided.
If you want to use a table name as in CREATE TABLE tableName you can use {tableName} and specify parameter tableName.
The parameter will be verified against the regular expression ^[a-zA-Z0-9_]+$.
sql(want_array:true, "select * from test1 where uid >= 3 and uid <= 5 order by uid desc");
return
{"nb_columns":4,"nb_rows":3,"rows":[["5","ln5","fn5","5"],["4","ln4","fn4","4"],["3","ln3","fn3","3"]]}
want_colname
This will return an array of all column headers.
next_row
Used with the function next_row. This runs the SQL query and allows iteration with next_row to get subsequent rows.
exec
When true, the SQL query is executed but no data is returned.
query
When true, the SQL query is expected to return data.
If exec and query are not set, sql will guess the type of query by checking if the query contains show or select.
single
If true, the query returns only the first row in a map named row.
res={{
res := sql("show databases");
"col="; res.nb_columns; "\n";
"row="; res.nb_rows; "\n";
for i in res.rows {
if i.value.Database == "goweb" {
"database goweb\n";
}
}
}}.
return
res=col=1
row=3
database goweb
.
res={{
func insert(i) {
res := sql("INSERT INTO test1(lastname, firstname, age) VALUES(?,?,?)", "ln" + string(i), "fn" + string(i), i);
if i == 5 {
res;
}
}
res := sql("drop table if exists test1");
res;
res = sql("
CREATE TABLE test1 (
uid int NOT NULL AUTO_INCREMENT,
lastname varchar(255) NOT NULL,
firstname varchar(255),
age int,
PRIMARY KEY (uid)
);
");
res;
nb := 100;
for i := 1; i<=nb; i++ {
insert(i);
}
for i := 1; i<=nb; i++ {
res = sql("select * from test1 where uid = ?", i);
if i == 5 {
res;
}
}
res = sql(want_array:true, "select * from test1 where uid >= 3 and uid <= 5 order by uid desc");
res;
}}.
return
res={"last_insert_id":0,"rows_affected":0}{"last_insert_id":0,"rows_affected":0}{"last_insert_id":5,"rows_affected":1}{"nb_columns":4,"nb_rows":1,"rows":[{"age":"5","firstname":"fn5","lastname":"ln5","uid":"5"}]}{"nb_columns":4,"nb_rows":3,"rows":[["5","ln5","fn5","5"],["4","ln4","fn4","4"],["3","ln3","fn3","3"]]}.
res={{
sql("sue &* fromm eijdij");
}}.
return
.*error executing sql.*
res={{
res := sql(error:err, "show tables");
"err = "; err; "\n";
res := sql(error:err, "sue &* fromm eijdij");
"err = "; err; "\n";
}}.
return
res=err =
err = db::sql : error executing sql 'sue &* fromm eijdij': Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'sue &* fromm eijdij' at line 1
.
res={{
res := sql(want_array:true, want_colname: true, "select * from test1 where uid >= 3 and uid <= 5 order by uid desc");
res;
}}.
return
res={"colname":["uid","lastname","firstname","age"],"nb_columns":4,"nb_rows":3,"rows":[["5","ln5","fn5","5"],["4","ln4","fn4","4"],["3","ln3","fn3","3"]]}.
res={{
func f(n) {
n; "------------------\n";
res := sql(next_row:true, "select * from test1 order by uid asc limit " + string(n));
i := 1;
for next_row(res) && i < 3 {
res; "\n";
i++;
}
close_row(res);
return;
}
f(5);
f(6);
}}.
return
res=5------------------
{"nb_columns":4,"rows":[{"age":"1","firstname":"fn1","lastname":"ln1","uid":"1"}]}
{"nb_columns":4,"rows":[{"age":"2","firstname":"fn2","lastname":"ln2","uid":"2"}]}
6------------------
{"nb_columns":4,"rows":[{"age":"1","firstname":"fn1","lastname":"ln1","uid":"1"}]}
{"nb_columns":4,"rows":[{"age":"2","firstname":"fn2","lastname":"ln2","uid":"2"}]}
.
res={{
func f(n) {
n; "------------------\n";
res := sql(next_row:true, "select * from test1 order by uid asc limit " + string(n));
i := 1;
for next_row(res) && i < 3 {
res; "\n";
i++;
}
return;
}
f(5);
f(6);
}}.
return
res=5------------------
{"nb_columns":4,"rows":[{"age":"1","firstname":"fn1","lastname":"ln1","uid":"1"}]}
{"nb_columns":4,"rows":[{"age":"2","firstname":"fn2","lastname":"ln2","uid":"2"}]}
6------------------
{"nb_columns":4,"rows":[{"age":"1","firstname":"fn1","lastname":"ln1","uid":"1"}]}
{"nb_columns":4,"rows":[{"age":"2","firstname":"fn2","lastname":"ln2","uid":"2"}]}
.
res={{
func f(n) {
n; "------------------\n";
res := sql(want_colname: true, next_row:true, "select * from test1 order by uid asc limit " + string(n));
i := 1;
for next_row(res) && i < 3 {
res; "\n";
i++;
}
return;
}
f(5);
f(6);
}}.
return
res=5------------------
{"colname":["uid","lastname","firstname","age"],"nb_columns":4,"rows":[{"age":"1","firstname":"fn1","lastname":"ln1","uid":"1"}]}
{"colname":["uid","lastname","firstname","age"],"nb_columns":4,"rows":[{"age":"2","firstname":"fn2","lastname":"ln2","uid":"2"}]}
6------------------
{"colname":["uid","lastname","firstname","age"],"nb_columns":4,"rows":[{"age":"1","firstname":"fn1","lastname":"ln1","uid":"1"}]}
{"colname":["uid","lastname","firstname","age"],"nb_columns":4,"rows":[{"age":"2","firstname":"fn2","lastname":"ln2","uid":"2"}]}
.
res={{
func f(n) {
n; "------------------\n";
res := sql(want_colname: true, want_array:true, next_row:true, "select * from test1 order by uid asc limit " + string(n));
i := 1;
for next_row(res) && i < 3 {
res; "\n";
i++;
}
return;
}
f(5);
f(6);
}}.
return
res=5------------------
{"colname":["uid","lastname","firstname","age"],"nb_columns":4,"rows":[["1","ln1","fn1","1"]]}
{"colname":["uid","lastname","firstname","age"],"nb_columns":4,"rows":[["2","ln2","fn2","2"]]}
6------------------
{"colname":["uid","lastname","firstname","age"],"nb_columns":4,"rows":[["1","ln1","fn1","1"]]}
{"colname":["uid","lastname","firstname","age"],"nb_columns":4,"rows":[["2","ln2","fn2","2"]]}
.
laplante@plcb.ca
tableName parameter