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"}.

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

sql

SQL