sqlca select

Contents

SQL commands can be easily executed in BeasScript.

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>

Always write all SQL commands in HANA Syntax. For more information 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>

For more information see sqlca into command

Error handling

Decent scripts error handling is important. See sqlca error handling

Placeholders, Variables

if you use placeholders, you must always convert this in SQL format.

In the following 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'

NOTE: Do not use:

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

Because this syntax does not support unicode and produces trouble with ' inside the SQL command. Additional bad injection is possible

see