Go to main content

sql

This function is used to do a sql request.

SQL

NAME

sql - This function is used to do a sql (structure query language) request.

SYNOPSIS

sql(( [db:pointer,] "select * from table where name = '?'", name);

sql(single:true,wantarray:boolean,wantheader:boolean,noarg:true,prefix:true,error:false,"select * from table where name = '" .+ name .+ "'");

DESCRIPTION

This function is used to do an sql request.

In the first form, the ? are replaced by the last parameter. If there are more than one input parameters, they can be passed as an array or as additional parameters at the end.

In the second form, no parameters are allowed.

Remember to quote your input otherwise the query may fail.

This function can also be used in a callback.

PARAMETERS

noarg
Doesn’t use parameters
single
Returns only the first row. In that case, the row is not an array. It's a context unless "wantarray" is true. In that case, it only returns one array.
wantheader
If this is set, sql will return a variable named headers with the list of name of headers in an array.
db
Specifies alternate database pointer as return from connect function
Starting from version 5.6.2 if db is undefined, it will fall back to the default database of the project.
prefix
Prefixes the name field with the tablename followed by _ followed by the fieldname. Useful with a sql join.
error
If "error" is false and there is an SQL error, there will be no error. The error will be returned in the variable.
 
errvar
Specify a variable to get all errors from sql. Will return errcode and errmesg. Valid from verson 5.172.
wantarray
Instead of returning named parameters, asks the system to return the array value.

RETURN

error
True or false.
nbrows
Number of rows returned
nbcols
Number of columns returned.
sqlcode
Sql code returned
sql
Initial sql request
sqlerr
Messages if there is an sql error.
affrow
Return the number of rows affected. This is only return for certain sql operation like delete, update and Insert.
rows
Rows return. This can be an array or an associative array.

EXAMPLES

Note: In the followings examples, the _ between the { should be removed to make it work.

Basic examples:

a = sql("select lg from sn_languages where active = 'yes'");
a.rows;
a = sql(single:true,"select lg from sn_languages where active = 'yes'");
a.rows;
a = sql(single:true,"select lg from sn_languages where active = '?'", 'yes');
a.rows;
a = sql(wantarray:true,single:true,"select lg from sn_languages where active = 'yes'");
a.rows;

return res=[{"lg":"fr"},{"lg":"en"}]{"lg":"fr"}["fr"]

res={_{
            a=sql(wantheader:true,"desc sn_tests");
                for i in a.headers do i; endfor
}}. return res=FieldTypeNullKeyDefaultExtra.
More examples:
res={_{a=sql("drop database ?;");a.error;}} return .*Missing.*
res={_{a=sql("drop database ?;", "sn_test");a.error;}} return .*Access.*

res={_{sql(noarg:true,"show databasesx;");}} return Execution error 6 : line 1 : Sql error.*
res={_{a=sql(error:false,noarg:true,"show databasesx;");a.error}} return res=true.
res={_{a=sql(error:false,noarg:true,"show databasesx;");a.nbrows}} return res=0.
res={_{a=sql(error:false,noarg:true,"show databasesx;");a.nbcols}} return res=0.
res={_{a=sql(error:false,noarg:true,"show databasesx;");a.sqlcode}} return res=1.
res={_{a=sql(error:false,noarg:true,"show databasesx;");a.sql}} return res=show databasesx;.
res={_{a=sql(error:false,noarg:true,"show databasesx;");a.sqlerr}} return res=You have an error.*
res={_{a=sql(error:false,noarg:true,"show databasesx;");a.rows}} return res=\[].
res={_{a=sql(error:false,noarg:true,wantarray:true,"show databasesx;");a.rows}} return res=\[].

res={_{a=sql(noarg:true,"show databases;");a.error;}} return res=false.
res={_{a=sql(noarg:true,"show databases;");a.nbrows;}} return res=3.
res={_{a=sql(noarg:true,"show databases;");a.nbcols;}} return res=1.
res={_{a=sql(noarg:true,"show databases;");a.sqlcode;}} return res=0.
res={_{a=sql(noarg:true,"show databases;");a.sql;}} return res=show databases;.
res={_{a=sql(noarg:true,"show databases;");a.sqlerr;}} return res=.
res={_{a=sql(noarg:true,"show databases;");a.rows[0].Database;}} return res=information_schema.
res={_{a=sql(noarg:true,prefix:true,"show databases;");a.rows[2].SCHEMATA_Database;}} return res=v5.

res={_{a=sql(noarg:true,wantarray:true,"show databases;");a.error;}} return res=false.
res={_{a=sql(noarg:true,wantarray:true,"show databases;");a.nbrows;}} return res=3.
res={_{a=sql(noarg:true,wantarray:true,"show databases;");a.nbcols;}} return res=1.
res={_{a=sql(noarg:true,wantarray:true,"show databases;");a.sqlcode;}} return res=0.
res={_{a=sql(noarg:true,wantarray:true,"show databases;");a.sql;}} return res=show databases;.
res={_{a=sql(noarg:true,wantarray:true,"show databases;");a.sqlerr;}} return res=.
res={_{a=sql(noarg:true,wantarray:true,"show databases;");a.rows[0][0];}} return res=information_schema.
res={_{a=sql(noarg:true,wantarray:true,"show databases;");a.rows[1][0];}} return res=test.
res={_{a=sql(noarg:true,wantarray:true,"show databases;");a.rows[2][0];}} return res=v5.
res={_{a=sql(noarg:true,wantarray:true,"show databases;");a.rows[3][0];}} return res=.

res={_{for i sql(noarg:true,error:false,"show databasesx") do i; endfor; i.error;}} return res=true.
res={_{for i sql(noarg:true,error:false,"show databasesx") do i; endfor; i.nbrows;}} return res=0.
res={_{for i sql(noarg:true,error:false,"show databasesx") do i; endfor; i.nbcols;}} return res=0.
res={_{for i sql(noarg:true,error:false,"show databasesx") do i; endfor; i.sqlcode;}} return res=1.
res={_{for i sql(noarg:true,error:false,"show databasesx") do i; endfor; i.sql;}} return res=show databasesx.
res={_{for i sql(noarg:true,error:false,"show databasesx") do i; endfor; i.rows;}} return res=\{}.
res={_{for i sql(noarg:true,error:false,"show databasesx") do i; endfor; i.sqlerr;}} return res=You have an error.*
res={_{for i sql(noarg:true,error:true,"show databasesx") do i; endfor; i.sqlerr;}} return .*You have an error.*
res={_{for i sql(noarg:true,"show databases") do i.rows.Database; endfor; i.nbrows;}} return res=information_schematestv53.
res={_{for i sql(noarg:true,"show databases") do i.nbcols; endfor; i.nbrows;}} return res=1113.
res={_{for i sql(noarg:true,wantarray:true,"show databases") sn_nb; ":"; i.rows[0]; endfor; i.nbrows;}} return res=0:information_schema1:test2:v53.
res={_{for i sql(noarg:true,"show databases") do sn_nb; ":"; i.rows.Database; endfor; i.nbrows;}} return res=0:information_schema1:test2:v53.
res={_{for i sql(noarg:true,"show databases") do sn_nb; ":"; i.rows.Database; '{'; for j sql(noarg:true,"show databases") do sn_nb; ":"; j.rows.Database; endfor; '}'; endfor; i.nbrows;}} return res=0:information_schema{0:information_schema1:test2:v5}1:test{0:information_schema1:test2:v5}2:v5{0:information_schema1:test2:v5}3.
res={_{for i sql(noarg:true,prefix:true,"show databases") do sn_nb; ":"; i.rows.SCHEMATA_Database; endfor; i.nbrows;}} return res=0:information_schema1:test2:v53.
res={_{for i sql("select * from sn_tests where firstname = '?'","l'ile") do i.rows; endfor}}. return res={"uid":"1","lastname":"stewart","firstname":"l'ile"}.

Example with more than one inputs:

    arr = [ "sn_test1", "firstname", "fv", "lastname", "lv"];
    void sql("update ? set ?='?',?='?'", arr);
    a = sql(single:true,"select lg from sn_languages where active = '?' and id = ?", 'yes', 1);

OTHERWISE instructions

If the loop is not used the otherwise instructions will be executed.

Example with an otherwise:
res={_{for i sql("select * from sn_tests where firstname = '?'","l'ile") do i.rows; otherwise "Oups!"; endfor}}. return res={"uid":"1","lastname":"stewart","firstname":"l'ile"}.

SEE ALSO

{{ include("includes/database.sn") }}

AUTHOR

Written by Pierre Laplante and Caroline Laplante, <laplante@sednove.com>

MODIFICATIONS

1.0 2014-09-09 21:24:14 laplante@sednove.com

1.1 2015-11-16 laplante@sednove.com Add example with an array

1.2 2017-06-27 xueyuanp@sednove.com update to adapt the newest syntax and consolidate all the examples into one place

Edit

© 2022 extenso Inc. All rights reserved.