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

instance datastore lds
lds=select top 5 "ItemCode" from "OITM"

 
Work with "select *"
Do not use the sign "*". This is very slow
Use the following syntax: (fieldfrom=<table>)

Example:

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

if you want to change data, you must define it  as update table 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

"as" is not working

Example:

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

create fields

oitm_myfield and owhs_whscode

With the following command the "as" command is working in the correct way:

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

this command creates the following 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

declare=lds=ue_datastorevalues

lds=execute beas_invhistory 'C11772'

If you use an existing DataStore name, it will be destroyed and created again automatically.

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

If you provide a psr File, 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

instance datastore source
instance daastore destination
 
source=select top 1 * from "OITM"
 
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 improperly, you can kill the data in the current database.

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

First you must define the table and the optional primary key

In HANA it's mandatory to define the primary key

 
instance datastore lds
// 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 than one primary key, you can define this with a comma

Example work order position

lds.updatetable=BEAS_FTPOS,BELNR_ID,BELPOS_ID

Difference between update with and without primary key

Define only table

Beas works with delete and insert

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

insert [all fields] into [table]

Especially in HANA this is not always working correctly.

Define the 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

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

instance datastore lds
// 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

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

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