JBS and SQL

JBS deliver different solution to work with SQL Commands

 

Overview

Type

Description

let result=sql("select field from table where rule");

Return only one Variable. Very fast

let result=sql

   ("select field,field2,field3... from table where rule");

Return object with all fields. Only one row, but fast

Name of properties: value1,value2 or names defined with "into"
Note: Use this, if you need less properties, only one row in fast way.

let result=SQLStatement.execute

   ("select field,field2,field3... from table where rule");

Return resultSet Object and save the result in a datastore

Use this, if you need more then one row as result

Allow to update properties back to database

let resuult=sql.select (String);

Execute the string

let result=sql.select ...sql command;

Execute sql statement directly

 

Simple way: One field, one Record

let result=sql("select \"ItemName\" from \"OITM\" where \"ItemCode\"='RM' ");
alert(result);

 

sql is a global function and allow to different solution

sql("SqlString");
sql(SqlString);
sql.select ...;

 

The second variant is special in HANA simple to use

let result=sql(select "ItemName" from "OITM" where "ItemCode"='RM');

or

let result=sql.select "ItemName" from "OITM" where "ItemCode"='RM';

 

the global function sql use always the current company connection.

this variant can return only one record and only one value

 

Return more then one row (resultSet)

It is possible to return more then one row

in this case the result is a resultSet object.

let result=SQLStatement.execute("select \"ItemCode\",\"ItemName\" from \"OITM\" );

Now you have the complete result list in the object "result"

Same for procedures

let result=SQLStatement.execute({exec MyProc(:a)});

More see SQLStatement.execute

 

Working with arguments

The simple SQL can return always only one row. Result is an object.

Arguments (Variables) start with double point ":"

In follow example you can see, how simple it's to use

let a="RM";
let result=sql.select "ItemName" from "OITM" where "ItemCode"=:a;
alert(result);

 

The function convert the variable ":a" automatically in N'RM'

You don't need to convert this in correct format. The function convert this always to correct SQL Format

It's allowed to use operators. For this you must set the argument in brackets

 

let a='R';
let b='M';
let result=sql(select "ItemName" from "OITM" where "ItemCode"=:(a+b));
alert(result);

 

 

Simple SQL: Return more then one value

If you want to return more then one value, but only one record, you can use the SQLStatement.select () function.

 

let result=SQLStatement.select "FrgnName","ItemName" from "OITM" where "ItemCode"='RM';

alert(result.value1+" "+result.value2);

 

The SQLStatement object use the SQLConnection from current company, if nothing other defined.

It can return only one record and return the result as type object

The result is in value1, value2 and so on.

 

Follow syntax allowed

resultSet=SQLStatement.[select command];
resultSet=SQLStatement.select ("[select command]");

 

Example

let a="RM";
let result=SQLStatement.select("select \"ItemName\" from \"OITM\" where \"ItemCode\"=:a");
alert(result.value1);

 

or sort variant

let a="RM";
alert(SQLStatement.select(select "ItemName" from "OITM" where "ItemCode"=:a").value1);

 

It is possible to define the result names with command "INTO"

 

let a="RM";
let result=SQLStatement.select "FrgnName","ItemName" into frgnName,itemName from "OITM" where "ItemCode"=:a;
alert(result.frgnName+" "+result.itemName);

 

It's possible to use reference from SQLStatement

let s=SQLStatement;
alert(s.select("select \"ItemName\" from \"OITM\" where \"ItemCode\"='RM'").value1);

 

Error handling

In SQL error case the SQL function send an error

You can control this with try/catch. The SQL Error is inside SQLConnection.sqlErrText

 

Example:

 

let result;
try {
result=SQLStatement.select ("\"FrgnNdame\" /*<- this is unknown field */,\"ItemName\" into frgName,itemName from \"OITM\" where \"ItemCode\"=:a;");
alert(result.frgnName+" "+result.itemName);
}
catch (re)
{
alert(SQLConnection.sqlErrText);
return ;
}

 

insert, delete or update command

Follow different script variants

sql.insert ...
sql.execute("insert ..");
sqlStatement.execute(insert);

 

same for delete and update

insert command return always the error code.

Example:

sql.delete from MyTable where MyColumn='a'

 

Object structure from JBS SQL

 

JBS SQL Collection allow to connect to every ODBC connection

 

jbsqlconstruct

 

Default connection is the database from object company.SQLConnection

sqlStatement=company.SQLConnection

But it's possible to connect to every other ODBC based Database.