sqlca

beasscript-logo-small

Object for current sql database connection

Read every property and execute T-SQL commands, functions and procedures

 

Command

Read

Write

Description

sqlca.commit

 

x

execute sql commit command

sqlca.concat

x

 

For compatibility

 

HANA: ||, MSSQL: +

select "ItemCode"<sqlca.concat>"ItemName" where "ItemCode"='1111'

 

MSSQL:

select "ItemCode" + "ItemName" where "ItemCode"='1111'

HANA:

select "ItemCode" || "ItemName" where "ItemCode"='1111'

 

Information:

 

In beas script select/update/delete command you can use always || as concat character. The framework convert this automatically to "+", if MSSQL Server is running

 

Write for HANA and MSSQL:

select "ItemCode" || "ItemName" where "ItemCode"='1111'

The system convert this automatically to "ItemCode"+"ItemName" in MSSQL

With actual MSSQL Version you can use always Concat function, bcause it's supported since Version 2012

sqlca.datetime

x

 

Return the Datetime format for current Server

sqlca.dbname

x

 

Return name from current  database

sqlca.getwithnolock

x

 

This command allow to read from tables without check locks or without creating own locks

Only mssql. In Hana the command is not existing (return nothing)

Example:

select "ItemCode" from "OITM" <sqlca.withnolock> where "ItemCode" = '1111'

sqlca.isconnected

x

 

Return true, if database connection is active or false, if not

sqlca.result.x

x

 

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

 

Return values in Variables <sqlca.result.1>

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

 

 

Note:

In older version you can use <wert1>, <wert2> ....

messagebox=ItemCode: <wert1>, ItemName: <wert2>

Compatibility infomation: This values are working too in current beas versions

sqlca.servername

x

 

Return name of current database server name

sqlca.sqlcode

x

 

Return code of last sql statement. More see select command - error handling

sqlca.sqlerrtext

x

 

Return error text of last sql statement. More see select command - error handling

sqlca.sqlerver

x

 

Return type of SQL Server. Example MS-SQL or HANA.
Note: Please use <sqlca.ishana>  to check, if this a HANA server

sqlca.getdate

x

 

Return command for Today() command: MSSQL: today(), HANA: now()

select <sqlca.today> from "BEAS_DUMMY"

sqlca.udf.<fieldname>

x

 

in MSSQL and HANA the names of UDF fields are different

in MSSQL: U_[name]
in HANA: U_beas_[name]

 

With this placeholder you can return the correct name

Example: select "<sqlca.udf.beas_znr>"  from "OITM"

return in

MSSQL: select "U_znr" from "OITM"

HANA: select "U_beas_znr" from "OITM"

sqlca.uppercase

x

 

Return, if uppercase check is on (HANA=1, MSSQL: -1)

sqlca.userid

x

 

Return current user id, which connected to database

sqlca.version

x

 

Return name of database

sqlca=<all other>

 

x

execute direct this statements

example

execute myprocedure parameter1,parameter 2

sqlca=blobread=<id>

 

x

extract the file from database with this id and return Variable <sys_blobreturn> ("ok" or the errormessage) and <sys_blobfile>

or

blobread=<id>=

sqlca=blobwrite=<id>=<file>

 

x

save file with this id in the database

sql=blobwrite=<id>=<file>

or

blobwrite=<id>=<file>

sqlca=delete

 

x

execute sql delete command. More information see sqlca.select

sqlca=execute <procedure> into <variablelist>

 

x

execute the procedure and return the result in the variable list

if you work without "into" then beas return the result in "wert1",,,"wertx"

Example:

sql=execute beas_poollist '1','1','Y' into :myid

return the id from Poollist in Variable "myid"

More Info about "into" see select-command.

sqlca=function=<sqlfunction()>

 

x

create, execute and delelete a sql Function

sqlca=select

x

x

Variant 1: Simple format

 

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

Return values in Variables <sqlca.result.1>

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

Note:

Use always HANA Format

 

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>

 

hmtoggle_plus1        Target-List - into command

the target-list is between "into" and "from" - all keywords in lowercase.

Wrong:

select "ItemCode" INTO :ls_itemcode from ..

select 'hello' into ls_itemcode

Correct:

select "ItemCode" into :ls_itemcode from ..

 

 

use as separator a comma

select "ItemCode","ItemName" into ls_itemcode,ls_itemname from "OITM" where "ItemCode"=<itemcode,dbstring>

 

You can work in Powerbuilder-Syntax with ":" for the target

select "ItemCode","ItemName" into :ls_itemcode,:ls_itemname from ..

or without ":"

select "ItemCode","ItemName" into :ls_itemcode,:ls_itemname from ..

 

if you will format the result: Write the format after ":"

select sum("OnHand") into ls_onhand:#0 from "OITW" where itemcode=<ls_itemcode,dbstring>
select "ItemName" into ls_itemname:lower from "OITM" where itemcode=<ls_itemcode,dbstring>

 

you can use as target:

 

 

local variable

local string ls_itemname

select "ItemName" into ls_itemname from oitm where itemcode=<itemcode,dbstring>

Format: You can only use the Format after ":", if the source is type "string"

Example:

select sum("OnHand") into lc_stock:#0.00 from "OITW" where ...

is not working.

instance variable

instance string ls_itemname

select "ItemName" into ls_itemname from oitm where itemcode=<itemcode,dbstring>

Format: You can only use the Format, if the source is type "string". Siee local Variable

str_parm

select "ItemName" into str_parm.s_parm1 from oitm where itemcode=<itemcode,dbstring>

 

window variable

// without declare

select "ItemName" into ls_itemname from oitm where itemcode=<itemcode,dbstring>

column in dw_1

set in dw_1 the field "itemname"

select "ItemName" into itemname from oitm where itemcode=<itemcode,dbstring>

dw_1-Column

example: select stock and set this in dw_2 column "currentstock"

select sum("OnHand") into dw_2.item.correntstock from "OITW" where "OITW"."ItemCode"='ls_itemcode,dbstring>

with format: ..into itemcode:lower from ...

Only in the current row in dw_1. if you want a other row, use ..into dw_1.itemcode:[ll_row] from ...

dw_x in row y

you can define the row in :row:format

if you've no format, set :row:

 

example: select stock and set this in dw_2 column "currentstock" in row

for ll_loop=1 to <dw_2.rowcount>

 select sum("OnHand") into dw_2.item.correntstock:[ll_loop]: from "OITW" where &      

    "ItemCode"=<dw_2.item.itemcode:[ll_loop].value,dbstring>

next

 

Short form:

You can write: .. into dw_2.item.onhand.value:[row]:format from..

without row and format: .. into dw_2.item.onhand.value from ..

without item and value-keyword: .. into dw_2.onhand from ..

without item and value-keyword, with row: .. into dw_2.onhand:[row]:format from ..

or only format: .. into dw_2.itemname:left 20 from ..

 

 

 

Priority:

select search for target-Variable in

1. datawindow (columname or dw_x)

2. str_parm

3. local-Variable

4. instance-Variable

5. window-variable

 

Error handling

with <sqlca.sqlcode> and <sqlca.sqlerrtext> you can check last result

sqlca.select xxxxx

// Push information. We don't want to overwrite this by sub functions

setvar=ll_sqlcode=<sqlca.sqlcode>

if <ll_sqlcode> = 100 then

 messagebox=error$$Entry not found

end if

if <ll_sqlcode> = -1 then

 messagebox=error$$Sql Syntax error <sqlca.sqlerrtext>

end if

// continue script

 

Convert Arguments

Arguments must be convert in correct format of SQL Server

you can set the select-command in placeholder. Use {} as sub-placeholder

setvar=ls_itemname=<select "ItemName" from "OITM" where "ItemCode"={itemcode,dbstring}>

 

Results additional available in value

wert1,wert2 ....

select "ItemCode","ItemName" from "OITM" where "ItemCode"='1111'
messagebox=ItemCode: <wert1>, ItemName: <wert2>

 

You can execute SQL commands with

sql.select=a,b
sql=select a,b
sqlca.select=a,b
sqlca.select a,b
select=a,b
select a,b

 

Attention

use allways Format "dbstring" to convert the String. "dbnum" to convert in Number or "dbdate" to convert in date- SQL-Conform Format.

use follow format

Strings: dbstring

Numbers: num

DateTime: dbdate

 

Format dbstring

Use dbstring format for format string commands to SQL String format

Example 1:

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

 

Differences between MSSQL and HANA

Example

setvar=ls_itemcode=my"Item"
select .. where "ItemCode"=<itemcode,dbstring>

Convert - Result in MSSQL:        where ItemCode=N'my "Item"'

Convert - Result in HANA:            where "ItemCode=N'my "Item"'

 

Format dbdate

use dbdate to format dates in sql-string:  

select "kwid" from "BEAS_KALENDER" where "datum_id"=<mydatecolumn,dbdate>

 

Format num()

use format num(x) for decimal:

x=count of decimal numbers. In beas standard we use always 6 decimal places.

sql=update "MYTABLE" set "quantity"=<myquantity,dbnum>

 

 

 

Write allways in Hana-Format for follow commands

 

Follow functions are converted in beas-MSSQL

Use HANA-Syntax in MS - SQL

is converting in (MS SQL)

select 'A'||'B' ,3+5 from "BEAS_DUMMY"

select 'A'+'B',3+5 from "BEAS_DUMMY"

select IfNull("ItemCode") from "OITM"

Akternative: use coalesce

select isnull(itemcode) from OITM

select now() from "BEAS_DUMMY"

select getdate() from BEAS_DUMMY

select max(5) "ItemCode" from "OITM"

select top 5 ItemCode from OITM

select rpad(' ',10) as emptystring from "BEAS_DUMMY"

select replicate(' ',10) as emptystring from BEAS_DUMMY

select add_date(now(),5) from "BEAS_DUMMY"

select dbo.add_date(getdate(),5) from BEAS_DUMMY

(the function dbo.add_date use dateadd(xx,yy) )

select to_varchar(now(),'yyyy-mm-dd')

select dbo.to_varchar(getdate(),'yyyy-mm-dd')

(the function dbo.to_varchar use convert)

 

Alternativ commands for Single Source

MSSQL

hana

Alternativ

select isnull(a,b)

select ifnull(a,b)

select coalesce(a,b)

select 'hello'+'world'

select 'hello'||'world

no solution, because concat is not available in old mssql version

select space(50)

select rpad(50)

select cast('' as nvarchar(50))

 

Use follow commands in MSSQL-World

Info

Correct

Wrong

use Cast and not convert

select cast("OnHand" as varchar) as onHandAsVarchar from "OITW"

select convert(varchar(20),"OnHand") as onHandAsVarchar from "OITW"

use from "BEAS_DUMMY" if you've no reference to a table

select 1+2 from "BEAS_DUMMY"

select 1+2

use "||" if you add a string

but use "+" for adding numbers

select 'A'||'B',3+4 from "BEAS_DUMMY"

select 'A'+'B',3+4

don't use dateadd(x,y)

use add_date(), add_month(), add_year(),

select add_year(now(),5)

select dateadd(yy,getdate())

convert datetime use dbo.to_varchar(x,y)

select to_varchar(now(),'yyyy-mm-dd')

select cast (now() as varchar(10))

 

 

sqlca=update

 

x

execute sql  update command. More information see sqlca.select

 

More Result-Rows, Procedure

if you've more then one result-Line, then work with ue_datastorevalues

if you call a procedure then work with ue_datastorevalues