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