Now you can use more than one datastore at a time.

Default = 0

Example Multi mode:

lds=multimode=1

lds=add=tomtom=select * from oitm

for=loop=1=<tomtom.rowcount>

  lds=setrow=tomtom=<loop>

next

Example Simple Modus

Only one Datastore can be defined / used.

lds=add=select * from oitm

for=loop=1=<lds.rowcount>

  lds=setrow=<loop>

next

declare=lds=ue_datastorevalues

lds=select top 5 "ItemCode" from "OITM"
 
Work with "select *"
Don't use the sign "*". This is very slow
Use follow syntax: (fieldfrom=<table>)

Example

select (fieldfrom="OITM") from "OITM"

if you want to change data, you must define thsi as updatetable datastore

see property updatetable

Select Version 2

The select command Version 2 is little faster and the SQL command "as" iw working

Example

lds=select "OITM"."ItemCode","OWHS"."WhsName" FROM "OITM" inner join "OWHS" (...)

create fields

oitm_itemcode and owhs_whscode

if you try to use as: it's not working

Example

lds.select "OITM"."ItemCode" as myfield,"OWHS"."WhsName" FROM "OITM" inner join "OWHS" (...)

create fields

oitm_myfield and owhs_whscode

With follow command the as command is working in correct way

lds.(v2)select "OITM"."ItemCode" as myfield,"OWHS"."WhsName" FROM "OITM" inner join "OWHS" (...)

this command create follow fields:

myfield and owhs_whscode

Import from beas service layer

Define get command via ODATA Syntax

Example

instance datastore lds

lds.bsl=Item?$select=ItemCode,ItemName

This import the complete Item List, only column ItemCode and ItemName

If you want to rename the fields, you can use the command "as"

instance datastore lds

lds.bsl=Item?$select=ItemCode,ItemName as MyItemName

In this case the second column has the name "MyItemName"

bsl and datastore

beas 9.3 PL 3

declare=lds=ue_datastorevalues

lds=execute beas_invhistory 'C11772'

If you use a existing DataStore name than it will be destroyed and created again automatically.

If you use a SQL Statement than the Datastore will be created dynamically. It contains all columns returned by the Statement.

If you provide a psr File than the DataStore will be created dynamically. It contains all columns which has been defined inside the psr.

top        ->    Beginning

bottom  ->    Ende

+x        ->    Current Pos + x

create new datastore with column filename and filesize and read the files from directory in the datastore.

set the new position and set the row in value "<value>"

value

-1    = error in argument

0     = nothing found

> 0  = the position

lds=find=substring(itemcode,1,1)='1'

Not in Multi Mode

declare=source=ue_datastorevalues

source=select top 1 * from "OITM"

declare=destination=destination

destination=updatetable=oitm_archive

destination=select top 1 * from "OITM_ARCHIVE"

source=rowscopy=oitm_archive

oitm_archive.update

// display result

tools=isql=select * from "OITM_ARCHIVE"

if you define the update properties, you can change and write back all data

Attention!

if you use this in wrong way, you can kill the data in current database.

You can set the primary key with beas 9.3 PL 1 and later.

First you must define the table and optional primary key

In HANA it's mandatory to define the primary key

declare=lds=ue_datastorevalues

// Table = MyColorTable

// Primary Key = Color

lds.updatetable=MyColorTable,Color

// read Data

lds=select="Color","Description" from "MyColorTable" where "Color"='red'

// change value

lds.item.description=Red Color

// save back

lds.update

If you have more then one primary key, you can define this with comma

example work order position

lds.updatetable=BEAS_FTPOS,BELNR_ID,BELPOS_ID

Different between update with and without primary key

Define only table

beas working with delete and insert

delete from [table] where [all fields have old value]

insert [all fields] into [table]

Special in HANA this is not working always correct.

Define Primary key

This is fast at runtime

beas working with update command and primary key in the where area

update [table] set [field]=<newvalue> where [primarykeyfield1]=<old value from primary key> and (second primary) and (third primary)

It's important to set the correct primary key!

You need to provide a unique ID which will be create by blobwrite.

Returnvalue is the files which was created.

Following Variables will be filled:

sys_blobreturn    "ok" oder Error

sys_blobfile        Filename or Error Message

The file will be created inside the temporary System Path.

Read will use the current Connection.

Error codes:

100              Not found

-1000          Create File-Error

                  Otherwise SQL Error Code

It will return a unique ID.

If you don't provide a unique ID than it will be generated automatically.

Write will use the current Connection.

Return Value:

sys_blobid              id:<unique ID>

                              or Error Message

                              e.g.. "file not exist"

                              or SQL-Error text

Supported Typ's:

psr  as PSR-File

xml  as XML-File in UTF8

csv  as CSV-File in UTF8

text  as Text-File in UTF8

excel, xls as Excel5-File in UTF8

clipboard copy Data in Clipboard in Text-Format

dbase3 as Dbase-File

dbstring: beas own data format

singlestring: as one string

Analyse the format of file and import this

is using file2datastore - checkfileformat

you can read excel and xml file. But it's better to use importfromxmlfile or importfromexcel

declare=lds=ue_datastorevalues

// import your file

lds.importfromfile=c:\temp\myimportfile.csv

// Check result

if <value> = <> 1  then

 messagebox=Import error: <value>

end if

for ll_loop=1 to <lds.rowcount>

 lds.setrow=<ll_loop>

// output the field names

 messagebox=<lds.myfield...>

next

destroy=lds

If this a text file, then the command analyse the format

with Priority 1. TAB, 2. Semilikon, 3. Comma

Check if Quotations marker is use

First line must be the header

Second and follow on the Data

Line Feed is allways CR_LF

Header Name: allowed only A-Z, a-z, 0-9 and Underline. All other signs convert to Underline.

return in variable "value"

ok = "1"

error =  "-1 " + error information

lds.saveas=dbstring=c:\temp\test.db

lds.importfromdbfile=c:\temp\test.db

Available in beas 9.2 PL 6. For older Versions use importfromfile=...

Attention: Excel must be installed.

the Program open Excel, load the file and save this in CSV format

Then the command read the CSV String

return in variable "value"

ok = "1"

error =  "-1 " + error information

Available in beas 9.3 PL 3. For older Versions use importfromfile=...

read and import xml file or all other file formats which supported by ue_file2datastore

this use the object ue_file2datastore

In second parameter you can define properties for ue_file2datastore, tab separated. See csv example

return in variable "value"

ok = "1"

error =  "-1 " + error information

Extended Datastore Import

example import XML File

Import CSV file, with Comma as delimiter

Don't use this command for new functions!
Use command "importfromfile"

File must be a tab-separated file (TXT). You must define the datastore before

// 1. Create Datastore

lds=select space(20) as field1,space(20) as field2 where 1=2

// 2. import from Textfile

lds=importtextfile==2

Return values ["value"]:

-2  Empty file or input data does not match

    number of columns or required column type

-3  Invalid argument

-4  Invalid input

-5  Could not open the file

-6  Could not close the file

-7  Error reading the text

-8  Unsupported file name suffix
   (must be *.txt, *.csv, *.dbf or *.xml)

-10  Unsupported dBase file format
    (not version 3)

-11  XML Parsing Error;
    XML parser libraries not found or XML not
    well formed

-12  XML Template does not exist or does not
    match the DataWindow

Don't use this command for new functions!
Use command "importfromfile"

this command import allways all rows.

if <separator> is defined then first row is header and mandatory.

If separator is not defined then default separtor is "comma-separated"

declare=lds=ue_datastorevalues

// make where-field with 1=-1, otherwise you´ve a empty row

lds=select space(20) as field1,space(20) as field2, space(20) as field3 from "BEAS_DUMMY" where 1=-1

// Import: insert all rows

lds=importcsvfile=test.csv

setvar=ls_test=

for ll_loop=1 to <lds.rowcount>

 lds=setrow=<ll_loop>

 setvar=ls_test=<ls_test>row <ll_loop> <lds.field1> <cr_lf>

next

messagebox=<ls_test>

<separator> parameter is available from version 9.2 PL4