Database Operations

Use these functions for handling database input and output.


New features in BaanERP
In BaanERP, the functions db.curr(), db.first(), db.last(), db.next(), db.prev(),db.gt(), db.ge(), db.lt() and db.le() are implemented internally as SELECT statements. They are supported for backward compatibility only. In new applications, use queries instead.
In BaanERP, certain 4GL functions have Data Access Layer (DAL) equivalents. The following table lists these functions and their DAL equivalents. 


4GL function                                              DAL function

on.main.table()                                            with.object.set.do()

on.old.occ()                                                 with.old.object.values.do()

set.input.error()                                          dal.set.error.message()

                                                                    return(DALHOOKERROR)

skip.io()                                                      dal.set.error.message()

                                                                    return(DALHOOKERROR)

abort.io()                                                    dal.set.error.message()

                                                                    return(DALHOOKERROR)

db.update()                                               dal.update()

db.delete()                                                dal.destroy()

db.insert()                                                 dal.new()

If a DAL exists for a particular table, it is preferable to use the DAL functions. The older functions access the database directly. When you make changes to the database with these functions, logic integrity checks programmed in the DAL are not executed. When you make changes to the database with the DAL functions, the relevant DAL hooks are executed automatically. This ensures the logic integrity of the database. 


Table names and declarations
The naming syntax for tables, record buffers, and fields is:
 table tppmmmxxx                | table declaration
 rcd.tppmmmxxx                  | record buffer of table
 ppmmmxxx.ffffffff                 | logical field of table


where t stands for table, pp is the package code, mmm is the module code, xxx is the table number, and ffffffff  is a field name.
If a table is used in a script, it must be declared with the statement:
table tppmmmxxx
Declaration of a table implies declaration of all its fields and its record buffer. These do not need to be declared separately.
There are no functions for opening or closing a table. A table is automatically opened the first time it is accessed. It is automatically closed when the program ends.

List of Database operations:

• commit.transaction()
• abort.transaction()
• activate.search()
• db.bind()
• db.insert()
• db.update()
• db.eq()
• db.delete()
• db.drop.table()
• db.unbind()
• db.create.table()
• on.main.table()
• db.retry.point()
• db.set.to.default()
• db.permission()
• db.prev()

Read More

4GL Event Section

A 4GL script can contain one or more of the following types of sections:

• Program sections 
• Form sections 
• Group sections 
• Choice sections 
• Field sections 
• Zoom sections 
• Main table sections 

With the exception of program sections, each section consists of a main section and one or more subsections. A main section indicates the object for which the programmed actions must be executed. A subsection specifies when the actions must be executed. Program sections consist of a main section only.
If you do not include a main section immediately before a subsection, the subsection is assigned to the previous main section in the script. For example:


form.1: | main section 
init.form: | subsection executed for form 1
...
...
before.form: | subsection executed for form 1
...
...
after.form: | subsection executed for form 1


...
The following rules apply to the ordering of sections within a script:

• The declaration section must be the first section in the script.
• The functions section must be the last section in the script. 
• Other sections can occur in any order, but the preferred order is: program sections, form sections, group sections, choice sections, field sections, zoom sections, main table sections.
• Do not mix sections of different types. For example, do not define a choice section, followed by a field section, and then a choice section again. Exceptions are the declaration and functions sections, which must be the first and last sections respectively in the script.

Variables declared in the declaration section are global variables that you can use in other sections. Variables declared within any other section are local variables that you can use in that section only.

Read More

String operations

Use these functions to manipulate strings in various ways. For example:

• to check whether a string contains a long value or spaces
• to check the number of characters or bytes in a string
• to load numeric values stored in a string into variables of type long or double
• to convert a number contained in a string to its numeric representation
• to convert an integer or floating point number to a string
• to convert a string to upper case or lower case
• to search for a substring within a string
• to strip leading and trailing spaces from strings
• to align strings
• to store a long or double in a string.

String operations:

• concat$
• isspace
• len()
• lval() 
• val()
• pos()
• rpos()
• str$()
• strip$()
• shiftc$()
• shiftl$()
• shiftr$()

Read More

Enumerate and Set constant

In the data dictionary you can define database table fields and domains of type enumerate or set. An enumerate or set domain consists of a number of constants. In the program script (and other places in the data dictionary), a symbolic name is used. The symbolic name is the domain name, followed by a period [.] followed by the name of the constant. Variables of type set can also consist of a combination of set values.
Examples of enumerate constants


DOMAIN color box_color                 | color is an enumerate domain
| with two constants, green and red
box_color = color.green
box_color = color.red


Examples of set constants


DOMAIN feature cf                                      | feature is a domain of type set with
| the constants bold, reverse, underlined
cf = feature.bold
cf = cf + feature.reverse                                 | add constant, cf contains
| bold and reverse
cf = cf - feature.bold                                      | subtract constant
etol( cf )                                                      | returns the combined number of the
                                                                         | current value, here reverse.
A special constant of type enumerate or set is EMPTY. This indicates an empty set or an enumerate which is not defined. Internally, it has the value 0.


Related Post:

• ask.enum()
• enum.descr$()
• etol()
• ltoe()

Read More

BAAN SQL

You use Baan SQL (Structured Query Language) to retrieve information from the database. With Baan SQL's SELECT statement, you can retrieve any selection of data from the database, regardless of the number of tables in which the data is stored. The system does search through all records sequentially. It bases its search on a number of conditions defined in the SELECT statement. 
The SELECT statement does not modify the data in the database. It is used simply to query the data. Whereas only one user at a time can modify data, any number of users can query or select the data concurrently.
The SELECT statement works at the table level; the output is also a table consisting of 0, 1, or more records. If the query is very specific, the output may consist of only one record. The structure of a SELECT statement is directly derived from the table structure. It takes the following general form:


SELECT columns ...
FROM table(s) ...
WHERE each row fulfills the condition(s) ...


The following sections provide information on SQL syntax and using SQL:

• SQL syntax 
• Using program variables 
• References 
• Company numbers 
• SQL and combined fields 
• SQL subqueries  
• SQL and delayed locks  
• SQL query extension

Read More