INSERT

NAME

insert - Inserts into the database.

SYNOPSIS

insert (( [db:pointer,] table:string, snc:bool, user: integer, fields:string);

DESCRIPTION

This function is used to insert values in a table.

PARAMETERS

table

Specifies the table.

fields

Specifies the list of fields, separated by commas. Fields are quoted automatically by the system to protect against SQL injection.

snc

Updates fields sn_cdate, sn_mdate, sn_cuid, sn_muid

user

User id for fields sn_cuid, sn_muid
 

db

Specifies an alternate database pointer as returned from connected function

Starting from version 5.6.2 if db is undefined, it will fall back to the default database of the project.

RETURN

This function return an array with the following elements:

sql

Built SQL statement.

nbcols

Number of columns in result.

Offset

Offset calculated when page is specified. If not, the value is -1.

sqlerr

Error message if any.

uid

Last insert id.

sqlcode

SQL error code. 0 if no error.

EXAMPLES

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

res={_{
       a=sql("drop table IF EXISTS ? ;","sn_test1");
       a=sql("create table sn_test1 (uid int NOT NULL , firstname varchar(255), lastname varchar(255), PRIMARY KEY (uid");
       a=insert(table:"sn_test1",fields:{firstname:'pierre',lastname:'laplante'}); a.uid;
       a=select(tables:"sn_test1"); stringnify(sort:true,a.rows);
    }}. return res=0\[{"firstname":"pierre","lastname":"laplante","uid":"0"}\].

res={_{
       a=sql("drop table IF EXISTS ? ;","sn_test1");
       a=sql("create table sn_test1 (uid int NOT NULL AUTO_INCREMENT, firstname varchar(255), lastname varchar(255), PRIMARY KEY (uid");
       a=insert(table:"sn_test1",fields:{firstname:'pierre',lastname:'laplante'}); a.uid;
       a=insert(table:"sn_test1",fields:{firstname:'chantal',lastname:'bilodeau'}); a.uid;
       a=select(tables:"sn_test1"); stringnify(sort:true,a.rows);
    }}. return res=12\[{"firstname":"pierre","lastname":"laplante","uid":"1"},{"firstname":"chantal","lastname":"bilodeau","uid":"2"}\].

res={_{
       a=sql("drop table IF EXISTS ? ;","sn_test1");
       a=sql("create table sn_test1 (uid int NOT NULL AUTO_INCREMENT, firstname varchar(255), lastname varchar(255), PRIMARY KEY (uid");
       a=insert(table:"sn_test1",fields:{sfirstname:'pierre',lastname:'laplante'}); a.sqlerr;
    }}. return res=Unknown column 'sfirstname' in 'field list'.

res={_{
       a=sql("drop table IF EXISTS ? ;","sn_test1");
       a=sql("create table sn_test1 (uid int NOT NULL AUTO_INCREMENT, firstname varchar(255), lastname varchar(255), PRIMARY KEY (uid");
       b="l'ile";
       a=insert(table:"sn_test1",fields:{firstname:b,lastname:b}); a.sqlerr;
       a=select(tables:"sn_test1"); stringnify(sort:true,a.rows);
    }}. return res=\[{"firstname":"l'ile","lastname":"l'ile","uid":"1"}\].

AUTHOR

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