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