SQL(1e)

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.

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.

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>