sqlca select

it is very simple to execute SQL commands in beas script

 

Variant 1: Simple format

sqlca.select "ItemCode","ItemName" from "OITM" where "ItemCode"='1111'

Return values in Variables <sqlca.result.1>

messagebox=ItemCode: <sqlca.result.1>, ItemName: <sqlca.result.2>

 

Write all SQL commands always in HANA Syntax.  More see internal SQL Runtime Converter

 

Variant 2: Working with "into" command

sqlca=select "values" into target-list from "table" where ..

Example

select "ItemCode","ItemName" into ls_itemname,ls_itemcode from "OITM" where "ItemCode"='1111'

Return values in Variables  <ls_itemcode>,<ls_itemname>

messagebox=ItemCode: <ls_itemcode>, ItemName: <ls_itemname>

 

In into variable you can define variables or datawindow dw_x

Example:

 

Save result directly in dw_1.item.itemname

select "ItemName" into dw_1.item.itemname from "OITM" where "ItemCode"=<dw_1.item.itemcode.value,dbstring>

 

More see sqlca into command

 

Error handling

For good scripts error handling is important. See sqlca error handling

 

Placeholders, Variables

if you use placeholder, you must convert this always in sql format

In follow example the placeholder dw_1.item.itemcode.value is converted in a SQL compatible string

select "ItemName" from "OITM" where "ItemCode"=<dw_1.item.itemcode.value,dbstring>

and execute in MSSQL:

select "ItemName" from "OITM" where "ItemCode"=N'FP'

 

Don't use

select "ItemName" from "OITM" where "ItemCode"='<dw_1.item.itemcode.value>'

Because this syntax don't support unicode and produce trouble with ' inside the sql command. Additional bad injection is possible

More see sqlca format placeholders

 

see

DataBase description

SQL Runtime Converter

sqlca into

sqlca error handling

sqlca format placeholders