PrimeBaseTalk Programmer's Guide

Copyright © 2008, PrimeBase Systems GmbH. All rights reserved.

The information contained in this document applies to:

Last major update to this document on November 16th, 1999.
 
 

Java and JavaScript are trademarks of Sun Microsystems, Inc.

All other product names mentioned herein are the trademarks of their respective owners.


Table of Contents

1. Introduction

2. The PrimeBase Virtual Machine

"Hosting" the PBVM
PBT as Middleware
PBT and DAL
Virtual Machine Sessions
3. Concepts
Getting Started
Submitting Requests
Compilation
Retrieving Output
Subprograms
Statements
Comments
Primitive Data Types
Arrays, Classes and Objects
Identifiers
Variables and Constants
Scope and Namespace
Operators
Flow Control
Procedures
Error handling
4. Variables
Constants
5. Primitive Types
BOOLEAN type
Integral types
Floating point types
Fixed point types
Date and Time Types
Textual types
BINARY types
6. Reference Types
Tests on References
7. Special Types
Generic
Null
Objname
8. Operators
BOOLEAN Operators
Comparison Operators
Arithmetic Operators
Bit-wise Operators
Data Type Conversion Operators
Unary Operators
Assignment Operators
Cursor-based Reference Operator
9. Procedures and Functions
Declaring a Procedure
A minimal Procedure Declaration
The maximal Declaration
Global Procedures
Function Procedures - with single result
Procedures with multiple results
Procedures with arguments and defaults
Scope
Static Statement Blocks
10. Flow Control
IF
SWITCH, CASE, DEFAULT
LABEL, GOTO
BREAK
CONTINUE
WHILE
DO WHILE
FOR
FOR EACH
11. Error Handling

12. Classes

Forward Declarations
Class Access
Inheritance
Final Classes
Abstract Classes
Class Redeclaration
Class Members
Access Operators
Static Members
Member Access
13. Objects
Constructor
Member Variable Initialization
Final Member Variables
finalize() as Destructor
clone()
getClass()
Member Procedures and Functions
Static methods
Abstract methods
Overloading
Overriding
Final Methods
Method Access
Reflection
14. Arrays
Array Methods
Numerically Indexed Array
Associative Array
15. Cursors

16. Garbage Collection

Explicit call
17. Concepts
The Data Access Language
Sessions - the DAL "runtime" Concept
System Architecture
18. Basic Elements
Data Type Conversions
Basic Elements
Functions Calls: f()
Cursor Based Reference: ->
 
19. Control Statements
DBMS Statements
DESCRIBE DBMS
DESCRIBE OPEN DBMS
OPEN DBMS
CLOSE DBMS
USE DBMS
Database Statements
DESCRIBE DATABASES
DESCRIBE OPEN DATABASES
OPEN DATABASE
CLOSE DATABASE
USE DATABASE
DESCRIBE TABLES
DESCRIBE COLUMNS
20. Basic Statements
Variable Manipulation
DECLARE
UNDECLARE
SET
Execute Statements
EXECUTE FROM
EXECUTE IMMEDIATE
EXECUTE FILE
Print Statements
PRINT
PRINTALL
PRINTCTL
PRINTF
PRINTINFO
PRINTROW
Error Handling
ERROR
ERRORCTL
21. Program Constructs
Conditional/Branch Statements
IF
SWITCH
LABEL
GOTO
BREAK
CONTINUE
Iteration Statements
WHILE
DO
FOR
FOR EACH
Procedure Statements
DECLARE PROCEDURE
RETURN
CALL
22. PBCTL - Configuring PrimeBase DAL Compatibility

23. Encrypting PrimeBaseTalk files
 
 

Appendices

A. System Variables
A.1 Format Control Variables
$ampm, $datefmt, $day, $decfmt, $moneyfmt, $month, $timefmt, $tsfmt
A.2 Constants
$false, $null, $true

Data Type Constants:

$BOOLEAN, $CHAR, $date , $DECIMAL, $FLOAT, $integer, $LONGBIN, $LONGCHAR, $MONEY, $smfloat, $SMINT, $time, $timestamp, $VARBIN, $VARCHAR
$sqlnotfound, $version, $ServerVersion
A.3 System Control Variables
$sqlcode, $sqlcode2, $switch
A.4 Cursor Control Variables
$aborttime, $colcnt, $cursor, $locktimeout, $maxrows, $rowcnt, $rowlocking, $rowsaffected, $rowsperpage
A.5 DBMS Lookup Variables
$dbmszone, $dbmsbrand, $dbmsprotocol
A.6 Login Information
$logintime, $connid, $user
B. Built-in Functions
B.1 String Functions
$format(), $hash(), $left(), $locate(), $ltrim(), $right(), $rtrim(), $substr(), $tolower(), $toupper(), $trim()
B.2 Variable Information Functions
$exists(), $len(), $scale(), $typeof()
B.3 Cursor Information Functions
$cols(), $collen(), $colname(), $colplaces(), $coltype(), $colwidth(), $currentrow(), $rows()
B.4 Cursor Manipulation Functions
$deleterow(), $insertrow(), $updaterow()
B.5 File I/0 Functions
$close(), $create(), $delete(), $fileexists(), $filestamp(), $getcwd(), $geteof(), $gettempdir(), $listdir(), $map(), $mkdir(), $nativepath(), $open(), $openreadonly(), $read(), $readline(), $rename(), $rmdir(), $seteof(), $write(), $writeline()
B.6 Miscellaneous Functions
$base64encode()$encrypt(), $errorstring(), $getenv(), $md5(), $now(), $ostype(), $putenv(), $sendmail(), $sendmailxt(), $yield()
C. Golfers Database




 
 

1. Introduction

PrimeBaseTalk (PBT) is an object oriented language specifically designed for the programming of "server-side" functionality common to intra- and internet Web sites.

The language includes the best ideas and concepts from 4 generations of languages, and combines them seamlessly and systematically. This includes:

The combination of such tools applied to any problem is extremely effective and allows for rapid application development.

PrimeBaseTalk is also DAL (Data Access Language) compatible and, as a result, supports code previously written to access the PrimeBase Database Server. In addition, it is "Java-like", making it easy for experienced Java programmers to use. This, and other factors have determined the design of PrimeBaseTalk, as will be discussed below.

Examples

Many of the examples in reference manual make use of a sample database, called the Golfers database. The tables and columns of the Golfers database are explained in detail in Appendix C.

So if you want to try any of these examples yourself you may want to copy and paste the Create Script into a file execute them in the PrimeBase Automation Client (PBAC) or the PrimeBase SQL Database Server console using the EXECUTE FILE command:

EXECUTE FILE "golfersdb.sql" LOCATION "c:\"; <press ENTER>
go <press ENTER>
Also make sure you open the Golfers database in PBAC or the server console prior to trying any of the examples contained in this reference manual. You do this by issuing the following:
OPEN DATABASE Golfers; <press ENTER>
go <press ENTER>
 




 
 

2. The PrimeBase Virtual Machine

The PrimeBase Virtual Machine (PBVM), is responsible for compiling and executing programs written in PBT. To do this the PBVM allows the creation of multiple independent "runtime environments", also known as sessions. Each session has its own global variables and execution thread, and runs independently of other sessions.

The PBVM supports various API's (Application Programming Interfaces) common to database applications, including DAL/DAM (the original Data Access Language and Data Access Manager API's from Apple™), ODBC (Open Database Connectivity from Microsoft™), JDBC (Java Database Connectivity from Sun™ Microsystems) and EOF Adaptor (Enterprise Objects Frameworks Adaptor from Apple).

The application uses one of these API's to create and destroy a session, submit and run a PBT program and receive output from the running program. Programs are submitted as a combination of text and BINARY data. The built-in compiler transforms the program into an internal "byte-code" form which can be executed by the Virtual Machine. A program running in a session may exit (normally or due to an error). After this point the execution thread of the session is idle, however the session remains, and a further program (sometimes more accurately refered to as a sub-program) may be submitted and executed.

During execution, classes are created and objects instantiated by the program. These objects and classes persists as long as the associated session remains. Subsequent programs can access objects and classes created by previously run programs.

This persistance is ideal suited for supporting Web-based applications, in which the state of the applications must be maintained on the server.

Additionally, the use of sub-program execution matches the way in which a Web-based application handles page requests. When a page request arrives, various sub-programs are submitted and executed in order to prepare the response. The result page is returned to the waiting browser, and in the meantime the sessions execution thread is idle.




 
 

"Hosting" the PBVM

Like the Java VM, the PBVM is implemented as a Shared Library, Shared Object or Dynamic Linked Library (depending on the platform) and therefore must be "hosted" by an application in order to do it's job. Any application that supports one of the API's mentioned above will do, but general purpose applications that are programmable using PBT are of particular interest. Two such applications are provided by PrimeBase Systems:
PBT programs execute asyncronously and independently of programs in other sessions. The hosting application is responsible for assigning a thread for execution in each session. An application may choose to use one thread, however, in this case the application must schedule the execution in the various sessions itself.

Exactly how this is done depends on the API used. However, of all the API's mentioned above (DAL/DAM, ODBC, JDBC, etc.), the DAL API is the most suited to the internal workings of the PBVM, and is used by the PrimeBase Application Server and the PrimeBase Automation Client.

Other API's may limit the use of the PBT language in certain way due to limitations in their own specification. For example, the ODBC API only allows the programmer to use the SQL components of the PBT language.




 
 

PBT as Middleware

Using the PrimeBase Open Server Interface, PBT can act as middleware for diverse data sources, not just the PrimeBase Database Server. The Open Server Interface is based on the client/server model and can be used for remote execution (in the form of RPC - Remote Procedure Calls) or data exchange.

In order to support data transfer between a PBT program and an external data source an adaptor must be written that supports the PBOS Interface. The adaptor translates calls (SQL statements, procedure calls, etc.) from the PBVM, into calls to a 3rd Party DBMS (e.g. Oracle, Sybase etc.) or accesses data itself using, for example, XBASE or ISAM functions. Such an adaptor is compiled as a shared library and is also known as a PBOS Plug-in.

PBOS plug-ins for the most popular DBMS's are provided by PrimeBase Systems, including Oracle, and ODBC data sources.

An application called the PrimeBase Open Server loads the plug-in and handles communication (local over a network) between the PBVM and the adaptor code. Alternatively a PBOS Plug-in can be loaded directly by the PBVM, eliminating the need for inter-process communication. This setup has obvious performance advantages, however, remote execution of a plug-in by the Open Server may be required for load distribution, or because as specific API required is only available on a particular machine, etc.

Whether the plug-in is loaded directly by the PBVM or loaded and hosted by the PrimeBase Open Server depends on the function of the adaptor and requirements of the system as a whole. How a plug-is deployed does not depend on how the plug-in is written, but only on the requirements of the system.
 
 




 
 

PBT and DAL

As mentioned above PBT provides a level of compatibility with the Data Access Language (DAL) from Apple™, which is also supported by previous PrimeBase Systems products. PBT was designed to eliminate ambiguities and add several new language features ranging from arrays to object oriented programming.

To maintain compatibility and also take advantage of the new features, PBT introduces two different language modes.

 

Extensions to DAL

In the following documentation, differences to the DAL mode are highlited with a special icon.

 
 
 

Comparison to other languages

Where appropriate, differences to other languages are also noted.




 
 

Virtual Machine Sessions

The Primebase Virtual Machine supports parallel execution through the session concept.

PBVM is usually accessed by some hosting application - for example the Primebase Application Server for web-based applications. The host application can open any number of simultaneous and independently executing sessions.

There is some basic means of sharing between sessions within the same virtual machine - objects allocated during execution of the "initialize.dal" procedure file are shared. This is not recommended for complex multi-tasking purposes, as there are no synchronization mechanisms built into the language. Especially the position of cursors is also shared.

Instead, the database integration provides a superior support for persistent data, locking strategies and transactions including rollback. All of this arbitrated between any number of independent hosting applications.




 
 

3. Concepts

This is an overview of the PrimeBaseTalk language. It will hilite the main concepts of the language and provide selected samples. Further detailed explanations and comparisons to other languages will follow in later chapters.




 
 

Getting Started

The hosting application has to open a connection to the PrimeBase Virtual Machine. The exact way is dependent on the API in use, and described in the "PrimeBase Virtual Machine Reference Manual". This connection is also referred to as session. The hosting application may open any number of additional independent sessions for separate execution.

Usually the session setup involves specifying a database machine for the default connection, although there may none, and additional parameters for user authentification ( user name, password ).

For the PrimeBase Application Server, all of this is controlled from outside using the first entry in the file "connect.def".

In the interactive mode of the PrimeBase Automation Server, it offers a list of all available connection definitions for manual choice.




 
 

Submitting Requests

The hosting application will submit any number of PBT statements and input parameters to the PBVM using the appropriate API calls, due to prepare a subprogram invokation. The parameters are either passed as literals embedded into the program code, or sent utilizing a dedicated API call.




 
 

Compilation

At the topmost level all PrimeBaseTalk subprograms are a collection of complete statements, terminated by ";". Only complete and valid statements are accepted by the compiler for one subprogram. If the compilation fails the whole subprogram is rejected.

After another API call, the combination of statements and parameters is then compiled by the PBVM into its internal bytecode representation, which is then executed.

The PrimeBase Application Server provides a large choice of different points where such requests might get placed, including web page preparation and explicitly tagged page sections.

The PrimeBase Automation Server intercepts lines containing the "GO" keyword and forces execution.




 
 

Retrieving Output

The results originating from the running subprogram are returned to the hosting application by explicit PRINT and PRINTF statements. In order to receive these results, the hosting application will apply polling calls, again dependent on the used API.

The PrimeBase Application Server will receive the output and deal with it according to several conventions. The simplest method will pass output into the program's own output stream.

The PrimeBase Automation Server also converts all received output to text and copies the result to its standard output.

 

Example

print "Hello, world";

Screen output:


Hello, world

 



 
 

Subprograms

At the topmost level all PrimeBaseTalk subprograms are a collection of complete statements, terminated by ";". Although the subprogram can get transmitted using several separate API call invokations, only complete and valid statements are accepted by the compiler. If the compilation fails the whole subprogram is rejected.

Besides simple statements which will get executed straight after compilation, PBT also knows statements to declare variables, procedures or whole object classes, and associate them with a name for later referral. These named entities will persist in the session across multiple different subprogram invokations, providing a convenient storage area.

The PrimeBase Application Server uses this to maintain its user session state across multiple page invokations, associated with a single user.




 
 

Statements

Most PrimeBaseTalk statements are in a functional notation similar to C and Java. For database integration, several mighty (and therefor verbose) additional statements also integrate a superset of SQL.




 
 

Comments

Any text enclosed within "/*" and "*/" is considered a comment and therefor ignored for compilation.

There is also a new provision for single line comments - text after the "//" token is skipped until the end of line.
 
 

 

Hint

PBT comments do nest. This makes them a partial replacement for a preprocessor as known from the C language. This approach avoids the complexity because of which the C preprocessor was omitted from the Java language.

To comment out any range of code, including comments or not, just enclose it into another comment.
 


 
 
 

Hint

Some editors support balancing of brackets - exploit them inside your comments.

 
 
 

Example

/* { A Comment with a balanced bracket.
Some inactive code here.
/* an older, nested comment. */
Some more inactive code here.
} */
// the closing bracket above may get found by some editors
print "Hello, world.";




 
 

Primitive Data Types

PrimeBaseTalk provides a rich set of primitive data types, with the speed benefit of native execution.

These include specialized types for date and time arithmetic, arbitrary size fixed point arithmetic based on BINARY coded DECIMAL (BCD) and several types for textual and BINARY data.

All primitive data types and operations support the explicit $NULL (mark) value as it is required for basic data types of relational databases.

Values of the primitive types are kept read only and therefor secured against modifications in place. All operations on them work on temporary expressions, the results have to be assigned to a variable to change it.

Literals represent a constant value of a certain data type. The characters that make up a single literal may not be separated by space, tab or end of line characters. Literal values may be placed directly in expressions and queries in PBT programs.

Besides sending literals embedded into query code, the PrimeBase VM provides dedicated API calls for direct submission of values.

Maintained at the database engine there is an additional level of data types, with stronger type checking and several additional features (case insensitive comparisons etc.) up to composite data types.




 
 

Arrays, Classes and Objects

In addition to the builtin primitive data types, PBT provides native support for classes and objects, with some special classes (Array and Cursor).

All of these data types are handled using object references, in order to implement an object model comparable to Java.

Arrays are either preallocated and numerically indexed or dynamically allocated as associative array, similar to Java's Dictionary class.

Cursors are used to store result sets of database operations.

The data types are described in detail in a following section.




 
 

Identifiers

The PBT language is case insensitive for statements, identifiers and keywords, with identifiers having a maximum length of 31 characters.

The first character of an identifier is drawn from the 26 alphabetical characters and the "_" underscore. The additionally possible "$" dollar sign is reserved for system functions.

The following identifier characters may again use the 26 alphabetical characters with addition of digits, the "#" hash mark and the "_" underscore, but not the dollar sign.
 
 

 

Note

The keywords "new", "this", "super" are especially reserved because they have a different meaning even inside expressions.

Other keywords include "final", "abstract", "class", "extends", "public", "protected", "private" and "static".
 




 
 

Variables and Constants

Variables are slots used to hold any value, associated with an identifier (name). Their type is predefined at the time of declaration, making them available for further access.

PrimeBaseTalk has a new concept of dynamic constants. These are similar to traditional variables, allowing to take full advantage of all language features during initialization. From then on they are protected against changes.




 
 

Scope and Namespace

With DAL, all kinds of entities shared the same global namespace. PrimeBaseTalk continues to support this global namespace, but provides alternatives.

There is also a local scope during execution of procedure calls, for use by temporary "local" variables.

The local scope is shared within one procedure call - variables declared persist until exit of the procedure or explicit undeclare, even when they are declared in a contained block.

To reduce the probability of collisions in the single global namespace, PrimeBaseTalk now also supports additional namespaces, within the scope of classes.
 
 

 

Note

The package concept is currently not supported.

 
 
 

Note

Classes are declared and defined in one consecutive code sequence, similar to Java.

Because of the use of classes for namespaces, the namespaces are not open for extensions.
 

For execution of immediate statements there is an anonymous "static" block limiting the variable lifetime of temporary variables.




 
 

Operators

SQL allows both '=' and '==' for the equality comparison, and '=' for assignments.

To resolve the ambiguity between assignment and comparison, the PrimeBaseTalk language introduces ':=' as assignment operator. Only in the DAL compatibility mode, the previously used '=' is still available.

Class members are accessed with the operator ':' instead of '->' or '::' or '.'




 
 

Flow Control

PrimeBaseTalk provides standard flow control for conditional execution and iteration (loops) with additional support of database specifics.




 
 

Procedures

The DAL language already supported procedural programming with some unconventional additions. These advantages persist but are integrated into a new level of object oriented programming.




 
 

Error handling

Errors are handled using the traditional methods of DAL. In exceptional situations ranging from "division by zero" to "transaction timed out", an error code is made available to the handling program. Dependent on further settings, execution is returned to the program hosting the PBVM, or the session itself tries to handle the problem.

A number of additional information is available through several system variables and functions.




 
 

4. Variables

Variables and constants are declared either in the global namespace, as member variables of classes, or dynamically during execution of procedures. These concepts are also described in detail later.

The variable declaration reserves a storage area and associates it with the given name and specified data type. Variables with one data type will only accept values of this data type. Some data types are also automatically converted.

Unless given a default value on initialization, newly declared primitive type variables are tagged as "unitialized", therefor preventing use of any random value. Further explicit initialization is possible within the declaration, assigning any expression. Reference variables are initialized with the empty reference $NULL unless an explicit initialization is provided.

The value stored in a variable is available for multiple use in any expression, until it gets replaced with a new assignment or the variable gets removed - with an explicit undeclare in global or local scope, or by redeclaring it as something else with the same name.
 
 

 

Example

declare INT a;
integer b := 1;
// print a; -- would fail because a is uninitialized
a := b + 1;
undeclare b; // now b is gone
declare VARCHAR a = "x"; // replacing old a




 
 

Constants

Named constants are not supported. Instead PBT provides a runtime protection against overriding variables declared with the final identifier. The advantage is a larger flexibility for the initialization of the constant - even from the result of a function call.

Global final variables are not protected against redeclaration.




 
 

5. Primitive Types

For use by variables and temporary expressions, PrimeBaseTalk supports a variety of data types which either are treated as atomic value or refer to a larger object. Data types working with concrete values are either called "primitive types" or, especially in context of relational databases, "basic types".

Primitive values are "atomic" because they are not modifyable in place. A text or numeric value stored in a variable will remain the same, until the containing variable explicitly gets assigned a new value.

The primitive types have an associated numeric type code, wich is internally returned by the functions $typeof() and $coltype(), and is also used by several database related statements.
 
 

 

Note

The type codes used by the PrimeBase Virtual Machine are those of the CL1 API. If you access the PrimeBase Virtual Machine using a different API, for example Apple DAM or ODBC, you will encounter different type codes.
See also:

$typeof(), $coltype(), printctl, describe columns, describe dbms
 
 

 

Note

Some extended functionalities, e.g. of domains are not supported beyond the database engine.
The following sections describe the primitive types in detail.




 
 

BOOLEAN type

The BOOLEAN data type is expected by control statements like if and while.

An implicit conversion to BOOLEAN is only available from integral, floating point and numeric types - textual and reference types are excluded. BINARY types have additional size constraints for a successful conversion.

The literals $false, $true and $maybe represent the different BOOLEAN values.

The BOOLEAN $maybe is mainly produced by a comparison with one of the operands being $null.
 
 

 

Example

print $typeof($true), $BOOLEAN;
print $true, $false, $maybe;
print (VARCHAR) $true, (VARCHAR) $FaLsE, (VARCHAR) $maybe;
print (BOOLEAN) "$maybe"

Screen output:


1 1
$TRUE $FALSE $MAYBE
$TRUE $FALSE $MAYBE
$MAYBE




 
 

BOOLEAN Values: 3-Valued Logic

Unlike C which uses the value zero to represent FALSE and non-zero to represent TRUE, PBT uses a special BOOLEAN type value. A BOOLEAN value can be one of 3 logical values: $true, $false or $maybe. The $maybe value is the result of a comparison with an unknown value ($null).

Evulation is done according to the following logic tables:
 
 

 
AND $true $false $maybe
$true $true $false $maybe
$false $false $false $false
$maybe $maybe $false $maybe

 
 
 
OR $true $false $maybe
$true $true $true $true
$false $true $false $maybe
$maybe $true $maybe $maybe

 
 
 
NOT  
$true $false
$false $true
$maybe $maybe




 
 

Conditional Expressions (if, while, etc.)

Condition expressions such as those in the if, while and do statements, are only executed if the result is $true (not in the case of $false or $maybe). The conditional expression in these statements is automatically converted to a BOOLEAN value.

Because of 3-valued logic used, conditional expressions can have some surprising effects. For example:
 
 

 

Example

procedure test(a)
argument integer a;
{
        print "Testing the value ", a;
        if( a==1 )           print "The value is equal to one";
        if( (a==1)!=$false ) print "The value is equal to one or unknown";
        if( a!=1 )           print "The value is not equal one";
        if( a==1 or a!=1 )   print "The value is either equal or not equal";
          else               print "Two valued logic fails!";
        if( a is null )      print "The value is null";
        if( a is not null )  print "The value is not null";
print;
}
end procedure test;

test(1);
test(2);
test($null);

Screen output:


Testing the value  1
The value is equal to one
The value is equal to one or unknown
The value is either equal or not equal
The value is not null






Testing the value  2
The value is not equal one
The value is either equal or not equal
The value is not null






Testing the value  $NULL
The value is equal to one or unknown
Two valued logic fails!
The value is null




 
 

Integral types




 
 

tinyint

Tiny integers have the characteristics of the unsigned 8 bit integer data type know from the C language. Their value ranges from 0 to 255.
 
 
 

Example

tinyint t := 1;
print $typeof(t), $tinyint, $len(t);
print tinyint 0x7F, tinyint 0x80, tinyint 0xFF;

Screen output:


20 20 1
127 128 255




 
 

SMALLINT / SMINT

Small integers are signed short (16 bit) integer ranging from -32768 to 32767.
 
 
 

Example

SMALLINT s = 1;
print $typeof(s),$SMALLINT, $len(s);
print SMINT 0x7fff, SMINT 0x8000;

Screen output:


2 2 2
32767 -32768




 
 

INTEGER / INT

The integer data type is equivalent to signed long (32 bit) integer ranging from -2147483648 to 2147483647.
 
 
 

Example

print $typeof(1),$integer, $len(1);
print INT 0x7FFFFFFF, (INT 0x7FFFFFFF) + 1;

Screen output:


3 3 4
2147483647 -2147483648

 
 
 
 
 

Hint

A 64 bit integer data type is not supported, but you can achieve even better precision and range by using the "DECIMAL" fixed point data type.

 
 
 

Note

In parameter matching for overloaded functions (described later), all integral types are considered equal.




 
 

Floating point types

Floating point values support a larger numerical range, but at the cost of less precision. This is achieved by reserving a part of the internal representation for an exponent.
 
 
 

Note

In parameter matching for overloaded functions (described later), all floating point types are considered equal.




 
 

SMFloat

A 4 byte floating point variable.




 
 

FLOAT

An 8 byte floating point variable.




 
 

Literals

Floating point literals are expressed in exponential form, with a DECIMAL point and a signed exponent of an 'e' or 'E'.
 
 
 

Example

print $smfloat, $FLOAT;
print 0.123e12, 3.141E0, 100.00001e-14, -5.6789E+8;

Screen output:


4 5
1.23E+11 3.141E+00 1.0000001E-12 -5.6789E+08




 
 

Fixed point types

The fixed point variables are implemented on base of BINARY coded decimals (BCD).

This means their internal representation is an almost arbitrarily long string of DECIMAL digits, two of each are squeezed into one byte value.
 
 

 

Hint

You can view the internal format of BCDs by converting to BINARY.

 
 
 

Example

printf("%x\n",VARBIN DECIMAL[10,2]  1234.56);
printf("%x\n",VARBIN DECIMAL[10,2] -1234.56);

Screen output:


000123456c
000123456d
Conversion between text and fixed point is controlled by the format variables $decfmt and $moneyfmt. $moneyfmt usually includes the currency symbol.




 
 

DECIMAL

DECIMAL literals are expressed using an optional sign, digits and one contained DECIMAL point ('.').
 
 
 

Example

print $decfmt, DECIMAL[10,4] 1234.56;

Screen output:


9,999.9 1,234.56




 
 

MONEY

MONEY literals are specified with a leading dollar ('$') sign, then optional sign, digits and a DECIMAL point ('.').
 
 
 

Example

print $moneyfmt, MONEY[10,4] 1234.56;

Screen output:


$ 9,999.9 $ 1,234.56




 
 

Date and Time Types




 
 

Date

Date values are internally represented by single bytes for date and month, and a short integer for the year. The maximum valid year is 9999 though.

The $datefmt, $day, $month system variables control conversion from and to text.
 
 

 

Hint

By assigning partial control strings to $datefmt, you can extract any parts of the date by converting it to text.
In arithmetical expressions, the numeric value 1 is equivalent to one day.
 
 
 

Example

print print (date) "1/1/1999" + 1;

Screen output:


01/02/1999




 
 

Time

Time values are internally stored as one byte each for hour, minute, second and hundreths of second.

The $ampm and $timefmt system control variables control conversion from and to text.




 
 

Datetime, Timestamp

The datetime data type combines date and time into a single value.

Timestamp is only an alternate name for the same data type.
 
 

 

Hint

The function $now() yields the current date and time. The keyword NOW is also used in the database statement CREATE DEFAULT




 
 

Date and Time Literals

Date and Time constants are best submitted in their native format. If the hosting application does not support this, they must be supplied according to the currently active conversion format.

The conversion format variables are accessible for read and write, therefor you can save them into a temporary variable, set them to your expected format and later on restore the value.
 
 

 

Example

print $datefmt;
print date "1/1/1999";

Screen output:


MM/DD/YYYY
01/01/1999




 
 

Textual types

In addition to the direct support of textual data types, there is a rich library of string functions.

Most string operations are 1 based, 1 is the position of the first character.
 
 

 

Hint

You can use the PBT function $format() (which is an equivalent to sprintf()) to interpret C escapes and apply formatting to additional parameters.

There is also a function called PRINTF(), which uses the same parameters as $format() and which causes direct output instead of returning a string.
 




 
 

VARCHAR

The maximum length of character based values, either for variables or expressions (not for database table columns!) in string operations, are as follows:
 
 
 
Date Type Length in Bytes Length in Characters
CHAR 1 048 576 1 048 576
VARCHAR 65 536 65 536
UNICODE 1 048576 524 288

 
 

Note

The underlying database system might impose stronger restrictions on these data types!




 
 

CHAR

The CHAR data type is used for fixed length strings. Shorter strings are padded with trailing spaces (ASCII character 32).

For this reason trailing spaces are ignored for all string comparison operations.
 
 

 

Example

print $CHAR, "A" == "A    ";

Screen output:


9 $TRUE




 
 

LONGCHAR

Beyond the size limit of 65 535 characters for a VARCHAR, you have to use the LONGCHAR data type.

Most string operations work for LONGCHAR.
 
 

 

Example

print $LONGCHAR;

Screen output:


14




 
 

UNICODE

UNICODE is an ISO standard for encoding characters, with support of all written languages of the world. In version 2.0 of the UNICODE standard (version supported in PrimeBase) 16 bit encoding is used to encode up to 65 536 different characters.

More information is available at www.unicode.org

The maximum length of UNICODE strings is 524 288 characters.
 
 

 

Example

print $typeof(UNICODE "");

Screen output:


27

 
 
 

Note

For correct working of the UNICODE data type, the "UNICODE" folder must be present inside the Setup folder.

Some string functions ( $toupper, $tolower) currently don't expect UNICODE. They convert to VARCHAR and assume the Macintosh Roman character encoding.
 




 
 

Literals

Character literals are a string of characters, enclosed either in single or double quotes.

The literal may contain the same quote by duplicating it, rather than escaping it with a backslash as in C.

The $format() function supports C-like escape sequences.
 
 

 

Example

print $format("x=%d\ny=%d\n%s",1,2,"Hello, ""YOU!""");

Screen output:


x=1
y=2
Hello, "YOU!"




 
 

BINARY types

Literals for BINARY types are specified in hexadecimal notation which produces a VARBIN.

The BINARY data types, especially VARBIN, can be used as intermediate, raw data type.
 
 

 

Example

print INT 0x12345678;
printf("%x",VARBIN "1234");

Screen output:


305419896
31323334




 
 

VARBIN

The VARBIN data type is the default for short BINARY data.

VARBIN is often used as intermediate data type when converting between the raw representations of incompatible data types.
 
 

 

Example

print $len(0x01), $typeof(0x01), $VARBIN, tinyint VARBIN "A", VARCHAR 0x40;

Screen output:


1 13 13 65 @




 
 

LONGBIN

This is the BINARY Large OBject (BLOB) variant of the BINARY data type.

In database tables LONGBIN fields are stored as a separate file, with only a reference stub of 64 bytes counting into the maximum record size.
 
 

 

Example

print $LONGBIN;

Screen output:


15




 
 

BINARY

The BINARY data type is the fixed-size variant of the VARBIN data type, similar to CHAR versus VARCHAR.
 
 
 

Example

print $typeof(BINARY 0);
printf("%x\n",BINARY[10] 4660);

Screen output:


25
00001234000000000000




 
 

Literals

BINARY strings are submitted in either of two hexadecimal notations.

They should contain an even number of hex digits. ( 0123456789ABCDEF )
 
 

 

Example

print 0x28736f6D65, 0X726177, X"6461746129";

Screen output:


(some raw data)




 
 

6. Reference Types

The PBT object model is similar to Java, with some shortcuts. It is described in detail later.




 
 
 
 

Tests on References

There are no default comparison operators between object references. Currently the only legal operations are NULL tests:
 
 
 

Example

class A {};
A aa = new A();
print   aa is null, aa is not null;
Members of referenced objects are accessed using the ':' operator.




 
 

7. Special Types

This section covers related concepts which are not represented as own type.




 
 
 
 

Generic

Variables declared as generic are containers which accept values of any data type, both primitive and reference types.

The currently assigned data type is determined by the $typeof() function.

Generic is not available as data type for database fields. You may use the VARBIN type instead, because it is generically convertible.




 
 

Null

Null is used for two different concepts.

In the context of relational databases it stands for missing (unknown) or inapplicable information. This is supported by all primitive data types.

Contrary to database fields, there is no way to declare a variable to be NOT NULL.

In the context of object oriented programs $null stands for an empty reference.

The $null constant
$null has no data type on its own (it translates to VARCHAR by default). In assignments and expressions $null is compatible with all other data types.

$null only exists with the dollar sign. But for compatibility reasons, there are also some special syntactic forms originating from SQL, which require the keyword NULL instead.

Sticky NULLs
Expressions involving $null usually yield $null. Prominent exceptions are the $len() function (yields zero) and comparison operations (producing booleans).

Therefor care must be taken whenever nulls could be introduced, for example through the database schema.
 
 

 

Example

integer i = $null;
print $typeof($null), $VARCHAR, $typeof(i), i is null, $len(i), 1+$null;

Screen output:


12 12 3 $TRUE 0 $NULL




 
 

Objname

The object name data type is used in database statements and when accessing cursors. It does not work as pointer to member for PBT objects.

There is also no support for objname by the database engine.
 
 

 

Example

print $typeof(objname "name");
describe databases; fetch;
print ->name, $cursor->(objname "name");

Screen output:


17
pbedemo pbedemo




 
 

8. Operators

In expressions, operators are used to manipulate or combine operands (variables, literals or other expressions).




 
 

BOOLEAN Operators

The operators AND, OR and NOT operate on BOOLEAN values, and return a BOOLEAN value.

Note that, unlike for example in the C programming language, both sides (operands) of the operators AND and OR are evaluated regardless of the outcome of the evaluation of the first operand. This is relevant if the calculation itself has a sideeffect, for example in a procedure call.
 
 

 

Example

BOOLEAN initialized = $true;
BOOLEAN printed = $false;
IF ( initialized AND NOT printed )
{
 PRINT "Now it is being printed.";
} ELSE {
 PRINT "ERROR: Can't print, it probably has not " +
       "been initialized yet.";
}


Screen output:


Now it is being printed.




 
 

Comparison Operators

The comparison operators and the "null-test" operator (IS NULL), all produce BOOLEAN values. Arguments may be values of any data type (as of PrimeBase 3.5 also CURSOR).

In comparisons, if one (or both) of the operands are $null, the result is the BOOLEAN value $maybe.

Comparison operators supported by PBT are

'==', '<>', '<', '>', '<=', '>=', '!='
and the unary operators IS NULL and IS NOT NULL.

In addition, the DAL compatibility mode supports the equality operator '='.
 
 

 

Example

BOOLEAN itIsRaining = $true;
IF ( itIsRaining == $true ) {
  PRINT "Too bad, it is raining.";
} ELSE {
  PRINT "Hey, it is not raining.";
}

/* Or equivalent: */
IF ( itIsRaining ) {
  PRINT "Too bad, it is raining.";
} ELSE {
  PRINT "Hey, it is not raining.";
}

/* And a different example: */
INTEGER anIntValue = 10;

IF ( anIntValue != 10 ) {
  PRINT "The value has not been properly assigned " +
        "to the variable!";
} ELSE {
  PRINT "The value has been properly assigned to the " +
        "variable.";
}

Screen output:


Too bad, it is raining.
Too bad, it is raining.
The value has been properly assigned to the variable.

 
 
 

Warning

Note that trailing spaces are ignored when comparing strings,
meaning that 'a ' == 'a    ' evaluates to BOOLEAN true.




 
 

Arithmetic Operators

The following arithmetic operations are supported by PBT expressions: addition (+), subtraction (-), multiplication (*), division (/) and remainder (%).
 
 
 

Example

PRINT "The remainder of 10 / 3 is", VARCHAR( 10 % 3 );

Screen Output:


The remainder of 10 / 3 is 1
Addition operators and Date, Time
Addition of plain date and time values won't yield the intended results. Instead, the date has to be converted to a datetime.
 
 
 

Example

date d = $now(); datetime dt = d; time t = $now();



print d+t, dt+t, $now();



02/02/1994 05/12/2000 17:34:05.00 05/12/2000 17:34:05.42
The conversion from VARCHAR is useful to compose date and time values from their components.
String Concatenation
The addition (+) operator may also be applied to character string values. The character string values are concatenated, and the result is a VARCHAR value.
 
 
 

Example

PRINT "Hello" + " " + "world" + "!";

Screen Output:


Hello world!




 
 

Bit-wise Operators

The bit-wise operations are those familiar to C and Java programmers. Shifting operators are currently not implemented.
 
 
 

Example

PRINT "255 & 127 =", VARCHAR( 255 & 127 );

Screen Output:


255 & 128 = 127




 
 

Data Type Conversion Operators

PrimeBaseTalk supports extensive data type conversion. Conversion to character string values is controlled by the various Format Control Variables.

Additionally there is an explicit syntax for conversion between other data types.

The following sample uses the function now() to get the current time with the type of datetime. The first cast extracts the time part, the second part converts it into a VARCHAR, using the format control variable $timefmt.
 
 

 

Example

print (VARCHAR) (time) $now();

Screen output:


23:48:08.59
The data type used for the conversion may specify size and scale as described for the various data types.

In DAL compatibility mode the old syntax without explicit round brackets is still valid. In addition, it is not any more required to enclose the intermediate result of the first cast in brackets.
 
 

 

Example

print tinyint VARBIN $maybe;

Screen output:


2
Numerical values not equal to zero are considered $TRUE in BOOLEAN promotion.

DECIMAL[] used to be a valid type for declarations and casts to the DECIMAL data type. Use DECIMAL[,] now to prevent collisions with the array mechanism.
 
 

 

Example

print (DECIMAL[10,2]) 0, DECIMAL[10,2] 0;

Screen output:


0.0 0.0




 
 

Unary Operators

The unary operators supported by PBT are unary-affirmation (+), unary-negation (-), and unary bitwise NOT (~).

Permissible operands are all numeric types (TINYINT, SMINT, INTEGER, DECIMAL, MONEY, SMFLOAT, FLOAT), and BINARY values (VARBIN).

operator ++ and --

These are currently no true operators, but instead extensions of the set statement, and limited to the postfix variant.
 
 

 

Example

INT i = 1;
set i++;
i++;
print i;
3

 
 
 

Example

/* these would all fail:
++i;
print i++;
MyClass:myVar++;
*/




 
 

Assignment Operators

Assignments change a slot referenced by some expression, on the left side of the assignment operator, to contain a new value provided by another expression, on the right side of the assignment operator.

The assignment operator has two equivalent variants: ':=' and '='.

':=' is the preferred notation required by PrimeBaseTalk mode programs. PrimeBaseTalk mode will reject the operator '='.

'=' is deprecated and only supported in DAL compatibility mode programs. It has the severe problem that the SQL language also allows the operator '=' for equality comparison. The operator ':=' is therefor recommended for DAL compatibility mode too.

Expressions using the operator = in an intermediate result are interpreting it as comparison operator.
 
 

 

Example

INT a := 1;
print (a =  2), a;
print (a == 2), (a := 3), a;

Screen output


$FALSE 1
$FALSE 3 3
There are no arithmetic assignment operators ( a+=7 ... ).




 
 

Cursor-based Reference Operator

The '->' operator is used to access values of the current row of a CURSOR rowset. The column may be specified using the column alias, an INTEGER literal representing the column ordinal number or a variable containing one of these two.

If the variable is of type INTEGER it is the column ordinal number, and if it is of type OBJNAME it is the column alias.
 
 

 

Example

select 1 as a, 2 as b into myCursor;
fetch of myCursor;
declare objname a = "b";
print myCursor->a,myCursor->:a;

Screen output:


1 2




 
 

9. Procedures and Functions

Procedures and functions allow to bind frequently used program code to a name. These are then available for execution in subsequent program code, even in the same subprogram.




 
 

Declaring a Procedure

Procedures are declared on a per session basis. They are not stored inside the database - the Primebase Virtual Machine does not require a database connection for its work.

As with all identifiers, the name of procedures is limited to 31 characters, and case insensitive.
 
 

 

Hint

Procedures declared on startup of the first session, within the file "initialize.dal", are shared between all additional sessions in the same PrimeBase Virtual Machine.

This feature should be avoided during development, so you don't experience replaced code.
 




 
 

A minimal Procedure Declaration

Simple procedures have no arguments, or only arguments declared with a default. They may get invoked by simply writing their name as a statement. It is also possible to apply the more verbose syntax of complex procedures.
 
 
 

Example

PROCEDURE mySimpleProc()
returns VARCHAR;
{
        printf("Hello, world\n");
        return "ABC";
}
END PROCEDURE mySimpleProc;
Unhandled results are silently ignored.
 
 
 

Example

mySimpleProc;
mySimpleProc();
call mySimpleProc();

Screen output:


Hello, world
Hello, world
Hello, world

 
 
 

Note

It is not possible to omit the round brackets for member function calls.




 
 

The maximal Procedure Declaration

In addition to the previous sample, procedures can have multiple arguments and/or multiple results.
 
 
 

Example

DECLARE GLOBAL PROCEDURE myProcedure(a1,a2,a3)
RETURNS VARCHAR,INTEGER;
ARGUMENT VARCHAR a1 = "1";
ARGUMENT GENERIC a3 := $NULL;
{
        PRINT "1", a1;
        PRINT "2", a2;
        PRINT "3", a3;
        RETURN a1,7;
}
END PROCEDURE myProcedure;
The DECLARE keyword is optional for procedure declarations.

The GLOBAL keyword is optional. It is only important when you use the "execute file" statement from within an old style global procedure. Procedures declared within that file are only temporarily available, within the scope of that procedure.

The optional RETURNS clause may declare multiple results by type. These are returned in parallel, not alternatively.

The order of the arguments in the parameter list determines the order of binding on invokation. The order may be different from the following parameter declarations.

For each parameter there can be a parameter declaration using the ARGUMENT. If a parameter is missing from the parameter declarations, it is assumed to be of type GENERIC.

In the parameter declaration, you can optionally provide a default expression. In DAL mode, both assignment operators are available for that purpose. In PBT mode (within class declarations), you have to use the ':=' operator.

Finally, you have to copy the exact procedure name into the END PROCEDURE part of the declaration.

If the parameter was declared with a default value, it is optional in the call. To skip optional parameters and override others, use an empty expression.
 
 

 

Example

// equivalent assuming previous procedure declaration:
myProcedure(,2);
myProcedure("1",2,$null);




 
 

Global Procedures

For PBT mode subprograms (class methods) all procedures created by "execute file" are declared globally by default. Only when you use "execute file" from within DAL compatibility mode non-global procedures created there are purged, when leaving the scope of the "execute file" statement.
 
 
 

Example

// file myProc
PROCEDURE myProc() { print "2"; } END PROCEDURE myProc;
// code
PROCEDURE myProc() { print "1"; } END PROCEDURE myProc;
PROCEDURE aTest()
{
 print  "DAL mode";
 myProc;
 execute file "myProc.dal";
 myProc;
}
END PROCEDURE aTest;
aTest;
myProc;

class X {
public static PROCEDURE aTest()
{
 print  "PBT mode";
 myProc;
 execute file "myProc.dal";
 myProc;
}
END PROCEDURE aTest;
};
X:aTest();
myProc;

Screen output:


DAL mode
1
2
1

PBT mode
1
2
2




 
 

Function Procedures - with single result

Procedures with one result are allowed inside expressions.

For methods, inside class declarations, there is a new alternate function declaration syntax.
 
 

 

Example

print mySimpleProc() + "DEF";

Screen output:


Hello, world
ABCDEF




 
 

Procedures with multiple results

It is possible to return a large number of result expressions in parallel, instead of the usual maximum of one.

This is also a alternative for the "call by reference" notation of other languages which silently modifies given parameters.
 
 

 

Example

procedure multipleReturn()
returns VARCHAR, integer;
{
        return  "Test", 3;
}
procedure multipleReturn;
Procedures with multiple results are called with the explicit call syntax:
 
 
 

Example

generic a,b;
call multipleReturn() returning a,b;
print a,b;

Screen output:


Test 3




 
 

Procedures with arguments and defaults

You can specify a matching default expression for each argument. The default is taken as a replacement value for the argument, when the calling code did not provide enough parameters or omitted the matching parameter.
 
 
 

Note

The default is evaluated only when no other value is specified.

 
 
 

Example

procedure tick()
returns integer;
{
        print "tick";
        return  1;
}
end procedure tick;

procedure timed(a1,a2)
argument time a1 = $now();
argument integer a2 = tick();
{
        print   a1;
        print   a2;
}
end procedure timed;
timed;
timed(,2);

Screen output:


tick
11:06:38.84
1
11:06:38.92
2




 
 

Scope

Parameters and variables declared within procedures have a local scope. That is, they expire when the procedure is left.




 
 

Static Statement Blocks

Another addition to the DAL language is the introduction of static statement blocks. These fulfill two purposes:

The statements are executed in PBT mode rather than in DAL compatibility mode.

The statement block is a separate scope for variable declaration.
 
 

 

Example

integer a = 1;
if( $true ) static {
        integer a := 12;
        static {
                integer a := 123;
                print a;
        };
         print a;
};
print   a;

Screen output:


123
12
1
As you see from the example, it is possible to nest the static blocks.
 
 


 
 

 

Note

Non-static blocks executed at global level or within other blocks of procedures or functions do not introduce a new scope.

 
 
 

Example

INT a = 1;
if( $true ) {
        INT a = 2;
}
print a;

Screen output:


2




 
 

10. Flow Control

PrimeBaseTalk provides the standard statements for flow control.




 
 

IF

The IF statement evaluates the given expression. If the value or its BOOLEAN promotion is the BOOLEAN $true, the following statements gets executed. Otherwise an other statement after an optional ELSE keyword is executed.
 
 
 

Example

if( $true ) print "yes";
else print "no";
if $null is null print "yes"; else print "no";

Screen output:


yes
yes




 
 

SWITCH, CASE, DEFAULT

The SWITCH statement evaluates the given expression, which might be of any PrimeBaseTalk primitive type, including textual types.

Execution is resumed at the first CASE literal matching the value, and continues till the end of the SWITCH statement or the first BREAK encountered, whichever comes first.

The DEFAULT case is used if no direct matching literal is found. It can be located anywhere inside the SWITCH statement, even preceding further CASEs.

Neither duplicate CASEs with the same literal nor multiple DEFAULTs are permitted within one SWITCH statement.
 
 

 

Example

switch( "A" ) {
case "A": print "fine";       break;
case "B": print "bad";        break;
case "C": print "too bad";    break;
default:  print "don't know"; break;
case "E": print "easy";       break;
);

Screen output:


fine
The SWITCH statement can be used to deal with generic parameters to procedures.
 
 
 

Example

generic aValue := "A";
switch( $typeof(aValue) ) {
case $CHAR:
case $VARCHAR:
case $LONGCHAR:
  print """", aValue,""""; break;
default:
  print VARCHAR aValue;
);




 
 

LABEL, GOTO

The LABEL statement indicates a position in code that can be addressed by the GOTO statement.

The GOTO statement jumps to the statement following the LABEL statement with the same name. The label must be in current scope (procedure or global).

A GOTO statement may jump out of FOR, FOR EACH, WHILE, DO and SWITCH statement, to break the flow of execution. It is not recommended to jump into the middle of these statements.
 
 

 

Example

GOTO ComeHere;
PRINT "A";
LABEL ComeHere:
PRINT "B";

Screen output:


B




 
 

BREAK

The BREAK statement is used to exit the current loop (WHILE, DO, FOR, FOR EACH) or SWITCH statement.

When a BREAK appears within nested loops, or SWITCH statements, it will cause the processing of the innermost loop (or rather, SWITCH) to break. It is not possible to cause a multilevel break in a single statement.




 
 

CONTINUE

The CONTINUE statement interrupts the flow of the body of a WHILE, DO, FOR, FOR EACH statement, thus causing the remainder of the current iteration of that statement to be skipped.

Execution continues with the loop control expression (for WHILE or DO) or with the loop-reinitialiser (for FOR), or with fetching of the next row (for FOR EACH).
 
 

 

Example

INT i;
FOR( i:=1; i<5; i++ ) {
 if( i==3 ) continue;
 print i;
};

Screen output:


1
2
4




 
 

WHILE

The WHILE statements repeatedly evaluates the given expression. If this expression yields or is converted to $true, the next statement is executed. Otherwise the loop is ended.
 
 
 

Example

integer  secs := 10;
datetime till := $now() + secs;
print "Let's sleep for", secs, "seconds!";
while( till > $now() ) {
  $yield();
}
print "done.";

Screen output:


Let's sleep for 10 seconds!


done.




 
 

DO WHILE

The DO statement executes the contained statement repeatedly until the post-test evaluates to $false.
 
 
 

Example

BOOLEAN failed;
do { failed := trySomeThing(); } while failed;




 
 

FOR

The FOR statement is used to control a loop with dependence on a control variable.

The first expression in the following round brackets is an assignment used to initialize the control variable.

The second expression is the loop condition. The loop will repeat the following statement as long the second expression yields $true.

The third expression is another assignment, used to update the control variable after the following statement was executed.
 
 

 

Example

INT i;
for( i:=0; i<2; i++ )
  print i;

Screen output:


0
1




 
 

FOR EACH

The FOR EACH statements is a special loop for the result sets of database operations.

Its control variable is a cursor.
 
 

 

Example

cursor x;
describe databases into x;
for each x
  printrow x;

Screen output:


Model
Master




 
 

11. Error Handling

The 'errorctl' statement sets an internal control variable - execution continues after the error situation or is returned to the program hosting the PBVM.

For both cases there are several system variables with further information:

 

Note

There are no Java-style exception objects.

 
 
 

Example

procedure toDate(aText)
argument VARCHAR aText;
{
  VARCHAR saveFmt := $datefmt;
  date    myDate  := $null;
  errorctl 1; // ignore following errors
  $datefmt = "MM/DD/YYYY";  myDate := aText;
  $datefmt = "MM/DD/YY";    myDate := aText;
  $datefmt = "D. MM. YYYY"; myDate := aText;
  $datefmt = "D. MMM YYYY"; myDate := aText;
  errorctl 0; // resume default failure mode
  $datefmt := saveFmt;
  print myDate, aText;
}
end procedure toDate;
toDate( "25.7.1966" );
toDate( "7/25/66" );
toDate( "25. July 66" );

Screen output:


07/25/1966 25.7.1966
07/25/1966 7/25/66
07/25/1966 25. July 66

 
 
 

Hint

In the Primebase Enterprise Objects (PBEO) framework you find the functions PBE:try() and PBE:catch()

They implement a nesting counter for the internally used errorctl function, maintain and evaluate the $sqlcode system variable.
 




 
 

12. Classes

The major new addition of PrimeBaseTalk to the previous DAL language is the introduction of object oriented programming. This is achieved by direct language support for the creation of indepenent modules, with their own variables and methods. Similar to other languages, especially C++ and Java, these modules are called classes.

A class is just another entity residing in the global namespace. It is introduced with the "class" keyword and its given name before the class declaration block which usually contains details of the class.
 
 

 

Example

class AB {
// a very simple class
};




 
 

Forward Declarations

Forward declarations allow references to not yet declared classes - for example two classes referring to each other. Of course, you should later provide the actual implementation.

Forward declarations may contain the extends clause, specifying a base class.
 
 

 

Example

class ABC extends AB;
ABC myVal;




 
 

Class Access

Classes are always public. The access keywords (public, protected, private) are not applicable, as class declaration already takes place at the global namespace.

Access of class members is described in the following section.




 
 

Inheritance

Classes can base their implementation on already declared classes. For object oriented programming this mechanism is called "inheritance". PrimeBaseTalk does not support "multiple inheritance".

To inherit all public or protected properties of a class into the currently declared new class, this base class has to get mentioned in the class declaration with the optional 'extends' keyword.
 
 

 

Example

class ABC extends AB {
};
All classes without explicit 'extends' automatically extend the class Object which is provided by the system.




 
 

Final Classes

Classes declared with the "final" keyword are not available for inheritance by another class. This is a very strong measure of protection, you should consider to instead explicitly make the existing methods final.




 
 

Abstract Classes

Classes declared with the "abstract" keyword can not get instantiated by themselves, but only through an inherited class.

Abstract classes don’t only allow, but require at least one abstract member function or procedure.
 
 

 

Example

abstract class AC1 {
        public abstract procedure a() end procedure a;
        public abstract void b();
};
abstract class AC2 {
        /* later */
}; /* illegal */




 
 

Class Redeclaration

It is possible to redeclare an existing class with the same name at any time. This is especially useful in development of interactive systems using the PrimeBase Application Server.

Existing objects survive though - they keep the association with the old class declaration. Preexisting compiled code, with parameter types of procedures and methods, also refers to the old class until recompiled. It won’t follow to redeclared classes.
 
 

 

Note

Although it is possible to supersede a class declaration by redeclaring the class, it is especially not recommended for the system classes.




 
 

Class Members

A member is an entity contained within a class. There are storage members - variables, and executable members - procedures and functions, also commonly called methods.




 
 

Access Operators

The colon ":" is used for any access of class or object members. The operators "." and "->" which are used by other languages for similar purpose have a slightly different meaning:

The "." operator is used to separate table aliases in database expressions from the columns identifiers.

The "->" operator is used for cursor columns.

The "::" operator is unused.




 
 

Static Members

Members declared with the keyword 'static' exist within the class rather than within single instances of that class - the objects.

They are accessible from within objects similarily to non-static members, but variables share the data across all instances.

In the following sample, everything refers to one shared variable "x".
 
 

 

Example

class A {
 static INT x := 1;
 static void PrintX() { print x; }
};
A aObj = new A();
print A:x, aObj:x;
A:PrintX();
aObj:PrintX();

Screen output:


1 1
1
1




 
 

Member Access

Access to the various members of a class is controlled using the modifiers 'private', 'protected' and 'public'.




 
 

13. Objects

Objects are independent instantiations of declared classes. There can be many different instances of the same class, or none.

Each object has its own set of member variables, as declared in the class. Since methods can't get changed, they are efficiently shared between all objects of that class.




 
 

Constructor

To create an instance of the class, the object has to be constructed by the appropriate method.

Similar to other languages, this method has the same name as the class itself. For easier referral, all these creation methods are called "constructor".

The constructor as used to create an object is invoked by a special syntax - the 'new' operator.

In comparison to other object oriented languages the whole object creation process is much simplified to gain maximum speed.

 

Example

class A {
 A() { print "A"; }
};
class B extends A {
 B() { print "B"; }
};
B bb = new B();

Screen output:


B
The order of these calls is not enforced by the language. You may even call to ancestor classes or call parent constructors multiple times.

The call to the parent constructor is super:this() not, super()
 
 

 

Example

class C extends B {
        C()
        {
                print   "C";
                super:this();
                super:super:this();
        }
        C(INT a)
        {
                print   "INT";
                this();
                super:this();
        }
};
C cc := new C();

Screen output:


C
B
A
To call the constructor without explicitly using the created object, you have to prefix the operator new with a colon.
 
 
 

Example

:new C(1);

Screen output:


INT
C
B
A
B

Inherited constructors are still available, even for derived classes.
 
 

 

Example

class A {
 A(INT x) { print x; }
};
class B extends A {
 B() {}
};
:new B(10);

Screen output:


10




 
 

Member Variable Initialization

During class compilation, non-static member variables are also available as static variables. Both kinds of member variables can get initialized during their declaration, or in a later static code block.

These assignments are evaluated only once, on class declaration. On class instantiation into an object, the values are duplicated. This is roughly equivalent to assigning a value to the prototype of a JavaScript class.
 
 

 

Note

If you assign references to non-static member variables, you share the referenced objects between instances of the class. To prevent this you should only preset to $NULL and initialize the members only during the constructor.

 
 
 
 
 

Example

class Testing {
        public /* non-static */ INT x := 1;

        static {
                // the prototype / template gets modified
                x := 2;
        }
};
/* would be a bug:
Testing:x = 3;
 */
print new Testing().x;

Screen output:


2
From now on, the prototype values are not accessible from outside.




 
 

Final Member Variables

Member variables declared with the keyword 'final' are write once - they may even get dynamically initialized from a function result.




 
 

finalize() as Destructor

The finalize() callback function supports additional cleanup task when an object is reclaimed by the garbage collecting mechanism: When all references to an object are lost (reference counting) or when found unaccessible during an explicit garbage collection, an object will get purged.

Similarily to Java, there is no destructor walking through the inheritance tree. Instead the special member function "finalize()" is called. An inherited super:finalize() requires an explicit call.
 
 

 

Note

Instead of throwing an exception as in Java, the garbage collection can get interrupted by introducing a new reachable reference to the object, during the call to finalize().
If the same object is again found by the garbage collection, it receives another finalize(), and so on.

For cycles (multiple objects connected between themselves, but from nowhere else) this may lead to a situation where some objects already got finalized before one new reference is established, breaking the whole step. There is no further notice to these other objects.




 
 

clone()

The clone() member function creates a duplicate of the object. The default implementation just copies the object itself, contained references will point to the same objects. A deep copy must be performed manually.




 
 

getClass()

The getClass() function yields an object representing the class. The only information currently available from class objects is the class name, via the GetName() method.




 
 

Member Procedures and Functions

Procedures declared within a class work the same way as global procedures. In addition you may change the access using the several keywords described for class members.

The separation of parameter list and parameter declaration resembles the abandoned old style of the original K&R C language.

Within class declarations, an alternate modern syntax becomes available for some methods.
 
 

 

Example

class MyClass {
 public static
 void anotherTest(INT x)
 {
        print   x;
 }
};




 
 

Static methods

Methods declared with the 'static' modifier are accessible with and without an instance of the class. They can only access static member variables, though.
 
 
 

Example

MyClass obj := new MyClass();
obj:anotherTest(1);
MyClass:anotherTest(1);

Screen output:


1
1




 
 

Abstract methods

Methods declared with the 'abstract' modifier are only a placeholder within an abstract class, without underlying code. The abstract class can not get instantiated into concrete objects (without inheritance) and has to be declared abstract itself.
 
 
 

Note

An abstract class requires at least one abstract method.
Abstract methods are declared without the statement block. Functions require an additional ";". Procedures still require the END PROCEDURE section.
 
 
 

Example

abstract class AClass {
abstract procedure myProc()
end procedure myProc;
abstract void myFunc();
};




 
 

Overloading

Inside one class multiple function methods may have the same identifier - they are overloaded. The functions must differ in at least one parameter type, though. The several integral types are considered to be the same - there can only be one of them. The same applies also to the different floating point types.
 
 
 

Example

class Over {
static void load(INT a)         { print "INT"; }
static void load(double a)      { print "FLOAT"; }
static void load(VARCHAR a)     { print "VARCHAR"; }
static void load(UNICODE a)     { print "UNICODE"; }
static void load(CHAR a)        { print "CHAR"; }
static void load(MONEY a)       { print "MONEY"; }
static void load(DECIMAL a)     { print "DECIMAL"; }
static void load(BINARY a)      { print "BINARY"; }
static void load(VARBIN a)      { print "VARBIN"; }
static void load(LONGBIN a)     { print "LONGBIN"; }
};
over:load(1);
over:load(SMINT 2);
over:load(INT 0x12345678);
over:load(DOUBLE 1);
over:load(FLOAT 1);
over:load(MONEY 1);
over:load(DECIMAL 1);
over:load("");
over:load(CHAR "");
over:load(VARBIN "");
over:load(BINARY "");
over:load(LONGBIN "");
over:load(UNICODE "");

Screen output:


INT
INT
INT
FLOAT
FLOAT
MONEY
DECIMAL
VARCHAR
CHAR
VARBIN
BINARY
LONGBIN
UNICODE
Overloading similarily works for non-static functions.

Overloading is only allowed for methods with the new function syntax. An attempt to overload a procedure would currently just replace it, later versions of PrimeBaseTalk will yield an error message.

It is not possible to have both static and non-static methods with otherwise similar name and parameters.




 
 

Overriding

Overriding is related to inheritance: A class extends another class and therefor inherits methods from that class. When a method in the new class is declared with the same identifier and parameters as a method in the base class, it is said to override that method.

When an overriden method is invoked on some object reference to the base class, the overriding code gets executed instead.
 
 

 

Example

class Base {
void test() { print "test"; }
};

class OverRide extends Base {
void test() { print "over!"; }
};
Base obj = new OverRide();
obj:test();

Screen output:


over!
Overridden functions won't hide other functions with the same identifier, as inherited from base classes. You need not redeclare and call the inherited functions.
 
 
 

Example

class OverRide extends Over {
static void load(VARCHAR a) { print "ride"; }
};
override:load(DECIMAL 1);
override:load("");

Screen output:


DECIMAL
ride




 
 

Final Methods

Instead of requiring a keyword (C++: virtual) for overridable methods, all methods are overridable by default.

Only methods declared with the 'final' modifier are not available for further overriding in inherited classes. This approach is similar to Java.
 
 

 

Example

class A {
final void ah() { print "ah"; }
};
class B extends A {
void ah() { print "boo"; }
};
B bb = new B();
bb:ah();

Screen output:


Symbol 'ah' is final.
Execution Failed.




 
 

Method Access

The access modifiers for class members have been discussed in the class section.

Method access rights are checked dynamically. Therefor even if you call a public method, a derived class may have overridden that method to more privacy.
 
 

 

Example

class A // implicitly extends Object
{
 private void clone() {}
}
A aa = new A();
Object o = aa;
o = o:clone(); // will fail




 
 

Reflection

Reflection is the self-description of classes and objects by means of some assisting objects.
 
 
 

Note

There is currently no excessive reflection mechanism for class objects.




 
 

$typeof()

The $typeof() function only retrieves the minimum required class for a reference. It yields the same result even if there is currently no object assigned.




 
 

Object.getClass()

Each class is represented by one instance of the "Class" class. The class object is retrieved by the Object method "getClass()".




 
 

Class.getName()

Currently the only information provided by the class object is the original name, available from getName().
 
 
 

Example

Object obj = new INT[100];
print $typeof(obj), obj:getClass():getName();

Screen output:


Object Array

 
 
 

Hint

As a similar concept, the database provides a detailed catalog for the contained structures, and result sets in cursors are also available for inspection using specialized cursor functions.

 
 
 

Hint

Using the "execute from" statement's dynamic compiler, you can evaluate arbitrary expressions which may reference any object member (variable or method) by name.




 
 

14. Arrays

Besides the object oriented programming, PrimeBaseTalk also added arrays to the DAL language.

Arrays are used to store an arbitrary number of similar data type into one object, enumerated by an indexing value. When referring to one slot of the array, the index is enclosed with square brackets.

PrimeBaseTalk allows for two kinds of arrays - indexed with an integer numeric value, or indexed with an arbitrary other primitive value. The differences are described below.
 
 

 

Note

All arrays are instances of the Array class, there is no further inheritance from class Array. Instead, the array objects are checked on assignment to array references.
Array declarations are always specified without the future dimension. Remember that some data types also use the square bracket notation for size specifications - these are always with values.




 
 

Array Methods

The Array class currently provides the following methods:
 
 
 

Example

class Array extends Object {
public final  integer length := 0;
public void setLength(integer size) ...
public generic getSlot(integer index) ...
public void removeSlot(generic value) ...
}




 
 

Numerically Indexed Array

Integral types are interpreted as straight array index.

Numerically indexed arrays are initialized to their maximum size at invokation of operator new. This size is later available from the length field, in read only form. It can also get modified using the SetLength() function.

The index is zero - based, so the valid index values range from 0 to length-1.

Uninitialized entries of arrays (after initial allocation or growing setLength()) contain a special uninitialized mark for primitive data types, or $NULL for reference data types. This is similar to unitialized variables.
 
 

 

Example

integer[] myIntegers = new integer[10];
myIntegers[0] = 10;
print myIntegers[0], $typeof(myIntegers[0]);

Screen output:


10




 
 

Associative Array

It is possible to use several other data types as alternate index.
 

Note

Reference types (objects etc.) are not allowed as associative index.

But they are perfectly legal as value type.
 

Associative arrays should get allocated with a zero length. Non-integer keys will ignore this length limit and create or replace new slots which then are also available for numeric indexing.

Allocation sizes are implicitly maintained by associative access: The capacity in use is then indicated by the length field.

Numerical indexing is possible on associative arrays for both the values (using the [] operator) and the key/index value (using the GetSlot() function).

The removeSlot() function removes a slot, either by integer or by associative index.
 
 

 

Note

It is not recommended to generally mix the array kinds.

The first associative use of an existing array will DOUBLE its memory requirements.
 


 
 
 

Example

VARCHAR[] myArray = new VARCHAR[0];
myArray["key"] = "val";
myArray["x"] = "y";
myArray[1] = "one";
print myArray:length, myArray:getSlot(0), myArray[0],
  myArray:getSlot(1), myArray[1];
myArray:removeSlot(0);
print myArray:length;

Screen output:


2 key val x one
1
The class PBEApplication demonstrates how to to use an associative array for a queue.




 
 

15. Cursors

Cursors are special objects for reading access to whole result sets of database operations.

All cursor objects are straight instances of the cursor class.

Cursors allow the operator "->" for access to their data columns. These are, different from member variables, only valid whenever the cursor is positioned onto a matching record.

Originating in the DAL language, there is also a vast library of global functions and special statements to access various properties of a cursor.
 
 

 

Note

$cursor is not the type identifier, but a predeclared global variable used as default cursor reference.

The clone() method also works for cursors, actually duplicating all the underlying data.

The operator '->' is now allowed for left expressions and permits direct modifications of the current data field.

This is a partial replacement for the $updaterow() function which still remains available. $updaterow() may be useful when modifying consecutive columns.
 
 

 

Example

select 1 as x, "a" as t, time $now() into myCursor for extract;
fetch of myCursor;
printrow myCursor;
myCursor->1 = 2;
$updaterow(myCursor,objname "t", "b", $now() );
printrow myCursor;

Screen output:


1 a 11:56:17.21
2 b 11:56:17.23

Since a cursor is now an object, the $typeof(aCursor) newly yields "Cursor" instead of the previous numeric value 16.




 
 

16. Garbage Collection

PrimeBaseTalk implements multiple methods to find lost objects and reclaim their memory.

One method is called reference counting. When an object lost the last reference from outside, it is reclaimed immediately. Only when multiple objects reference each other in a ring or similar cycle this may fail.

A complete garbage collection examines the whole allocated memory for connections to the contained objects. If an object is not reachable from outside, it is declared garbage and its memory gets reclaimed.

The PrimeBase VM garbage collection is executed automatically whenever a session is closed.




 
 

Explicit call

An explicit call is available to cause a garbage collection.
 
 
 

Example

$collectgarbage();

 
 
 

Note

Since sessions may share objects created during execution of the "initialize.dal" script file, the garbage collection has to get performed across all sessions of the particular PrimeBase Virtual Machine.

Therefor garbage might get collected when this is unexpected.
 



1. Concepts


The Data Access Language

The Data Access Language (DAL) is a super-set of standard SQL (Structured Query Language) and is used to query and manipulate data stored by a DBMS (Database Management Systems).

When using DAL, the programmer need not be concerned about differences in the flavours of SQL spoken by the various DBMS's. The SQL syntax, which is part of DAL, is clearly defined, and any differences between DBMS's is taken care of by the DAL runtime environment.

The PrimeBase™ DBMS from PrimeBase Systems GmbH uses DAL (in addition to ODBC and JDBC) as a native interface. DAL is considerably more useful than SQL, because it includes all aspects of a complete programming language, including: variables, CURSORS, looping and branch constructs and procedure declaration.

In particular, the DAL "print" concept allows the programmer to determine what data retrieved from the DBMS will be returned to the application. SQL does not allow this flexiblity on its own.

For example, SQL requires that all the data retrieved by a SELECT statement be returned to the application. DAL, on the other hand, can scan or manipulate the result of the SELECT and return only the data the application is interested in, given that:

Besides providing a standard API (Application Programming Interface) and a standard language, DAL offers several other advantages:
  1. Depending on the architecture of the system (see below) you can save on network traffic by returning just the data the application requires.
  2. Development can be simplified by manipulating and converting the data in DAL (a language designed for this purpose) rather than in application source code.
  3. The application becomes more flexible and data/representation independent when the data processing is done in DAL.


Sessions - the DAL "runtime" Concept

The DAL subsystem creates a runtime environment called a "session". A DAL program is always executed by a particular DAL session. Using a session ID, the application program can submit a DAL program to the session and initiate execution. Each session has its own global variable space. This includes storage for procedures declared.

Data is returned from the session in the form of an "item stream". An item is an atomic value of a particular data type (INTEGER, DATE, FLOAT, VARCHAR (string), etc.). The DAL "print" statements are used to place items into the stream. Items are retrieved by the application in the order in which they are placed in the stream. For example:

  PRINT 123, "hello world!";

 
This simple DAL program places 2 items in the output stream, the INTEGER value '123', and the string (type is known as VARCHAR in DAL) value 'hello world!'.

Input into the session can only occur in the form of program submittion. This means to pass a value into the session from the application, the application can only do this by submitting and executing a DAL program. For example to get the value '1.41459265' into a DAL session you could execute the following DAL program:

  declare FLOAT pi = 1.41459265;

 
The variable 'pi' is hereby created with the appropriate value and is now available to future DAL programs executing in this session.

 


System Architecture

The standard DAL architecture includes the following components:
 

PrimeBase DAL has a completely different architecture, although it is fully DAL compatible:

Note: The PrimeUtils library (mentioned above) provides one method for accessing PrimeBase DAL and standard DAL.


18. Basic Elements

 


"Complex" Data Types using Cursors

Cursors are like database tables, except that CURSORS are kept in the computer's RAM, not on harddisk. Also there are several built-in functions in PrimeBase DAL that only work on CURSORS, not on database tables (see B.3 Cursor Information Functions, B.4 Cursor Manipulation Functions).

A special syntax of the SELECT statement is used to specify the data types the given or default CURSOR should be initialized with:

<select_where> ::= SELECT <value_list>
                   [ WHERE ( $true | $false ) ]
                   [ INTO <cursor> ]';'

<value_list>   ::= <value_expr> { ',' <value_expr> }
If INTO <cursor> is not specified the default CURSOR variable $cursor will be used.

If WHERE $true is specified the SELECT WHERE statement does not only initialize the CURSORS data type specification, but also adds the values in the <value_list> as the first row.

The FETCH statement is used to make a single row of the specified CURSOR the current row by allowing absolute or relative movement in the rowset of the CURSOR.
Cursor based references (see Cursor Based Reference) are used to access single columns of the current row.
 
 

Example #1

  CURSOR myCursor;

  SELECT INTEGER 1,                /* ID */
         VARCHAR[20] "Name",       /* Name */
         VARCHAR[30] "Surname",    /* Surname */
         INTEGER 1,                /* Age */
         MONEY 1,                  /* Yearly income */
         DATE "01/01/1900"         /* In company since */
         WHERE $false
         INTO myCursor FOR EXTRACT;

  $insertrow(
    myCursor,
    1,
    "Joe",
    "Average",
    26,
    "$95,000",
    "01/01/1996"
  );

  $insertrow(
    myCursor,
    2,
    "Zoe",
    "Example",
    27,
    "$97,000",
    "06/01/1995"
  );

  FETCH FIRST OF myCursor;

  PRINT "ID      :", myCursor->1;
  PRINT "Name    :", myCursor->2;
  PRINT "Surname :", myCursor->3;
  PRINT "Age     :", myCursor->4;
  PRINT "Income  :", myCursor->5;

  PRINT "";

  FETCH NEXT OF myCursor;

  PRINT "ID      :", myCursor->1;
  PRINT "Name    :", myCursor->2;
  PRINT "Surname :", myCursor->3;
  PRINT "Age     :", myCursor->4;
  PRINT "Income  :", myCursor->5;


  Screen output:

  ID      : 2
  Name    : Zoe
  Surname : Example
  Age     : 27
  Income  : $ 97,000.0

  ID      : 1
  Name    : Joe
  Surname : Average
  Age     : 26
  Income  : $ 95,000.0
 
Note:

Note that the second set of data (rowset), which was added last in the sourcecode, was added as row number 1 in the CURSOR, whereas the rowset added first in turn became row number 2 of the CURSOR.
 


 

Example #2

  CURSOR myCursor;

  SELECT INTEGER 1,                /* ID */
         VARCHAR[20] "Joe",        /* Name */
         VARCHAR[30] "Average",    /* Surname */
         INTEGER 26,               /* Age */
         MONEY "$95,000",          /* Yearly income */
         DATE "01/01/1996"         /* In company since */
         INTO myCursor FOR EXTRACT;

  FETCH ABSOLUTE 1 OF myCursor;

  PRINT "ID      :", myCursor->1;
  PRINT "Name    :", myCursor->2;
  PRINT "Surname :", myCursor->3;
  PRINT "Age     :", myCursor->4;
  PRINT "Income  :", myCursor->5;


  Screen output:

  ID      : 1
  Name    : Joe
  Surname : Average
  Age     : 26
  Income  : $ 95,000.0
 

 


Expressions

 

 


Data Type Conversion

DAL supports extensive data type conversion. Conversion to character string values is controlled by the various Format Control Variables mentioned in Appendix A.1 Format Control Variables. <data_type> may specify size and scale as described for the various data types.
<expr_conversion> ::= [ <data_type> ] <expr_factor>

If two data type conversions are to take place directly one after the other, the second data type conversion has to be put in brackets, otherwise an error results.

Example

  PRINT TINYINT ( INTEGER '10' );


  Screen Output:

  10
 

 

Example

  INTEGER personalID = 10;

  VARCHAR personalIDString = VARCHAR personalID;

  PRINT "The ID of this person is " + personalIDString;


  Screen Output:
  The ID of this person is 10
 

 

Example

  INTEGER a = 123;
  INTEGER b = 456;
  VARCHAR c = VARCHAR( b / a );

  PRINT "b / a = " + c;
  PRINT "b / a + c = " + VARCHAR( INTEGER c + INTEGER c );


  Screen output:

  b / a = 3
  b / a + c = 6
 

 


Basic Elements

The basic elements of an expression include a bracketed expression, CURSOR based references, function calls, literal values and variable.
<expr_primary> ::= '(' <expression> ')' |
                   <cursor_based_column_ref> |
                   <function_call> |
                   <literal> |
                   [ ':' ] <var_name>


Function Calls

Call a previously declared procedure. The procedure should return only one value. The number of expressions should be equal to or less than the number of parameters accepted by the procedure. Parameters not passed a value are assigned default values as declared by the procedure.

<var_name> may also be one of the built-in functions ($typeof(), $len(), $format(), $update(), $substr(), etc.).

<function_call> ::= <var_name> '(' [ <expression> ] { ',' [ <expression>] } ')'

Note: Multiple return values are also supported in DAL, see the description of the CALL procedure statement for more details.

Example

  procedure add( operator1, operator2 )
  argument INTEGER operator1;
  argument INTEGER operator2;
  {
        return ( operator1 + operator2 );
  }
  end procedure add;

  INTEGER result = add( 10, 20 );

  PRINT result;


  Screen output:

  30;
 


Cursor-based Reference

A CURSOR based reference refers to values of the current row of a CURSOR rowset. The column may be specified using the column alias, an INTEGER literal representing the column ordinal number or a variable containing one of these two.

If the variable, <var_name>, is of type INTEGER it is the column ordinal number, and if it is of type OBJNAME or VARCHAR it is the column alias.

According to the DAL standard unqualified identifiers (like <column_alias> in the first option of <cursor_based_column_ref>) are first interpreted as DAL variables in the current scope, and then as a CURSOR based reference.

Note: The error message may be misleading here because the last thing the interpreter does is try to interpret the identifier as a column of $cursor.

<cursor_based_column_ref> ::=
               [ [ <cursor> ] '->' ] <column_alias> |
                 [ <cursor> ] '->' <int_literal> |
                 [ <cursor> ] '->' ':' <var_name>


19. Control Statements

 


DBMS Statements

A DBMS, or Database Management System is another word for database server. An open DBMS is a connection to the server. In PrimeBase DAL, a DBMS may be a PrimeBase server, or a gateway to a 3rd party data source. Multiple DBMS's may be open at any particular time.


DESCRIBE DBMS

<describe_dbms_stat> ::= DESCRIBE DBMS [ INTO <cursor> ] ';'

This statement creates a rowset containing a list of DBMS's accessible from the DAL session. In standard DAL this is all DBMS's on the host running the DAL server. In PrimeBase DAL, it can be any PrimeBase server or gateway running on the network.

In PrimeBase DAL, DBMS's listed are all entries in the connection definition file (connect.def) on the client machine. The DBMS name is the alias given in the connection definition. For DAL compatibility, alias names given in the connection definition file should follow the rules for identifiers specified above.

Example

  CURSOR myCursor;

  DESCRIBE DBMS INTO myCursor;

  PRINTALL myCursor;


  Screen Output:

  local 0 YYY YNNNNNYN NYN YYYYYNNNN YYN YYYYYYYYYYYYYYNN YYYYYYYYNNYY YYYYYYYYYY YYYYYYYY  
 
Note:

The screen output of course may vary, depending on the setup and current state of your iDAL session or server console.
 


DESCRIBE OPEN DBMS

<describe_open_dbms_stat> ::= DESCRIBE OPEN DBMS
                                [ INTO <cursor> ] ';'
Create a rowset containing a list of all open DBMS's. The current "in-use" DBMS is listed first. DBMS's are opened using the OPEN DBMS statement.

Example

  CURSOR myCursor;

  DESCRIBE OPEN DBMS INTO myCursor;

  PRINTALL myCursor;


  Screen Output:

  1 local 3114 YYY YNNNNNYN NYN YYYYYNNNN YYN YYYYYYYYYYYYYYNN YYYYYYYYNNYY YYYYYYYYYY YYYYYYYY  
 
Note:

The screen output of course may vary, depending on the setup and current state of your iDAL session or server console.
 


OPEN DBMS

<open_dbms_stat> ::= OPEN <dbms_alias> DBMS
                     [ AS ] USER <char_expr>
                     [ [ WITH ] PASSWORD <char_expr> ]
                     [ OPTION <char_expr> ] ';'
Open a DBMS. The DBMS becomes the currently in-use DBMS. The <dbms_alias> should be one of the DBMS's listed by the DESCRIBE DBMS statement. Note that a user name is required.

If <dbms_alias> does not correspond to a connection alias of a connection definition in the connect.def file, then you need to provide the relevant connection options in the OPTION clause. If a DBMS is opened twice with the same alias, the second open is ignored without error.


CLOSE DBMS

<close_dbms_stat> ::= CLOSE [ <dbms_alias> ] DBMS ';'

Close a currently open DBMS. If is not specified, then the current in-use DBMS is closed.


USE DBMS

<use_dbms_stat> ::= USE <dbms_brand> DBMS ';'

Set the currently in-use DBMS. All operations in which a DBMS (or database) is not explicitly specified now operate on this DBMS.


Database Statements

A database must be opened before any queries or update operations can be performed on the tables of the database.


DESCRIBE DATABASES

<describe_databases_stat> ::=
                  DESCRIBE [ <dbms_alias> ] DATABASES
                    [ [ IN ] LOCATION <character_literal> ]
                    [ INTO <cursor> ] ';'
Return a rowset containing a list of the databases available from the given DBMS. PrimeBase servers ignore the IN LOCATION clause. If no CURSOR is specified the default CURSOR ($cursor) is used.

The format of the rowset returned is as follows:
 
Col#
Data Type Name Description
VARCHAR [31] name The name of the database, to be used in OPEN statement.

Example

  CURSOR myCursor;

  DESCRIBE DATABASES INTO myCursor;

  PRINTALL myCursor;


  Screen Output:

  Master
  Model
  Golfers
 
Note:

The screen output of course may vary, depending on the setup and current state of your iDAL session or server console.
 


DESCRIBE OPEN DATABASES

<describe_open_databases_stat> ::= DESCRIBE OPEN DATABASES
                                     [ INTO <cursor> ] ';'
The statement returns a rowset containing a list of the current open databases. The currently in-use database is listed first.

The structure of the rowset is as follows:
 
Col#
Data Type Name Description
SMINT order Number in open database list.
VARCHAR[31] alias The database alias.
VARCHAR[31] brand The DBMS alias of the open database.
SMINT shrmode The sharing mode used to open the database (1).
SMINT updmode The update mode used to open the database (2).
VARCHAR[31] owner Current owner (creator) name of the user in the database.

NOTES:

(1) The sharing mode is one of the following: 1 = SHARED, 2 = PROTECTED, 3 = EXCLUSIVE.

(2) The update mode is one of the following: 1 = READONLY, 2 = UPDATE, 3 = SCROLLING, 4 = EXTRACT.

Example

  CURSOR myCursor;

  DESCRIBE OPEN DATABASES INTO myCursor;

  PRINTALL myCursor;


  Screen Output:

  0 golfers PrimeBase 2 2
 
Note:

The screen output of course may vary, depending on the setup and current state of your iDAL session or server console.
 


OPEN DATABASE

<open_database_stat> ::=
            OPEN [ <dbms_alias> ] DATABASE
            [ <database_name> ]
            [ ALIAS <database_alias> ]
            [ [ IN ] LOCATION <char_expr> ]
            [ [ AS ] USER <char_expr> [ [ WITH ] PASSWORD
              <char_expr> ] ]
            [ FOR [ <shared_mode> ] [ <access_mode> ] ] ';'

<shared_mode> ::= SHARED | PROTECTED | EXCLUSIVE

<access_mode> ::= READONLY | UPDATE
Open a database on the specified DBMS. If not DBMS is specified, the currently in-use DBMS is assumed. The <database_name> is an identifier, but should be specified as a string literal for DAL compatibility. The PrimeBase server requires that a database name be given. The newly opened database becomes the currently in-use database.

If no alias is specified, the database name is used as the alias. IN LOCATION, USER and PASSWORD clauses are ignored by the PrimeBase server, but may be used by certain gateways (for example the ODBC gateway). For the PrimeBase server the user and password must be specified in the OPEN DBMS statement (or in the DBInit() API call).

The PrimeBase server currently only supports the PROTECTED shared mode, and the UPDATE access mode.

NOTES:

(1) The sharing mode is one of the following: 1 = SHARED, 2 = PROTECTED, 3 = EXCLUSIVE.

(2) The update mode is one of the following: 1 = READONLY, 2 = UPDATE, 3 = SCROLLING, 4 = EXTRACT.

Example

  OPEN DATABASE golfers;
 


CLOSE DATABASE

<close_database_stat> ::= CLOSE DATABASE [ <database_alias> ] ';'

Close the specified database, or the currently in-use database, if <database_alias> is omitted. Do not assume which database is in-use of closing the currently in-use database. Use the USE DATABASE command to set the currently in-use database.

Example

  CLOSE DATABASE golfers;
 


USE DATABASE

<use_database_stat> ::= USE DATABASE <database_alias> ';'
Set the currently in-use database. This database is used when no other database is explicitly specified. An unqualified table in a SELECT statement will be assumed to be in this database. The SELECT will be sent to the DBMS on which this database resides

Example

  USE DATABASE golfers;
 


DESCRIBE TABLES

<describe_tables_stat> ::=
                       DESCRIBE TABLES [ [ OF ]
                         <db_alias>] [ INTO <cursor> ] ';'
Return a rowset containing a list of the tables of the specified database. If <db_alias> is omitted then the currently in-use database is assumed. The format of the rowset returns is as follows:
 
Col#
Data Type Name Description
VARCHAR[31] name Table name.
VARCHAR[1] type Either 'T' for table or 'V' for view.
BOOLEAN ordered $true if the table is ordered.
SMINT colcnt Number of simple (non-composite) columns.
INTEGER rowcnt Number of rows ($null if unknown) (1).
SMINT parentcnt Parent count (2).
SMINT childcnt Child count (2).
VARCHAR[31] title The title of the table.
VARCHAR[255] remarks Comment about the table (3).
10 
VARCHAR[255] owner The table owner (in PrimeBase, the creator name).

NOTES:

(1) PrimeBase always returns $null here in order to avoid accessing the database. PrimeBase build the resulting rowset from data already held in RAM.

(2) Parent & child counts are part of a DAL feature are not supported by PrimeBase.

(3) Comments on tables and views are stored in the System.SysObjects table, but this information is not returned here.

Example

  CURSOR myCursor;

  DESCRIBE TABLES golfers INTO myCursor;

  PRINTALL myCursor;


  Screen Output:

  Clubs T $false 9 0 0 9 $null $null Common
  Competitions T $false 9 0 0 9 $null $null Common
  Courses T $false 7 0 0 7 $null $null Common
  Golfers T $false 11 0 0 11 $null $null Common
  GolfersAmateurs V $false 6 0 0 6 $null $null Common
  Results T $false 7 0 0 7 $null $null Common
  Scores T $false 8 0 0 8 $null $null Common
 


DESCRIBE COLUMNS

<describe_columns_stat> ::=
                      DESCRIBE COLUMNS [ OF ] <table_ref>
                        [ INTO <cursor> ] ';'
This statement returns a rowset containing a list of the columns of a particular table. If a database is not specified in the <table_ref>, then the currently in-use database is used. The table must be accessible to the user. The format of the rowset returned is as follows:
 
Col#
Data Type Name Description
SMINT colnr The column number.
SMINT level Column level number (2).
VARCHAR[31] name The column name.
SMINT type The DAL type of the column.
SMINT len The maximum length of column values.
SMINT places Columns DECIMAL places (scale), for DECIMAL types only.
BOOLEAN nullsok $true if null values are allowed on the column.
BOOLEAN groupcol Group column (2).
9
SMINT parentnr Parent column number (2).
10 
SMINT occurs Number of occurences.
11 
SMINT occdep Occurs depending in column (2).
12
BOOLEAN updtok $true if column can be updated.
13 
VARCHAR[31] title Column title.
14 
VARCHAR[255] remarks Column remarks (3).

NOTES:

(1) Column numbers always begin at 1. Columns are numbered in the order they are declared.

(2) Although PrimeBase supports composite columns, it does not support the hierarchical group columns specified by DAL. In PrimeBase, any particular simple column can be a member of any number of composite columns, as a result, the PrimeBase composite columns cannot be mapped to the DAL group column concept.

(3) PrimeBase supports comments on columns (and these are stored in the System.SysObjects table), however the data is not returned by this command. This ensures that getting the basic information on columns in PrimeBase is a fast and cheap operation.
 
 

Example

  CURSOR myCursor;

  DESCRIBE COLUMNS Golfers INTO myCursor;

  PRINTALL myCursor;


  Screen Output:

  1 0 ID 3 4 0 $false $false 0 0 0 $true
  2 0 SurName 12 55 0 $false $false 0 0 0 $true
  3 0 FirstNames 12 55 0 $false $false 0 0 0 $true
  4 0 Title 9 10 0 $true $false 0 0 0 $true
  5 0 Gender 9 1 0 $false $false 0 0 0 $true
  6 0 Nationality 12 55 0 $true $false 0 0 0 $true
  7 0 DateOfBirth 6 4 0 $true $false 0 0 0 $true
  8 0 Status 9 8 0 $true $false 0 0 0 $true
  9 0 Handicap 2 2 0 $true $false 0 0 0 $true
  10 0 MemberOfClub 3 4 0 $true $false 0 0 0 $true
  11 0 Earnings 11 12 2 $true $false 0 0 0 $true
 

 


20. Basic Statements

 


Variable Manipulation



DECLARE

<declare_stat> ::= [ DECLARE [ GLOBAL ] ] <data_type>
                     <decl_var> { ',' <decl_var> } ';'

<decl_var>     ::= <var_name> [ '=' <expression> ]
Declare a DAL variable.

<var_name> may also begin with the '$' character.

If the GLOBAL keyword is not specified, variables are declared in the current scope, either global (if issued in the outer block), or local to a procedure. If the global keyword is specified variables are always declared globally.

Note that <decl_var> cannot include the initialization clause when declaring a CURSOR.

The same name space is shared for both outer-block variable names and procedure names. A DECLARE and DECLARE PROCEDURE statement for an outer block automatically re-declares any procedure or outer block variable with the same name.

If an initial value is present, an outer block variable is initialized at declaration; a local variable is re-initialized on each procedure entry.

On return from a procedure, local variables are undeclared. Rowset associated with local CURSOR variables are also de-selected.

If an initial value is not specified, the variable remains uninitialized and produces an error if it is used in an expression before a value is assigned.

A DECLARE statement takes priority over any previous declaration for a variable of the same name within the same block.

Example #1

  INTEGER intA = 1,
          intB = 2,
          intC,
          intD,
          intE,
          intF = 3,
          intG = 4;

  VARCHAR myStringA =
          "Just a test.",
           myStringB,
           myStringC = "Yes, really just a test.";
 

 

Example #2

  procedure testA()
  {
    /*
    ** The following command declares the global VARCHAR
    ** variable "myString", overwriting any other globally
    ** declared variable of that name.
    */

    DECLARE GLOBAL VARCHAR myString =
      "I have been declared and initialized in testA().";
  }
  end procedure testA;

  procedure testB()
  {
    /*
    ** The following command declares the local VARCHAR
    ** variable "myString". As it is declared locally, it
    ** it does NOT overwrite/re-declare any other possibly
    ** already declared global variable of the same name
    */

    DECLARE VARCHAR myString =
      "I have been declared and initialized in testB().";
  }
  end procedure testB;



  /*
  ** We undeclare the variable myString here in order to
  ** avoid side-effects with already declared variables,
  ** etc.
  */

  UNDECLARE myString;

  testA();

  PRINT myString;

  testB();

  PRINT myString;


  Screen output:

  I have been declared and initialized in testA().
  I have been declared and initialized in testA().
 


UNDECLARE

<undeclare_stat> ::= UNDECLARE <var_name> { ',' <var_name> } ';'

Undeclare a local or global DAL variable.

Warning

This function is not properly implemented in earlier versions of PrimeBase DAL, as it would - with a single UNDECLARE statement in a procedure - undeclare local and global variables alike. This has been corrected in version 3.5 of PrimeBase DAL.

The correct behaviour is to undeclare a local variable, if one by that name exists, and if not, undeclare a global variable, if one by that name exists.

  INTEGER myInt = 1;

  procedure test()
  {
    /*
    ** Declare a local variable with the same name as the
    **globally declared variable.
    */

    INTEGER myInt = 2;


    /*
    ** Undeclare the local variable again.
    */

    undeclare myInt;


    /*
    ** Try to print the value of the global variable
    **
    ** We get an error in PrimeBase DAL of versions prior
    ** to version 3.5 here, similar to this one:
    **
    **   "ERROR: -10004 (0) : "API"@client, line 32: No
    **   such symbol'myInt'."
    **
    ** In PrimeBase DAL 3.5 the value of the global
    ** variable myInt is printed
    */

    PRINT myInt;
  }
  end procedure test;

  test();

  PRINT myInt;


  Screen output:

  1
  1
 

 

Example

  INTEGER intA = 1,
          intB = 2,
          intC,
          intD,
          intE,
          intF = 3,
          intG = 4;

  UNDECLARE intA,
            intB,
            intC,
            intD,
            intE,
            intF,
            intG;
 


SET

<set_stat> ::= [ SET ] <var_name> '=' <expression> ';' |
               [ SET ] <var_name>'++' ';' |
               [ SET ] <var_name>'--' ';'
Assign a value to a DAL variable. '++' may be used to increment, and '--' may be used to decrement a variable.

The expression may contain literals, variables, cursor-based column references, as well as arithmetic and comparison operators. Sub-queries and aggregate functions are not permitted.

The data type of the expression will be automatically converted to that of the <var_name>, if this is possible, according to the data type conversion rules.

A NULL expression value assigns the NULL value to the variable.

Example

  INTEGER myInteger;

  SET myInteger = 1;

  myInteger++;

  PRINT myInteger;


  Screen output:

  2
 


Execute Statements



EXECUTE FROM

<execute_from_stat> ::= EXECUTE [ FROM ] <char_expr> ';'
This statement compiles and executes the string value (<char_expr>) assuming it contains a normal DAL program.

This statement can be used to build DAL statements (for example a SELECT statement), or programs at runtime.

Example

  INTEGER myInteger = 10;

  VARCHAR myString = "5 + 5 = ";



  /*
  ** A little control output of what will be sent to
  ** the PrimeBase DAL interpreter
  */

  PRINT "Control output:";

  PRINT
    "  myString = myString + '" +
    VARCHAR( myInteger ) + "';";

  PRINT "";

  EXECUTE FROM
    "  myString = myString + '" +
    VARCHAR( myInteger ) + "';";

  PRINT myString;


  Screen output:

  Control output:
    myString = myString + '10';

  5 + 5 = 10
 

 


EXECUTE IMMEDIATE

<exec_immediate_stat> ::=
              EXECUTE ( <immediate_in_dbms> |
                <exec_in_dbms> ) ';'

<immediate_in_dbms>   ::=
              IMMEDIATE <char_expr> [ IN ] <dbms_alias>

<exec_in_dbms>        ::=
              [ [ IN ] <dbms_alias> ] <char_expr>
The EXECUTE IMMEDIATE statement is used to submit a statement to the DBMS directly. The string is a native command of the specified DBMS. If no DBMS is specified the currently in-use DBMS is assumed. This statement is most often used to do data definition in the native language of the DBMS.

In PrimeBase DAL this statement has the same effect as the normal EXECUTE FROM statement above, if the DBMS is PrimeBase. If the DBMS is a PrimeBase Gateway, the statement is passed on to the back-end by the gateway.


EXECUTE FILE

<execute_file_stat> ::=
                     EXECUTE FILE <char_expr>
                       [ [ IN ] LOCATION <char_expr> ] ';'
Load, compile and execute a DAL program stored in a text file. The name of the text file is specified in the first <char_expr>, and the location (path) in the host file system in the second <char_expr>.

There is a limit to the length of each line in the file; This limit is 512 characters.

Note that in PrimeBase DAL the file executed resides on the client machine. If no location is specified PrimeBase DAL searches for the file. The search path is as follows:

  1. <cwd>
  2. <cwd> + 'Setup'
  3. <csw> + 'Setup' + 'Scripts'
  4. <gwd>
  5. <gwd> + 'PrimeBase Setup'
  6. <gwd> + 'PrimeBase Setup' + 'Scripts'
Where <cwd> = current working directory, and <gwd> = global working directory. The location of the global working directory depends on the platform: On the Macintosh it is the system 'Preferences' folder. Under Windows it is the Windows installation directory, and under UNIX it is the location specified by the SNAPHOME environment variable.

Note also that the folder 'PrimeBase Setup' is called 'PB-SETUP' under Windows, and 'primebase.setup' under UNIX.

Since of PrimeBase version 3.5 PrimeBase DAL additionally looks for a file with a '#' character added the left side of the given filename.

See 23. Encrypting PrimeBase DAL files for more details on how to encrypt PrimeBase DAL files.

Example

  EXECUTE FILE "My own DAL Definitions.dal"
  IN LOCATION  "C:\Scripts";
 

 


Print Statements

All data returned to the application from the DAL runtime environment must be explicitly "printed" using one of the PRINT statements below. Values printed are placed in an item stream where they may be collected by the application in the order printed. Execution of the DAL program will be suspended if the item queue becomes full, and the application does not retrieve or discard the items.

All values printed are modified (converted) according to the current PRINTCTL settings.


PRINT

<print_stat> ::= PRINT <expression> { ',' <expression> } ';'
This is the most general form of the print statements. It returns a string of values of any type to the application.

The statement creates a single output row with as many columns as there are expressions. The data type of the column is the data type of the corresponding expression. See the PRINTCTL statement for details as to output data type mapping.

A maximum of 256 expressions can appear in the expression list.

Example

  PRINT "Hello world.";

  PRINT "10" + " + " + "20" + " =", 10 + 20;


  Screen output:

  Hello world.
  10 + 20 = 30
 


PRINTALL

<printall_stat> ::= PRINTALL [ <cursor> ] ';'
Send the entire contents of a CURSOR to the application. Values are printed from the first row to the last row, and from column 1 to column n (number of columns). $cursor is used if no CURSOR is specified.

The entire contents of the rowset are sent to the item stream. For each row of the rowset and from the first column to the last each item placed in the output stream. The items are converted if necessary according to the current PRINTCTL settings. If the rowset is empty, no data will be printed.

The last item in each row is returned with the end-of-row flag set.

Example

  CURSOR myCursor;

  SELECT INTEGER 1,                /* ID */
         VARCHAR[20] "Name",       /* Name */
         VARCHAR[30] "Surname",    /* Surname */
         INTEGER 1,                /* Age */
         MONEY 1,                  /* Yearly income */
         DATE "01/01/1900"         /* In company since */
         WHERE $false
         INTO myCursor FOR EXTRACT;

  $insertrow(
        myCursor,
        1,
        "Joe",
        "Average",
        26,
        "$95,000",
        "01/01/1996"
    );

  $insertrow(
        myCursor,
        2,
        "Zoe",
        "Example",
        27,
        "$97,000",
        "06/01/1995"
  );

  FETCH FIRST OF myCursor;

  PRINTALL myCursor;


  Screen output:

  2 Zoe Example 27 $ 97,000.0 06/01/1995
  1 Joe Average 26 $ 95,000.0 01/01/1996
 


PRINTCTL

<printctl_stat> ::= PRINTCTL <conv_spec>
                      { ',' <conv_spec> } ';'

<conv_spec>     ::= <int_expr> [ '=' <int_expr> ] |
                    <int_expr> '=' <proc_name> |
                    <proc_name>
Specify how the data of each type is converted when printed to the output item stream. All <int_expr> values must return an INTEGER value representing a DAL data type. These values are also the values of the DAL constants: $INTEGER, $SMINT, $VARCHAR, etc. Alternatively, a procedure name may be specified which converts the items of a particular data type.

If an '=' sign appears in a <conv_spec>, then the first value is the source data type and the second value (or procedure) is the destination data type. If a single expression (or procedure name) is specified, the source data type is implied by the position of the <conv_spec>. PRINTCTL 0 sets conversion for all data types to the default, which is to convert all values to VARCHAR.

There is a special code - namely 0, (zero), which specifies mapping to VARCHAR (type code 12) and where NULL values are returned as the text string "$NULL". For convenience´ sake, the statement:

PRINTCTL 0,0,0,0,0,0,0,0,0,0,0,0:

can be abbreviated to:

PRINTCTL 0;

If the name of a procedure appears, the DAL subsystem is directed to call the named procedure before sending it back to the client application. The procedure should be declared to take a single argument and to produce a single value. The DAL subsystem sends the return value from the procedure back to the client application.

The PRINTCTL governs output data mapping until another PRINTCTL statement supersedes it.

This statement affects the actual data returned by the API to the client application.

The PRINTINFO reports the DAL data type of each column in a rowset - that is the unmapped types. If the contents of a rowset are printed with a PRINTALL or PRINTROW statement, however, the client application will map the received data.

The PRINTCTL statement must be used before values can be received in BINARY form.

Example

  /*
  ** Change print control to convert to INTEGER values to
  ** FLOAT values
  */

  PRINTCTL $integer = $FLOAT;

  PRINT "INTEGER value ""10"" as FLOAT:", INTEGER( "10" );


  /*
  ** Change print control to convert to INTEGER values to
  ** DECIMAL values
  */

  PRINTCTL $integer = $DECIMAL;

  PRINT "INTEGER value ""10"" as DECIMAL:",
         INTEGER( "10" );


  /*
  ** Reset print control to default again
  */

  PRINTCTL 0;


  Screen output:

  INTEGER value "10" as FLOAT: 10.000000
  INTEGER value "10" as DECIMAL: 00000000000000000010
 

 

Warning

PRINTCTL 0; is default, which means that all values printed gets converted into items of type VARCHAR.


PRINTF

<printf_stat> ::= PRINTF '(' <expression>
                    { ',' <expression> } ')' ';'
PRINTF stands for "print formatted". It results in the printing of one VARCHAR value. The first expression is the format string, and the following expressions are the values to be embedded in the string at the positions, and in the form indicated by the format string.

This statement creates a single output row containing a single output column of type VARCHAR. This is, of course, subject to PRINTCTL - output data mapping.

This statement is especially useful for client applications that have little or no tabular-processing capability and that are text-oriented.

The format string has the same form as the format string in the $format() built-in function. The VARCHAR item returned has the format flag set for the application.

The format string (<fmtstr>) may consist of three formatting specifications. They are interpreted in a left to right order. These formatting specifications are:


 

Special Characters: Two-Character Sequences

 
\b generates a backspace character.
\n generates a newline character.
\r generates a return character.
\t generates a tab character.
\\ generates a backslash character.
\% generates a percent character.

 

Conversion Specifications

The percent character (%) introduces all conversion specifications and is concluded by a conversion character (<fmtchar>). See the syntax below:
<conversion_spec> ::= '%' [ '-' ] [ <width> ]
                        ['.' <precision> ] <fmt-CHAR>
Minus Sign
The minus sign, when included, justifies the data to the left upon conversion. Otherwise the data is right justified. Justification is, of course, relative to width and precision - which specify the width of the converted data string.
Width and Precision
Specify the width and precision of the converted data string in the fields <width> and <precision>. Remember to include the period (.) preceding the value you give as the <precision>. You can specify width and precision as the special character, asterisk (*). Then the numeric value for the parameter is taken from the next data argument to the $format() call.

 

Format Character Values

 
d or u This is an INTEGER data argument. It converts to a string, whose value is given in the INTEGER literal format.
p This is a DECIMAL or MONEY data argument. It converts to a string, whose value is given in the DECIMAL literal format.
c This is a single character data argument. It copies the character.
f This is a floating-point data argument. It converts to a string, whose value is given in the floating-point literal format.
s This is a string data argument. It copies to a string, according to the other conversion parameters.
x or X This converts its data argument to a sequence of characters that are the unsigned hexadecimal representation of the data item. A capital X results in uppercase hex digits (A-F); a lowercase x results in lowercase hex digits (a-f).

 

When the <fmtchar> is preceded by:

 
^ (caret) the output field contents are to be converted to uppercase characters.
! (exclamation point) the output field contents are to be converted to lowercase characters.

For every call to the $format() or the PRINTF procedure, the number of conversion specifications in <fmtstr> and the number of data arguments must match. If the number of data arguments is less than the number of conversion specifications, then errors may result.


 

Example

  /*
  ** Output "Hello" in hex format with lower alpha digits
  ** and "world" in hex format with upper alpha digits
  */

  PRINTF( """Hello"" in hex = %x", "Hello" );

  PRINT "";

  PRINTF( """world"" in hex too == %X", "world" );


  Screen output:

  "Hello" in hex = 48656c6c6f

  "world" in hex too == 776F726C64
 


PRINTINFO

<printinfo_stat> ::= PRINTINFO [ <cursor> ] ';'
Print a table of information, one row for each column in the CURSOR, which describes the rowset of the CURSOR. The rows returned by this statement have the following format:
 
Col#
Data Type Name Description
SMINT number The rowset column number.
VARCHAR[255] name The name of the column.
SMINT type The DAL type of the column
SMINT length The maximum length of column values.
SMINT scale The DECIMAL scale of the column.
SMINT width The maximum length when converted to a VARCHAR value.

Example

  CURSOR myCursor;

  SELECT INTEGER 1,                          /* ID */
         VARCHAR[20] "Joe",        /* Name */
         VARCHAR[30] "Average",    /* Surname */
         INTEGER 26,                         /* Age */
         MONEY "$95,000",          /* Yearly income */
         DATE "01/01/1996"         /* In company since */
         INTO myCursor FOR EXTRACT;

  FETCH FIRST OF myCursor;

  PRINTINFO myCursor;


  Screen output:

  1 expr1 3 4 0 11
  2 expr2 12 20 0 20
  3 expr3 12 30 0 30
  4 expr4 3 4 0 11
  5 expr5 11 10 0 15
  6 expr6 6 4 0 10
 


PRINTROW

<printrow_stat> ::= PRINTROW [ <cursor> ] ';'
Print all items of the current row of the given CURSOR. The default CURSOR, $cursor, is used if no CURSOR is specified. Items are sent to the application in column order, 1 to n (number of columns). If the current row is set to before the rowset, or after the rowset, or if the CURSOR contains no rowset, an error occurs.

This statement creates a single output row with as many columns as there are columns in the current cursor. The data type is the type of the corresponding column of the rowset. See PRINTCTL for details as to output data mapping.

Use the PRINTINFO statement to find out information about the names and data types, and other related information, of the columns of a rowset

Example

  CURSOR myCursor;

  SELECT INTEGER 1,                /* ID */
         VARCHAR[20] "Joe",        /* Name */
         VARCHAR[30] "Average",    /* Surname */
         INTEGER 26,               /* Age */
         MONEY "$95,000",          /* Yearly income */
         DATE "01/01/1996"         /* In company since */
         INTO myCursor FOR EXTRACT;

  FETCH ABSOLUTE 1 OF myCursor;

  PRINTROW myCursor;


  Screen output:

  1 Joe Average 26 $ 95,000.0 01/01/1996
 


Error Handling



ERROR

<error_stat> ::= ERROR [ <prim_err> [ <sec_err>,
                                      <prim_item>,
                                      <sec_item>,
                                      <message> ] ] ';'
This command can be used to raise your own errors.

Example

The following raises the error just caught with ERRORCTL 1:
  ERROR $sqlcode;
 


ERRORCTL

<errorctl_stat> ::= ERRORCTL <int_expr> ';'
The statement is used to control whether execution of the DAL program continues or stops after a runtime error occurs. By default the execution of the DAL program stops when a runtime error occurs.

To prevent execution from terminating use non-zero value for <int_expr>, for example:

ERRORCTL 1;
To restore the default behaviour use:
ERRORCTL 0;
When trapping errors check the global variable $sqlcode to determine whether an error has occured. $sqlcode is set to the "primary error code" of the error. Errors are indicated by negative numbers, warnings are positive numbers and zero indicates no error.

Note though that only data-manipulation set $sqlcode to zero signalling successful execution, so usually you want to set $sqlerror to zero manually to get a meaningful result in any case.

Example

  /*
  ** Switch error control off, just in case it has been
  ** enabled previously
  */

  ERRORCTL 0;


  /*
  ** Undeclare myInteger, just in case it has been
  ** previously defined
  */

  UNDECLARE myInteger;


  /* Switch error control on, because we are going to do
  ** something that "might" (actually in this example it
  ** really would) cause an error
  */

  ERRORCTL 1;


  /*
  ** Set system variable $sqlcode to 0 to ensure we don't
  ** use the currently stored value in it
  */

  $sqlcode = 0;


  /*
  ** Attempt to print a variable that has not been declared
  */

  PRINT myInteger;


  /*
  ** Let's see what $sqlcode indicates
  */

  PRINT $sqlcode;


  Screen output:

  -10004
 


21. Program Constructs



Conditional/Branch Statements



IF

<if_stat> ::= IF '(' <bool_expr> ')' <statement>
              [ ELSE <statement> ]
The value of <bool_expr> should be a BOOLEAN value, however will be automatically converted to a BOOLEAN value if not. It is, however, best to always use an operator that explicitly returns a BOOLEAN value to avoid an unexpected result, due to $null values and DAL 3-valued logic. For example, use IF (i == 9), rather than IF (i).

Example

  INTEGER myIntegerA = 1;

  INTEGER myIntegerB = $null;

  IF ( myIntegerA < 1 )
  {
        PRINT "myIntegerA is less than 1";
  }
  ELSE
  {
        PRINT "myIntegerA is greater or equal to 1";
  }

  IF ( myIntegerB IS NULL )
  {
        PRINT "myIntegerB is NULL";
  }
  ELSE
  {
        PRINT "myIntegerB is not NULL";
  }


  Screen output:

  myIntegerA is greater or equal to 1
  myIntegerB is NULL
 


SWITCH

<switch_stat> ::= SWITCH '(' <expression> ')' '{'
                  { CASE <literal> ':' <statement_list> }
                  [ DEFAULT ':' <statement_list> ] '}'
The SWITCH statement operates on all DAL data types (including CHAR and VARCHAR). Execution begins at the first matching literal value, and continues till the end of the SWITCH statement or until the first BREAK statement. A <statement_list> includes zero, one or many statements.

The DEFAULT case may appear only once in the SWITCH statement, but can be placed anywhere near to other CASEs.

As mentioned before, the CASEs themselves do not alter flow of program; flow continues sequentially until all statements have been executed. Interrupt this flow with BREAK, RETURN, GOTO or similar statements. The most common to use is the BREAK statement.

Data types are converted for comparison, just as they would be for a comparison expression (<expression> == <comparison_expr>).

The system variable $switch is set to the value of <expression> for the last SWITCH statement that is executed when the body of the SWITCH statement is entered.

Example

  INTEGER myInteger = 3;

  SWITCH( myInteger )
  {
        CASE 1:
        {
                PRINT "myInteger is 1.";
                break;
        }

        CASE 2:
        {
                PRINT "myInteger is 2.";
                break;
        }

        DEFAULT:
        {
                PRINT "myInteger is neither 1 nor 2.";
        }
  }


  Screen output:

  myInteger is neither 1 nor 2.
 


LABEL

<label_stat> ::= LABEL <var_name> ':' <statement>
The LABEL statement indicates a position in code that can be used in the GOTO statement.


GOTO

<goto_stat> ::= GOTO <var_name> ';'
Unconditionally jump to the statement following the LABEL statement with the given <var_name>. The label must be in the current scope (procedure or global). Jumping into and out of loops is permitted, but not recommended (use BREAK to exit a loop).

Note that in standard DAL the GOTO statement is not permitted in the outer block.

A GOTO statement may jump out of a FOR, WHILE, DO, or SWITCH statement to break the flow of control. It is not recommended to jump into the middle of a FOR, WHILE, or DO loop, or a SWITCH statement.

Example

  GOTO ComeToMe;

  PRINT "A";

  LABEL ComeToMe:

  PRINT "B";


  Screen output:

  B
 


BREAK

<break_stat> ::= BREAK ';'
Use the statement to exit the current loop (WHILE, DO, FOR or FOR EACH statements), or SWITCH statement.

When a BREAK appears within nested loops, or SWITCH statements, it will cause the processing of the innermost loop (or rather, SWITCH) to break. It is not possible to cause a multilevel break in a single statement.

Example

  INTEGER myCounter;

  FOR ( myCounter = 0; myCounter < 10; myCounter++ )
  {
    IF ( myCounter == 5 )
    {
      PRINT "myCounter ==", myCounter, "exiting for loop";
      BREAK;
    }
  }


  Screen output:

  myCounter == 5, exiting for loop
 


CONTINUE

<continue_stat> ::= CONTINUE ';'
This statement interrupts the flow of the body of a WHILE, DO, FOR, FOR EACH statement, thus causing the remainder of the current iteration of the WHILE, DO, FOR, FOR EACH statement to be skipped.

Execution continues with the loop control expression (for WHILE or DO) or with the loop-reinitialiser (for FOR), or with fetching of the next row (for FOR EACH).

Example

  INTEGER myCounter;

  FOR ( myCounter = 0; myCounter < 3; myCounter++ )
  {
    IF ( myCounter == 1 )
    {
      CONTINUE;
    }

    PRINT "myCounter ==", myCounter;
  }


  Screen output:

  myCounter == 0
  myCounter == 2
 


Iteration Statements



WHILE

<while_stat> ::= WHILE '(' <bool_expr> ')' <statement>

If <bool_expr> is not a BOOLEAN value it will be converted. It is, however it is recommended to use operators that explicitly return BOOLEAN values. For example, write WHILE (i > 0) rather than WHILE (i), unless it is a BOOLEAN value.

The <statement> is usually a compound statement.

Remember that if the <expression> is evaluated as FALSE the first time, then the statement will not be executed at all.

An expression that evaluates to NULL is FALSE.

Example

  INTEGER myCounter = 0;

  WHILE ( myCounter < 3 )
  {
    PRINT "myCounter ==", myCounter;
    myCounter = myCounter + 1;
  }


  Screen output:

  myCounter == 0
  myCounter == 1
  myCounter == 2
 


DO

<do_stat> ::= DO <statement> WHILE '(' <bool_expr> ')' ';'
This statement executes repeatedly and terminates through a post-test, when this evaluates to FALSE. With each repetition, the DO statement executes a specified statement, and then evaluates the specified expression. If the expression evaluates as TRUE, then the cycle of statement execution followed by expression evaluation continues. When the expression eventually evaluates to FALSE, the flow then passes on to the next statement after the DO statement.

<statement> is always executed once, even if <bool_expr> produces a FALSE result the first loop.

An expression that evaluates to NULL is FALSE.

Example

  INTEGER myCounter = 0;

  DO
  {
    PRINT "myCounter ==", myCounter;
    myCounter = myCounter + 1;
  }
  WHILE ( myCounter < 3 );


  Screen output:

  myCounter == 0
  myCounter == 1
  myCounter == 2
 


FOR

<for_stat> ::= FOR '(' [ <set_var> ] ';' [ <bool_expr> ] ';'
                 [ <set_var> ] ')'
                 <statement>

<set_var>  ::= <var_name> '=' <expression> |
               <var_name> '++' | <var_name> '--'
This statement performs repetitive execution initialized by an assignment, causing an expression to be evaluated, which if resulting as TRUE allows the specified statement to be executed. Then a loop re-initialization assignment is called, and the whole process begins again. This cycle continues until the expression evaluates to FALSE. Flow then passes on to the next statement after the FOR statement.

The initialization assignment <set_var> is always executed exactly once. If <bool_expr> produces a FALSE result at the first evaluation, then <statement> and the second initialization assignment <set_var> will never be evaluated.

Example

  INTEGER myCounter;

  FOR ( myCounter = 0; myCounter < 3; myCounter++ )
  {
    PRINT "myCounter ==", myCounter;
  }


  Screen output:

  myCounter == 0
  myCounter == 1
  myCounter == 2
 


FOR EACH

<for_each_stat> ::= FOR EACH [ <cursor> ] <statement>
Loop through all rows in a CURSOR, from the first to the last. The current row is incremented each time through the loop. The columns values of the current row may be accessed using the "cursor reference operator", '->'. If the CURSOR was selected FOR EXTRACT, or FOR SCROLLING, then the FOR EACH loop may be used more than once. The specification of a CURSOR is optional. If omitted, the default CURSOR ($cursor) will be used. If the CURSOR contains no row set, and error will occur.

The CONTINUE statement can start the next loop iteration prematurely.

If the retrieval of any row in the rowset fails, the FOR EACH iteration is terminated with an error. A BREAK statement can also prematurely terminate a FOR EACH iteration statement.

Warning

Because $deleterow() changes the current row of the cursor to the next row and FOR EACH doing the exact same at the end of <statement>, this leads to single rows being skipped over!

A workaround to this behavior is issuing a FETCH PREVIOUS OF <cursor> directly after calling $deleterow().
 


 

Example

  CURSOR myCursor;

  SELECT * FROM Golfers INTO myCursor FOR EXTRACT;

  FETCH FIRST OF myCursor;

  FOR EACH myCursor
  {
    PRINT myCursor->3,
          myCursor->2,
          "was born",
          myCursor->7;
  }


  Screen output:

  Buzz Bee was born 10/01/1960
  Tumble Dry was born 12/10/1959
  Humsdee Dumsdee was born 01/21/1972
  Fish Head was born 02/07/1954
 


Procedure Statements

PrimeBase DAL supports recursion. Also, unlike standard DAL, you can access global variables from within a PrimeBase DAL procedure.


DECLARE PROCEDURE

<procedure_stat> ::=
      [ DECLARE ] [ GLOBAL ] PROCEDURE <proc_name>
        '(' [ <param_list> ] ')'
      [ RETURNS <data_type> { ',' <data_type> } ';' ]
      { <param_decl> }
      '{' <statement_list> '}'
      END PROCEDURE <proc_name> ';'

<param_list>     ::=
      <param_name> { ',' <param_name> }

<param_decl>     ::=
      ARGUMENT <data_type> <param_name> [ '=' <expression> ]
        { ',' <param_name> [ '=' <expression> ] } ';'
DAL procedures can return zero, one or many values. Declare the data types in the RETURNS clause. If a procedure returns one value, it may be used in an expression. Otherwise, the procedure must be invoked using the CALL statement. Input parameters and their data types are declared in one or more ARGUMENT clauses. Parameters appearing in the <param_list>, but not in a <param_decl>, are assumed to be of type GENERIC. The order of the parameters in the <param_list>, is the order in which the values should be passed to the procedure. A parameter may be give a default value, <expression> in the ARGUMENT clause. In this case passing a value to the procedure for these parameters is optional (see CALL statement below).

The same name space is shared for both outer-block variable names and procedure names. A DECLARE DECLARE GLOBAL statement for an outer block automatically re-declares any procedure or outer block variable with the same name.

Execution of a procedure ends when a RETURN statement is executed. If no RETURN statement is encountered in <statement_list> then an implied RETURN statement with no return values is executed at the end of the procedure. IF values are not returned as required be the CALL statement, then the variables remain uninitialized.

Procedure names and global variables share the same name space. A new procedure statement automatically undeclares any previously declared procedure or global variable with the same name.

Unlike standard DAL, PrimeBase DAL supports recursive procedure calls. Procedures may also access global variables unless a local variables is declared with the same name as a global variable.

Example

  DECLARE PROCEDURE addAndPrint( parameter1, parameter2 )
  ARGUMENT INTEGER parameter1;
  ARGUMENT INTEGER parameter2;
  {
    PRINT parameter1, "+",
          parameter2, "=",
          parameter1 + parameter2;
  }
  END PROCEDURE addAndPrint;

  addAndPrint( 10, 5 );


  Screen output:

  10 + 5 = 15
 


RETURN

<return_stat> ::= RETURN [ <expression>
                    { ',' <expression> } ] ';'
Terminate execution of a procedure, and return a number of values to the calling statement. One <expression> should be provided for each declared return value.

See CALL for an example demonstrating the use of this statement.


CALL

<call_stat>   ::= [ CALL ] <proc_name> [ <proc_params> ]
                  [ <return_vars> ] ';'

<proc_params> ::= '(' [ <expression> ]
                    { ',' [ <expression> ] } ')'

<return_vars> ::= RETURNING <var_name> { ',' <var_name> }
Input parameters passed to a procedure may be skipped or less values may be supplied to the procedure than declared in the procedure definition.

Missing input parameters are given default values according to the definition of the arguments in the procedure. For example: CALL print_table(my_cur, ,20,); In this example parameters 2 and 4 have been omitted. Note that the last comma is not required, because no input parameters proceed the missing 4th parameter.

Missing parameters without defaults remain uninitialised. If the procedure returns more values than variables supplied in the RETURNING clause, the additional values are discarded without error.

In addition to the call procedure form above, a procedure can be called as a function when embedded in an expression. In this case, the procedure should only return one value, however additional return values are discarded.

Procedure calls may be nested deeply. The depth of nesting is subject only to resource constraints on the run-time environment.

Recursive procedure calls are supported by PrimeBase DAL only, standard DAL on the contrary does not support recursive procedure calls.

Example

  DECLARE PROCEDURE addAndMultiply( parameter1,
    parameter2 )
  RETURNS INTEGER, INTEGER;
  ARGUMENT INTEGER parameter1;
  ARGUMENT INTEGER parameter2;
  {
    RETURN ( parameter1 + parameter2 ),
           ( parameter1 * parameter2 );
  }
  END PROCEDURE addAndMultiply;

  INTEGER additionResult;

  INTEGER multiplicationResult;

  CALL addAndMultiply( 10, 5 )

  RETURNING additionResult, multiplicationResult;

  PRINT "10 + 5 =", additionResult;

  PRINT "10 * 5 =", multiplicationResult;


  Screen output:

  10 + 5 = 15
  10 * 5 = 50
 


22. PBCTL - Configuring PrimeBase DAL Compatibility

This statement controls various aspects of compatibility and other PrimeBase specific features.

 

syntax

PBCTL [ <parameter_spec> ]
  { ',' [ <parameter_spec> ] } ';'

<parameter_spec> ::= <expr_value_expr>
                     ['=' <expr_value_expr>]

 

notes

This statement:

PBCTL 0;

sets all control parameters to their default values. Each of the compatibility parameters, and their various modes are described below.


 

parameters
 
PBCTL statement identifying keyword.
<parameter_spec> parameter specification
<expr_value_expr> the first value expression must be a valid control specification number and the second a valid value for the parameter
'=' if this clause is omitted the value expression is considered to be the value of the parameter, and the parameter number is determined by the position in the list.

 

PARAMETER 1

The first control parameter controls the resulting rowset of a DESCRIBE DATABASES statement. It is possible to get further (useful) information from the describe databases statement when the parameter is set to a non-DAL compatible mode.

Mode 0: In this mode, DESCRIBE DATABASES returns the following rowset
Col#
Data Type
Name
1 VARCHAR[31] name

 

Mode 1: In this mode, DESCRIBE DATABASES returns the following rowset:
Col#
Data Type
Name
1 INT id
2 VARCHAR[31] name

 

Mode 2: In this mode, DESCRIBE DATABASES returns the following rowset:
Col#
Data Type
Name
1 VARCHAR[31] name
2 CHAR[3] access

 

Mode 3: In this mode, DESCRIBE DATABASES returns the following rowset:
Col#1 INT id
2 VARCHAR[31] name
3 CHAR[3] access

In the rowsets above, the column "id" contains the database identifier, as given in the master database. The column "access" contains information concerning the user's access to that particular database. The column can contain one of the following values:

DBA - The user has DBA privileges in the database.

USR - The user has normal user status in the database.

NA - No access. The user is not a user of the database, and may not open the database.

In the case of DBA and USR, the user may open the database. In the case of NA, the user will not be allowed to open the database.
 

PARAMETER 2

Control parameter 2 determines the formatting of DATETIME (TIMESTAMP) values. Standard DAL DATETIME formatting suffers from the problem that in the format string ($tsfmt) the symbol "MM" is ambiguous. It could mean either months or minutes.

Mode 0 In the DAL compatible mode, this ambiguity is solved assuming the first occurrence of MM refers to the month and the second occurrence refers to the minutes.

Mode 1 In this mode the upper-case version of the symbol ("MM") is considered to be months, and the lower-case version ("mm") represents minutes.
 

PARAMETER 3

Control parameter 3 is used to make PrimeBase more compatible with P.INK SQL 1.x. There are 3 options for compatibility with P.INK SQL 1.x:

A. When turned on, this option will place the value zero (or empty string) into an inserted column whose value is not specified and the column has no default.

B. This optional string trims trailing spaces from all values returned through the API. In this case the stated length of the string will exclude the trailing spaces (for CHAR and VARCHAR values).

C. This option allows all possible settings of these three options:
Mode
A
B
C
0
-
-
-
1
ON
-
-
2
-
ON
-
3
ON
ON
-
4
-
-
ON
5
ON
-
ON
6
-
ON
ON
7
ON
ON
ON

 

PARAMETER 4

This parameter allows you to define in which order the driver handles date and time values.

Mode 0 In this mode, a timestamp value is ordered by time first and then date.

Mode 1 In this mode, a timestamp value is ordered by date first and then time.
 

PARAMETER 5

Parameter 5 concerns the confusion regarding buffer length when retrieving a character string value; using this parameter, you can specify how the application should handle this exchange.

To determine how strings are output through the API we ask 4 questions, which are answered either 'yes' or 'no'. 16 different modes determine every possible combination of answers to these questions.

The questions are:

A. Should a string value always be returned with a zero termination byte?

B. Is the buffer length given the absolute length of the buffer (if not it is assumed there is one more byte than the given length)?

C. Does a information CLGetItem() return a length that includes the zero terminator byte?

D. Does a data retrieval CLGetItem() return the length including the zero terminator byte (if any)?

Set the mode according to how you answer these 4 questions.
Mode
A
B
C
D
0
-
-
-
-
1
YES
-
-
 
2
-
YES
-
-
3
YES
YES
-
-
4
-
-
YES
-
5
YES
-
YES
-
6
-
YES
YES
-
7
YES
YES
YES
-
8
-
-
-
YES
9
YES
-
-
YES
10
-
YES
-
YES
11
YES
YES
-
YES
12
-
-
YES
YES
13
YES
-
YES
YES
14
-
YES
YES
YES
15
YES
YES
YES
YES

 

PARAMETER 6

This control parameter determines the BINARY coded DECIMAL (BCD) format for data types DECIMAL and MONEY. The application can choose between DAL format and DAM format for exchange of BCD numbers in DECIMAL form.

Mode 0 DAL compatible mode: all BCD numbers are exchanged in the internal form used by the PrimeBase client and server software.

Mode 1 DAM compatible mode: all BCD numbers are exchanged in the format specified by the Apple Data Access Manager. This format is described in "Inside Macintosh 6", pages 8-36.

Note: For control parameter 6, Mode 1 is the default mode.


23. Encrypting PrimeBaseTalk Files

It is now possible to encrypt DAL scripts that are part of your application. Decryption of a file only occurs during the EXECUTE FILE process. As a result, it is not possible to read an encrypted file. Encryption can be used to hide/protect the implementation of components of your application written in DAL.

An encrypted file has a '#' character as the first character of the name. You can use the following procedure to encrypt a file:

Example

  declare procedure encrypt_script( file, path )
  argument VARCHAR file;
  argument VARCHAR path = "";
  {
    integer source_fh, dest_fh;
    VARCHAR line;
    /* Create the encrypt file if it does not exist: */
    if ( not $fileexists( path + '#' + file ) )
        $create( path + '#' + file );
    /* Open the destination file: */
    dest_fh = $open( path + '#' + file );
    /* Set the length of the file to zero: */
    $seteof( dest_fh, 0 );
    /* Open the source file: */
    source_fh = $openreadonly( path + file );
    /* Copy the data (encryption is automatic when name begins with '#'): */
    line = $readline( source_fh );
    while ( line is not null)
    {
      $writeline( dest_fh, line );
      line = $readline( source_fh );
    }
    /* Close the files: */
    $close( source_fh );
    $close( dest_fh );
  }
  end procedure encrypt_script;
 

 
For example, the encrypt the file 'showall.dal', in the TMP folder:

encrypt_script("showall.dal", "Macintosh HD:TMP:");

To execute the encrypted file, use the EXECUTE FILE command:

EXECUTE FILE "#showall.dal" in location "Macintosh HD:TMP:";

Note: EXECUTE FILE will search for an encrypted version of a file if it does not find the non-encrypted version (and visa versa). For example the statement: EXECUTE FILE "showall.dal"; will execute file '#showall.dal' if 'showall.dal' is not found. This means you do not need to modify your code in order to use encrypted scripts.


Appendices



A. System Variables



A.1 Format Control Variables

The date and time system variables have been extended to include more options for the formatting values of these types. This has been done without losing any compatibility with formats supported by DAL.


$ampm

The $ampm variable is a string containing two names separated by a '+'. The first name is the sign for morning (e.g. PM) and the second is the sign for evening.

For DAL compatibility: $ampm = "AM+PM"


$datefmt

The following tokens are recognized in $datefmt (case is NOT significant):
 
Format Description
YYYY 4-digit year
YY 2-digit year
MMM substitute $month
MM 2-digit month
M 1- or 2-digit month
DD 2-digit day
D 1- or 2-digit day
DDD substitute $day

On startup: $datefmt = "MM/DD/YYYY"


$day

The $day variable is a string of 7 day names separated by '+'. If the $day string is empty (i.e. the empty string or $null) a 3 digit Julian day (number of the day in the year) is returned. On session startup, $day contains an empty string in order to be compatible with DAL.

For DAL compatible startup $day must be empty or $null.

Note: Space is significant.

Example

  /* German week (starting with monday) is the default */
  $day  = "Monday+Tuesday+Wednesday+Thursday+Friday+Saturday+Sunday";
 


$decfmt

The system variable $decfmt is used to format DECIMAL values when converted to CHAR or VARCHAR.
<decfmt> ::= [ '[' ] '9' [ <tchar> ] '999' [ ']' ]
             [ '[' ] <dchar> <fchar> [ <ichar> ] [ ']' ]
In this description [ and ] indicate string elements that are optional, while characters in ' and ' are meant literally. <tchar>, <dchar> and <ichar> are all single characters to be select by the user. The various elements of the <decfmt> string have the following meaning:
 
<tchar> The user defined character, <tchar>, is the thousands separator. If <tchar> is omitted, no thousands separator is used.
<dchar> The user defined character, <dchar>, is the DECIMAL point. This character must be specified, and must be different to <tchar>.
<fchar> The <fchar> character indicates how to display the character immediately after the DECIMAL point. The characters `9´ and `0´ indicate that the digit should always be displayed. The underscore character indicates that the character is not to be displayed if the fractional part of the number is zero. The characters, ' ' ( a space), and ' * ' (the asterisk), may also be used if the fractional part of the number is zero.
<ichar> The <ichar> character is the insignificant fraction digits character. Insignificant fraction digits are the trailing zero digits of the fraction: for example, in the value 1.2000, the characters '000' are the insignificant zero digits of the fraction. The number of such digits depends on the scale of the DECIMAL number (and the number of significant fraction digits). These digits are printed as the user defined character <ichar>. (<ichar> must be set to '0' to print the insignificant fraction digits as zeros!) <ichar> may also be omitted, or the underscore character can be set; in this case, insignificant fraction digits are not displayed. <ichar> can also be: ' ' (space), '0', or '*'.
The first optional '[' ']' The first optional set of brackets indicate whether the whole number part of the number should be displayed, if it is zero. By placing the whole number part between the brackets, you indicate that it is optional and therefore not displayed when the value is zero.
The second optional '[' ']' By placing the fraction between the brackets (including the DECIMAL point), you indicate that this part is optional, and should not be displayed when the value is zero.

For DAL compatibility, on startup: $decfmt = "[9999].9"

Note: If both whole number and fractional parts are optional, zero will still be printed as '0'.


$moneyfmt

The system variable $moneyfmt is used to format MONEY type values when converted to string. The $moneyfmt variable may be assigned a value of the form:
<moneyfmt> ::= [ '(' ] [<cstring>] [ '(' ] [ '-' ] <decfmt>
                 [ ')' ] [ <cstring> ] [ ')' ]
Parentheses: '(' ')' All parentheses are optional. If they appear, they indicate that negative MONEY values should be displayed in parentheses. You can indicate whether the brackets should include the currency string (<cstring>) or not. When parentheses appear the negate sign is not permitted, see below.
<cstring> The <cstring> is a user defined string of characters, which may optionally be enclosed in DOUBLE or single quotation marks. The string indicates which currency symbol is to be used. Spaces are significant in this string. Either the first or second <cstring> must appear, but not both. The position of <cstring> indicates the position of the currency symbol: whether it comes before or after the currency value.
The negate sign: '-' The negate sign is optional. When it appears, it indicates that negative values are to be displayed with a leading `-´character. Please note that either the optional parentheses can be specified, or the negate sign, but never both. If neither is specified, then the negate sign is assumed by default.
<decfmt> This is a DECIMAL format string, as has already been described under the heading $decfmt. Please note the following example in this regard: "9,999.9 DM". In this example, it is impossible to tell whether the space after the 9 is meant to be the <ichar> (the insignificant fraction digits character) or the first character of <cstring> (the currency symbol). Please use the optional quotation marks for the currency symbol in the case as follows: "9,999,9' DM´" It is now clear that the currency symbol has a leading space and that insignificant fraction digits are not displayed.

On startup: $moneyfmt = "$[9999].9"


$month

The $month variable is used to convert month values (1..12) to string (space is significant). It contains a string of 12 month names separated by '+'.

On startup: $month = "January+February+March+April+May+June+July+August+September+October+November+December"


$timefmt

The following tokens are recognized in $datefmt. Case is NOT significant, except for 'xm' and 'XM'.
 
Format Description
HH 2-digit hour (24 hour time by default, 12 hour time if xm or XM in time format).
H 1- or 2-digit hour
MM 2-digit minutes
M 1- or 2-digit minute
SS 2-digit seconds
S 1- or 2-digit seconds
HU 2-digit hundreds
T 1-digit tenth
XM | AM | PM substitute uppercase $ampm
xm | am | pm substitute lowercase $ampm
X.M. | A.M. | P.M. substitute uppercase A.M. or P.M.
x.m. | a.m. | p.m. substitute uppercase a.m. or p.m.

On startup: $timefmt = "HH:MM:SS:hu"


$tsfmt

This format string includes both DATE and TIME tokens. Without loosing compatibility with DAL, PrimeBase allows the '$tsfmt' variable to be empty (the empty string - ""), or $null. In this case the $datefmt and $timefmt are used in that order and a space placed between the two.

In $tsfmt variable the month and minute formats are ambiguous. To resolve this problem, the first 'MM'/'mm' format value is interpreted as the month value and following 'MM'/'mm' value is interpreted as minutes.

In PrimeBase DAL you may set PBCTL 1=1; the month and minute tokens are then case-sensitive. Uppercase ('MM') indicates months, and lowercase ('mm') indicates minutes.

There are some problems with the DAL definition of this format, yet these are solved with a non-compatible PrimeBase extension, which can be turned on or off:

In DAL compatible mode the PBCTL parameter 2 is set to zero. For example,

PBCTL 2=0;

In this mode, the format before the first space in the timestamp format must be a date format, and the format after the first space must be a time format. Note that in this way, "MM" or "M" appearing before the first space represent months, and "MM" and "M" appearing after the first space represent minutes.

PRIMEBASE EXTENSION: This extension is not DAL compatible because it makes the month and minute tokens case-sensitive in the timestamp format. By setting the following PBCTL:

PBCTL 2=1;

both date and time tokens may be freely mixed. Month and minute tokens are distinguished by the case of the first "m". Upper-case indicates months and lowercase indicates minutes. Thus "MM", ""Mm" and "M" represent months, and "mm", "mM" and "m" represent minutes. Please note that the "MM" token is still case-insensitive in the $timefmt and $datefmt system variables.


A.2 Constants

 
Constant Value Description
$true
The BOOLEAN value "true".
$false
The BOOLEAN value "false".
$null
A NULL (missing) value.
$BOOLEAN
The result of $typeof() on a BOOLEAN value.
$SMINT
The result of $typeof() on a SMINT value.
$integer
The result of $typeof() on a INTEGER value.
$smfloat
The result of $typeof() on a SMFLOAT value.
$FLOAT
The result of $typeof() on a FLOAT value.
$date
The result of $typeof() on a DATE value.
$time
The result of $typeof() on a TIME value.
$timestamp
The result of $typeof() on a TIMESTAMP value.
$CHAR
The result of $typeof() on a CHAR value.
$DECIMAL
10 
The result of $typeof() on a DECIMAL value.
$MONEY
11 
The result of $typeof() on a MONEY value.
$VARCHAR
12 
The result of $typeof() on a VARCHAR value.
$VARBIN
13 
The result of $typeof() on a VARBIN value.
$LONGCHAR
14 
The result of $typeof() on a LONGCHAR value.
$LONGBIN
15 
The result of $typeof() on a LONGBIN value.
$sqlnotfound
100 
Assigned to $sqlcode when the current row is no longer valid.
$ServerVersion
An integer representing the version number of the server connected to. Zero is returned if the session isn't connected to a server.
$version
A string describing the current DAL type and version number. (1)

(1) This is the format used for the $version string:

<$version_string> ::= 'PrimeBase ' <version>
                         '(' <OS type> '; ' <Endian> ';' <OS Info> ')'

<OS type>         ::= 'Windows' | 'Mac OS' | 'Unix'
<Endian>          ::= 'L-E' | 'B-E'  (L-E means Little-Endian,
                                     and B-E means Big-Endian)

<OS Info>         ::= '68K'      |'PPC'       | '95,98,NT' |
                      'OpenStep' | 'Rhapsody' | 'AIX' |
                      'SunOS'    | 'Solaris'  | 'Linux'


A.3 System Control Variables

 


$sqlcode

This variable is automatically set after each execution of each data manipulation statement. Its value is zero if no error occurred and negative for errors.

Note though that only data manipulation functions (for example SELECT, FETCH, etc.) set $sqlcode to zero, so usually you want to set it to zero manually to get a meaningful result in any case.


 


$sqlcode2

This variable contains the secondary error code if an error occured. Also see $sqlcode.

 


$switch

This system variable has the value of the expression of the last SWITCH statement executed. It is uninitialized before the first switch statement is executed.


A.4 Cursor Control Variables



$aborttime

The abort time is the time that the server allows a transaction to remain idle. A transaction is considered idle when it is not reading, writing or sorting. Abort time is specified in seconds. The upper-limit (and default value) for abort time is stored on a per user basis in the SysUsers table in each database. If more than one database is open on a particular connection, then the upper-limit is the minimum of all default values.

It is recommended that abort time be set to, at least, 2 seconds. Normally abort time is quite high, for example 10 minutes. The system default is 30 minutes. The purpose of an abort time is to ensure that a transaction does not consume server resources and do nothing. This can occur if a complex deadlock has occurred (a deadlock involving more than 2 transactions), or if the client application has forgotten about the transaction for some reason (for example, an application begins a transaction, and then the user switches to another application).


$colcnt

After each successful SELECT statement, this variable is automatically set to the number of columns that have just been created in the rowset.

Example

  SELECT * FROM Golfers;

  PRINT $colcnt;


  Screen output:

  11
 


$cursor

After a successful SELECT statement that has no CURSOR explicitly specified as destination (i.e. no INTO clause), this variable is set to identify the rowset just created.

Example

  SELECT FirstNames FROM Golfers FOR EXTRACT;

  PRINTALL;

  PRINT "";

  PRINTALL $cursor;


  Screen output:

  Buzz
  Fish
  Humsdee
  Tumble

  Buzz
  Fish
  Humsdee
  Tumble
 


$locktimeout

This variable contains the time in hundredths of seconds that the system should wait for a lock.

When set to zero, the Server will return an error to the user as soon as the user (user's transaction) attempts to acquire a lock on data that conflicts with locks held by other transactions.

If the variable is set to $null the Server will wait as long as it takes to acquire a lock. Any other positive value causes the Server to wait that number of 100th of a second before returning a locked error.

The default for this variable is zero.


$maxrows

This variable is initially set to $null, and can be modified by the SET statement. If it is set, it limits the number of rows in the rowset created by a SELECT statement.

Example

  /*
  ** Remember current value of $maxrows to be able to
  ** restore it again
  */

  integer maxRowsBackup = $maxrows;

  $maxrows = $null;

  SELECT SurName FROM Golfers;

  PRINTALL;

  PRINT "";

  $maxrows = 1;

  SELECT SurName FROM Golfers;

  PRINTALL;


  /*
  ** Restore old value of $maxrows
  */

  $maxrows = maxRowsBackup;


  Screen output:

  Bee
  Dry
  Dumsdee
  Head

  Bee
 


$rowcnt

After each successful SELECT statement, this variable is set to the number of rows in the rowset just created, if the number of rows is known. If the number of rows is not known $rowcnt is set to $null.
Note that if the mode of the SELECT statement is not EXTRACT, the number of rows is not necessarily known. Only after a SELECT for EXTRACT have all rows always been loaded by the client, and as a result the number of rows is known.

Example

  SELECT * FROM Golfers FOR EXTRACT;

  PRINT $rowcnt;


  Screen output:

  4
 


$rowlocking

This variable is used to turn row locking on (set to $true) or off (set to $false). When row locking is off, the server gains table level locks for every table accessed by the transaction. When row locking is on, the server locks only the rows accessed by the transaction.

Row locking should not be used in transactions that access a large number of rows (over 2000), or most rows in a particular table, because each row lock requires about 28 bytes of memory on the server.


$rowsaffected

$rowsaffected is set after a successful INSERT, UPDATE or DELETE. The value is the number of rows affected by the query. If an error occurs $rowsaffected is set to zero.


$rowsperpage

This system variable allows you to control the number of rows in a page received from the Server. Normally the variable is set to zero or NULL, in which case the number of rows is estimated by the system. The effect of setting this variable is not visible from the DAL program itself.

NOTE: The size of the pages to be used is only calculated when the SELECT is done; subsequent pages fetched for the SELECT are not resized according to $rowsperpage (i.e. make sure that $rowsperpage is set before the SELECT is done).


A.5 DBMS Lookup Variables

PrimeBase equates a server or gateway with the DAL concept of a DBMS. These global variables are used to control the behavior of the DESCRIBE DBMS statement when in "dynamic lookup" mode.

DESCRIBE DBMS can be made to perform a dynamic lookup of servers and gateways on the network. This is done by setting: PBCTL 11=1;

The DBMS lookup variables may then be used to further control the scope of the network lookup. If the variables below are set to NULL, then all servers and gateways are listed.
 
$dbmszone If not NULL, then it specifies the AppleTalk zone to be searched for servers by the DESCRIBE DBMS statement.
$dbmsbrand This variable can be used to search for a particular server/gateway type. For example, when set to ‘PrimeBase’, the DESCRIBE DBMS statement will search only for PrimeBase servers. To lookup P.INK SQL servers, set this variable to ‘PinkSQL’.
$dbmsprotocol Servers and gateways may publish itself on one or more protocols, for example, ADSP (Apple Data Stream Protocol) and TCP/IP (Transport Control Protocol/Internet Protocol). When set to NULL, the client will search for all servers published on protocols that the client is capable of. Setting this variable to ‘adsp’, would limit the search to servers capable of communicating using ADSP. Other possibilities are: ‘tcp’ (TCP/IP), ‘ppc’ (Macintosh Program-to-Program communications) and ‘ipc’ (UNIX or Windows inter-process communication, using shared-memory). Note that an error will occur if the client is not capable of the specified protocol.


A.6 Login Information

After the client has successfully established a connection to a server certain information regarding the connection/login is made available to the client application. This information is stored in the following global variables:
 
$logintime The time on the server machine at login is recorded in this variable. This value can be used by the client to synchronize its time with that of the server after login.
$connid The server connection identifier is stored in the variable after login. The value can be used by the client to find it’s connection entry in the SysConnections system table in the Master database.
$user Stored in this variable is the name of the user of the last successful connection to a server. If a user name is not specified in subsequent connects (OPEN DBMS command), then the value stored in $user is used.


B. Built-in Functions



B.1 String Functions



VARCHAR $format( <fmtstr>, <arg1>, <arg2>, ... )

Returns a formated string.

<fmtstr> - a string describing the format of the arguments.
<argn> - values to be embedded in the format string.

The Format String
<fmtstr> may consist of three formatting specifications. They are interpreted in a left to right order. These formatting specifications are:

1. Ordinary characters: They simply stand for themselves, and are appended to the end of the output string.

2. Special characters: They are non-printing characters - for example a tab. Special characters consist of a two character sequence: a backslash and a second character.

3. Conversion specifications: These are specifications that take the following data argument and convert it into a sequence of characters that are appended to the end of the out string.


 

Special Characters: Two-Character Sequences

 
\b generates a backspace character.
\n generates a newline character.
\r generates a return character.
\t generates a tab character.
\\ generates a backslash character.
\% generates a percent character.

 

Conversion Specifications

The percent character (%) introduces all conversion specifications and is concluded by a conversion character (<fmtchar>).

 

syntax

<conversion_spec> ::= '%' ['-'] [<width>] ['.' <precision>]

  <fmtchar>


 

Minus Sign

The minus sign, when included, justifies the data to the left upon conversion. Otherwise the data is right justified. Justification is, of course, relative to width and precision - which specify the width of the converted data string.

 

Width and Precision

Specify the width and precision of the converted data string in the fields <width> and <precision>. Remember to include the period (.) preceding the value you give as the <precision>. You can specify width and precision as the special character, asterisk (*). Then the numeric value for the parameter is taken from the next data argument to the $format() call.

 

Format Character Values

The values that you can use in <fmtchar> are as follows:
 
d or u This is an integer data argument. It converts to a string, whose value is given in the integer literal format.
p This is a DECIMAL or MONEY data argument. It converts to a string, whose value is given in the DECIMAL literal format.
c This is a single character data argument. It copies the character.
f This is a floating-point data argument. It converts to a string, whose value is given in the floating-point literal format.
s This is a string data argument. It copies to a string, according to the other conversion parameters.
x or X This converts its data argument to a sequence of characters that are the unsigned hexadecimal representation of the data item. A capital X results in uppercase hex digits (A-F); a lowercase x results in lowercase hex digits (a-f).

 

When the <fmtchar> is preceded by:

 
^ (caret) the output field contents are to be converted to uppercase characters.
! (exclamation point) the output field contents are to be converted to lowercase characters.

 

Notes

For every call to the $format() function, the number of conversion specifications in <fmtstr> and the number of data arguments must match. If the number of data arguments is less than the number of conversion specifications, then errors may result.

 

Example

  PRINT "3153936 in hexadecimal format (network byte";

  PRINT "order ( MAC ) ):";

  PRINT " ", $format( "%x", 3153936 );

  PRINT "";

  PRINT "3153936 in hexadecimal format (opposite network";

  PRINT "byte order ( PC ) ):";

  PRINT " ", $format( "%x", VARBIN( 3153936 ) );


  Screen output:

  3153936 in hexadecimal format (network byte
  order ( MAC ) ):
    10203000

  3153936 in hexadecimal format (opposite network
  byte order ( PC ) ):
    00302010
 


INTEGER $hash( <string> )

Returns a hash value for the specified string.

<string> - string to be "hashed".

Example

  PRINT $hash( "test" );

  PRINT $hash( "test1" );


  Screen output:

  363140
  5810289
 


VARCHAR $left( <string>, <pattern>, <count> )

Returns the string to the left of the given pattern.

<string> - the string to be searched.
<pattern> - a pattern to be found in the string.
<count> - an optional argument. If included, a positive <count> value causes the search of <string> to continue until <count> occurrences have been found, starting from the right side of the string. A negative <count> value causes the search to begin at the end of <string>, and proceed from right to left. If it is omitted, the functions searches for the first occurrence of <pattern> within <string>starting from the right.

Example

  VARCHAR myString = "Hello world1 world2.";

  myString = $left( myString, "world", 2 );

  PRINT myString;


  Screen output:

  Hello world1
 


INTEGER $locate( <string>, <pattern>, <count> )

Returns the offset of the pattern in the string.

<string> - the string to be searched.
<pattern> - a pattern to be found in the string.
<count> - an optional argument. If included, a positive <count> value causes the search of <string> to continue until <count> occurrences have been found, starting from the right side of the string. A negative <count> value causes the search to begin at the end of <string>, and proceed from right to left. If it is omitted, the functions searches for the first occurrence of <pattern> within <string>starting from the right.

If the pattern could not be found, $locate returns 0.

Example

  INTEGER myOffset = $locate( "Hello world1 world2.",
                              "world", 2 );

  PRINT "The search-pattern has been found the second";

  PRINT "time at offset:";

  PRINT " ", myOffset;


  Screen output:

  The search-pattern has been found the second
  time at offset:

    14
 


VARCHAR $ltrim( <string> )

Returns a string without spaces to the left.

<string> - a VARCHAR or CHAR value.

Example

  PRINT "'" +
                "   <-3 spaces on each side->   "   + "'";

  PRINT "'" +
        $ltrim( "   <-3 spaces on each side->   " ) + "'";


  Screen output:

  '   <-3 spaces on each side->   '
  '<-3 spaces on each side->   '
 


VARCHAR $right( <string>, <pattern>, <count> )

Returns the string to the right of the pattern.

<string> - the string to be searched.
<pattern> - a pattern to be found in the string.
<count> - an optional argument. If included, a positive <count> value causes the search of <string> to continue until <count> occurrences have been found, starting from the right side of the string. A negative <count> value causes the search to begin at the end of <string>, and proceed from right to left. If it is omitted, the functions searches for the first occurrence of <pattern> within <string>starting from the right.

Example

  VARCHAR myString = "Hello1 Hello2 world.";

  myString = $right( myString, "Hello1 " );

  PRINT myString;


  Screen output:

  Hello2 world.
 


VARCHAR $rtrim( <string> )

Returns a string without spaces to the right.

<string> - a VARCHAR value.

Example

  PRINT "'" +
                "   <-3 spaces on each side->   "   + "'";

  PRINT "'" +
        $rtrim( "   <-3 spaces on each side->   " ) + "'";


  Screen output:

  '   <-3 spaces on each side->   '
  '   <-3 spaces on each side->'
 


VARCHAR $substr( <string>, <position> ,<length> )

Returns a string extracted from <string>.

<string> - a VARCHAR or CHAR value.
<position> - an integer specifying the starting position within <string>. The first character of <string> is specified as position1. If position is negative, then it specifies a position relative to the end of <string>. The last character of <string> is designated by the value -1.
<length> - this argument is optional, and specifies the number of characters to extract. If length is negative, then position specifies the last character of the extracted string, and extraction proceeds to the left instead of to the right. If omitted, the function extracts the entire remainder of <instr> up to its final character.

Example

  PRINT $substr( "Hello Hello world.", 7, 12 );


  Screen output:

  Hello world.
 


VARCHAR $tolower( <string> )

Returns a lower-case version of the given string.

<string> - a VARCHAR value.

Example

  PRINT $tolower( "Hello world." );


  Screen output:

  hello world.
 


VARCHAR $toupper( <string> )

Returns a upper-case version of the given string.

<string> - a VARCHAR value.

Example

  PRINT $toupper( "Hello world." );


  Screen output:

  HELLO WORLD.
 


VARCHAR $trim( <string> )

Returns a string without spaces to the left or right.

<string> - a VARCHAR or CHAR value.

Example

  PRINT "'" +
        $trim( "   <-3 spaces on each side->   " ) + "'";


  Screen output:

  '<-3 spaces on each side->'
 


B.2 Variable Information Functions



BOOLEAN $exists( <var_name> )

Test if variable or procedure with the specified name exists.

<var_name> - VARCHAR containing the name of the variable or procedure whose existance should be tested.

$exists() can also be used to query the existance of builtin functions.

Example

  INTEGER myInt;

  PRINT $exists( "myInt" );

  PRINT $exists( "myInt2" );


  /* Check wether a variable or function called $toupper exists.
  ** This should return true, unless the builtin function $toupper
  ** got "overwritten" by the definition of another procedure or
  ** variable of the same name, or $toupper got undeclared using
  ** UNDECLARE. */

  PRINT $exists( "$toupper" );


  Screen output:

  $TRUE
  $FALSE
  $TRUE
 


INTEGER $len( <value> )

Returns the number of bytes a value takes up in memory.

<value> - any type of value.

$len( $null ) is 0 by definition.

Example

  /* A VARCHAR literal */
  PRINT $len( "Teststring" );

  /* An INTEGER (4 Bytes) literal */
  PRINT $len( 10 );

  /* A TINYINT (1 Byte) value */
  PRINT $len( tinyint( 10 ) );


  Screen output:

  10
  4
  1
 


INTEGER $scale( <value> )

Returns the scale of the value.

<value> - a value of any type.

$scale( $null ) returns 0 by definition. For all values that are not of data type DECIMAL or MONEY, $scale() always returns 0.

Example

  /* A DECIMAL literal */
  PRINT $scale( 3.14 );

  /* A MONEY literal */
  PRINT $scale( $3.14 );

  /* A FLOAT value */
  PRINT $scale( FLOAT( 3.14 ) );

  /* An INTEGER literal */
  PRINT $scale( 3 );

  /* A LONGBIN value */
  PRINT $scale( LONGBIN( "TEST!" ) );


  Screen output:

  2
  2
  0
  0
  0
 


INTEGER $typeof( <value> )

Returns the type of the value as an INTEGER.

<value> - a value or variable of any type.

The value returned depends on the constant value of the type indicated by the table of constants.

Example

  INTEGER myInteger = 10;

  GENERIC myGeneric = myInteger;

  PRINT $typeof( myGeneric );


  Screen output:

  3
 


B.3 Cursor Information Functions



INTEGER $cols( <cursor> )

This function returns the number of the columns in the rowset of a particular CURSOR.

<cursor> - optional, name of a CURSOR, default is $cursor.

Example

  CURSOR myCursor;

  SELECT * FROM Golfers INTO myCursor;

  PRINT $cols( myCursor );


  Screen output:

  11
 


INTEGER $rows( <cursor> )

Returns the number of rows in the CURSOR.

<cursor> - optional, name of a CURSOR, default is $cursor.

Example

  CURSOR myCursor;

  SELECT * FROM Golfers INTO myCursor;

  PRINT $rows( myCursor );


  Screen output:

  4
 


VARCHAR $colname( <cursor>, <column> )

Returns the name of the column.

<cursor> - optional, name of a CURSOR, default is $cursor.
<column> - number or name of the column.

Example

  CURSOR myCursor;

  SELECT * FROM Golfers INTO myCursor;

  PRINT $colname( myCursor, 11 );


  Screen output:

  Earnings
 


INTEGER $coltype( <cursor>, <column> )

Returns the type of the column as an INTEGER value.

<cursor> - optional, name of a CURSOR, default is $cursor.
<column> - number or name of the column.

The value returned depends on the constant value of the type indicated by the table of constants.

Example

  CURSOR myCursor;

  SELECT * FROM Golfers INTO myCursor;

  PRINT $coltype( myCursor, 2 );


  Screen output:

  12
 


INTEGER $collen( <cursor>, <column> )

Returns the column length.

<cursor> - optional, name of a CURSOR, default is $cursor.
<column> - number or name of the column.

Example

  CURSOR myCursor;

  SELECT * FROM Golfers INTO myCursor;

  PRINT $collen( myCursor, 2 );


  Screen output:

  55
 


INTEGER $colplaces( <cursor>, <column> )

Returns the scale of the column.

<cursor> - optional, name of a CURSOR, default is $cursor.
<column> - number or name of the column.

Example

  CURSOR myCursor;

  SELECT * FROM Golfers INTO myCursor;

  PRINT $colplaces( myCursor, 11 );


  Screen output:

  2
 


INTEGER $colwidth( <cursor>, <column> )

Returns the maximum size of the column as text.

<cursor> - optional, name of a CURSOR, default is $cursor.
<column> - number or name of the column.

Example

  CURSOR myCursor;

  SELECT * FROM Golfers INTO myCursor;

  PRINT $colwidth( myCursor, 11 );


  Screen output:

  17
 


B.4 Cursor Manipulation Functions

These functions manipulate the memory image of a CURSOR rowset on the client.

Note: No changes are made to the database - this is a memory update only. These functions are supported by PrimeBase DAL only.


INTEGER $currentrow( <cursor> )

Returns the current row offset.

<cursor> - optional, name of a CURSOR, default is $cursor.

Returns the absolute row number of the of the current row of the CURSOR. This function returns zero when the current row is before the rowset, and returns the number of of rows plus one when the current row is after the last row.

Example

  CURSOR myCursor;

  SELECT INTEGER 1,                /* ID */
         VARCHAR[20] "Joe",        /* Name */
         VARCHAR[30] "Average",    /* Surname */
         INTEGER 26,               /* Age */
         MONEY "$95,000",          /* Yearly income */
         DATE "01/01/1996"         /* In company since */
         INTO myCursor FOR EXTRACT;

  FETCH FIRST OF myCursor;

  PRINT $currentrow( myCursor );


  Screen output:

  1
 


$deleterow( <cursor> )

Delete the current row of a rowset.

<cursor> - optional, name of a CURSOR, default is $cursor.

This function deletes the current row in the rowset. The current row becomes the row after the row deleted. The absolute row position (as returned by $currentrow()) remains the same.

Warning

Because $deleterow() changes the current row of the cursor to the next row and FOR EACH doing the exact same at the end of the <statement> of FOR EACH, this leads to single rows being skipped over!

A workaround to this behavior is issuing a FETCH PREVIOUS OF <cursor> directly after a call of $deleterow().
 


 

Example

  CURSOR myCursor;

  SELECT INTEGER 1,                /* ID */
         VARCHAR[20] "Joe",        /* Name */
         VARCHAR[30] "Average",    /* Surname */
         INTEGER 26,               /* Age */
         MONEY "$95,000",          /* Yearly income */
         DATE "01/01/1996"         /* In company since */
         INTO myCursor FOR EXTRACT;

  FETCH FIRST OF myCursor;

  PRINT "Number of rows in myCursor:", $rows( myCursor );

  $deleterow( myCursor );

  PRINT "Number of rows in myCursor:", $rows( myCursor );


  Screen output:

  Number of rows in myCursor: 1
  Number of rows in myCursor: 0
 


$insertrow( <cursor>, <value>, <value>, ... )

Insert a row into the rowset of the CURSOR.

<cursor> - optional, name of a CURSOR, default is $cursor.
<value> - the values to be inserted (one for each column).

This function inserts a row into a rowset. $null is inserted when values are not given. The row is inserted before the current row, thus becoming the current row itself.

Example

  CURSOR myCursor;

  SELECT INTEGER 1,                /* ID */
         VARCHAR[20] "Joe",        /* Name */
         VARCHAR[30] "Average",    /* Surname */
         INTEGER 26,               /* Age */
         MONEY "$95,000",          /* Yearly income */
         DATE "01/01/1996"         /* In company since */
         INTO myCursor FOR EXTRACT;

  $insertrow(
        myCursor,
        2,
        "Joey",
        "Leverage",
        30,
        MONEY "$10,000",
        DATE "02/02/1902"
  );

  PRINT "Number of rows in myCursor:", $rows( myCursor );


  Screen output:

  Number of rows in myCursor: 2
 


$updaterow( <cursor>, <start_column>, <value1>, <value2>, ... )

Update the current row of a rowset.

<cursor> - optional, name of a cursor, default is $cursor.
<start_column> - the number or name of the column where $updaterow should start updating (columns are numbered 1 to n, n = $cols(<cursor>)).
<value1>, <value2>, ... - the new values for the columns begining at the position indicated above.

This function updates the current row of a rowset beginning at a specific column.

The start column may be identified using a column number or an OBJNAME value which contains the name of a column alias. If omitted, the update begins at the first column of the row.


 

Example

  CURSOR myCursor;

  SELECT INTEGER 1             as ID,
         VARCHAR[20] "Joe"     as Name,
         VARCHAR[30] "Average" as Surname,
         INTEGER 26            as Age,
         MONEY "$95,000"       as Income,
         DATE "01/01/1996"     as SomeDate
         INTO myCursor FOR EXTRACT;

  FETCH FIRST OF myCursor;


  /*
  ** Change Age to 30
  */

  $updaterow( myCursor, 4, 30 );

  /*
  ** which is equivalent to...
  ** $updaterow( myCursor, 1, , , , 30 );
  ** $updaterow( myCursor, objname "Age", 30 );
  */


  PRINT myCursor -> 4;


  Screen output:

  30
 


B.5 File I/0 Functions

Some of the functions presented below accept or need a parameter containing a filesystem path. Since the format of filesystem paths are system dependant, the correct format for the target platform must be used

Each of the functions that reads or writes data to or from files, also changes the current position in the file. This behaviour ca be used if data has to be appended to files, etc.

Where apropriate, examples for each of the different platforms supported by PrimeBase DAL are given. Please be sure to use the correct one for your platform.

The following examples assume that you have write access to the (current working - on Windows and Unix) directory iDAL or the server console was started in.


$close( <handle> )

Close a file previously opened with $open().

<handle> - an INTEGER value previously returned by $open() or $openreadonly().

The file associated with the INTEGER is closed, and resources for accessing the file are freed.

See $open() for an example of how to use this function.


$create( <file> )

Create a file.

<file> - name of the file (including filesystem path if necessary) to be created.

Example

  /*
  ** Let's simply create a file in the directory where the
  ** iDAL session or server console you are using was
  ** started, and delete it again immediately
  */


  $create( ":PrimeBase-Test" );
  $delete( ":PrimeBase-Test" );



  $create( ".\PrimeBase-Test" );
  $delete( ".\PrimeBase-Test" );



  $create( "./PrimeBase-Test" );
  $delete( "./PrimeBase-Test" );

 
Note:

Also see the introductory information provided at the beginning of this appendix (B.5 File I/O Functions) about platform-specific differences in the usage of the functions described here.
 


$delete( <file> )

Delete a file.

<file> - name of the file (including filesystem path if necessary) to be deleted.

See $create() for an example of how to use this function.


BOOLEAN $fileexists( <file> )

Returns $true if a particular file or directory exists.

<file> - name of a file (including filesystem path if necessary).

Example

  /*
  ** Let's create a file, so we can test for its existance
  ** successfully
  */

  $create( "PrimeTest" );


  /*
  ** Let's test for its existance now
  */

  IF ( $fileexists( "PrimeTest" ) )
  {
        PRINT "The file was successfully created.";
        $delete( "PrimeTest" );
  }
  ELSE
  {
        PRINT "The file could not be created!";
  }


  Screen output:

  The file was successfully created.
 


DATE $filestamp( <file> )

Return the last modification time of a file.

<file> - name of a file (including filesystem path if necessary).

Example

  /*
  ** Let's create a test file and print its filestamp
  */

  $create( "PrimeBase-Test-File" );

  PRINT $filestamp( "PrimeBase-Test-File" );


  /*
  ** Let's cleanup again
  */

  $delete( "PrimeBase-Test-File" );


  Screen output:

  12/23/1998 11:45:08.00
 
Note:

Of course your screen output will differ from the above.
 


VARCHAR $getcwd()

Return the current working directory.

Example

  PRINT $getcwd();


  Screen output:

  D:\projects\pbds
 
Note:

Of course your screen output will probably differ from the above.
 


INTEGER $geteof( <handle> )

Return the length of a file.

<handle> - an INTEGER value previously returned by $open() or $openreadonly().

See $seteof() for an example of how to use this function.


VARCHAR $gettempdir()

Return the system's temporary directory.


CURSOR $listdir( <directory> )

Read the contents of a directory.

<directory> - name of a directory (including filesystem path if necessary).

The rows returned in the cursor have the following format:
 
Col#
Data Type Name Description
VARCHAR[64] name Name of directory or file.
BOOLEAN isfile BOOLEAN value indicating wether this is a file or a directory.
TIMESTAMP created TIMESTAMP with creation date of this file or directory.
TIMESTAMP modified TIMESTAMP with last modification date of this file or directory.
VARCHAR[64] permission A string of yet unclarified syntax describing the access permission of this file or directory. (1)

(1) On some platforms this column may always contain $NULL.

Example

  CURSOR myDir = $listdir( "" );

  PRINTINFO myDir;

  PRINTALL myDir;


  Screen output:

  1 name 12 64 0 64
  2 isfile 1 1 0 6
  3 created 8 8 0 22
  4 modified 8 8 0 22
  5 permission 12 64 0 64
  ADMIN.exe $TRUE 03/16/1999 15:09:00.00 01/01/1970 01:00:00.00 $NULL
  CONSOLE.EXE $TRUE 02/19/1999 10:18:22.00 01/01/1970 01:00:00.00 $NULL
  DATABASE $FALSE 03/16/1999 13:54:06.00 01/01/1970 01:00:00.00 $NULL
  ENVEDIT.EXE $TRUE 11/20/1998 18:08:28.00 01/01/1970 01:00:00.00 $NULL
  README.DOC $TRUE 12/30/1997 19:54:02.00 01/01/1970 01:00:00.00 $NULL
  RELEASE.TXT $TRUE 03/16/1999 13:42:58.00 01/01/1970 01:00:00.00 $NULL
  SERVER.EXE $TRUE 02/19/1999 10:18:28.00 01/01/1970 01:00:00.00 $NULL
  SETUP $FALSE 02/01/1999 08:41:56.00 01/01/1970 01:00:00.00 $NULL
  server.key $TRUE 02/03/1999 17:59:26.00 01/01/1970 01:00:00.00 $NULL
  password.adm $TRUE 03/24/1999 14:00:14.00 01/01/1970 01:00:00.00 $NULL
  PrimeBase-Test-Dir $FALSE 03/24/1999 14:00:14.00 01/01/1970 01:00:00.00 $NULL
 
Note:

Depending on your platform you will get different results for this example.
 


LONGBIN $map( <file> )

Read an entire file into a LONGBIN or LONGCHAR value.

$map( <file>, <data> )

Write an entire LONGBIN or LONGCHAR value into a file.
<file> - name of a file (including filesystem path if necessary).
<data> - the data to be written.

Example

  VARCHAR myFilename = "PrimeBase-Test-File";


  /*
  ** Let's write some LONGBIN test data into a file
  */

  $map( myFilename, LONGBIN( 0x00010203040506070809 ) );


  /*
  ** Let's check if all data was written into the file
  */

  LONGBIN myLONGBIN = $map( myFilename );

  PRINT $format( "%x", $substr( myLONGBIN, 1, 50 ) );


  /*
  ** Let's cleanup again
  */

  UNDECLARE myLONGBIN;

  $delete( myFilename );


  Screen output:

  00010203040506070809
 


$mkdir( <directory> )

Create a directory.

<directory> - name of the directory (including filesystem path if necessary) to be created.

Example

  /*
  ** Let's simply create a directory
  ** and delete the directory again
  */
  $mkdir( "PrimeBase-Test" );
  $rmdir( "PrimeBase-Test" );
 
Note:

Also see the introductory information provided at the beginning of this appendix (B.5 File I/O Functions) about platform-specific differences in the usage of the functions described here.
 


VARCHAR $nativepath( <unix_path> )

Converts a unix directory path into the equivalent native path.

<unix_path> - the directory path to be converted.

Example

  
  print $nativepath( "foo/bar.pbt" );
 

  Screen output (Mac):
  :foo:bar.pbt
  Screen output (Windows):
  foo\bar.pbt
  Screen output (Unix):
  foo/bar.pbt
 
Note:

Absolute paths may not be interpreted correctly since absolute paths on Mac and Windows require you to specify a volume. (If you know enough about your environment to specify an absolute path then you probably know what OS you are on and therefore do not need this function anyway.)
 


INTEGER $open( <file> )

Open a text file for reading or writing.

<file> - name of the file (including filesystem path if necessary) to be opened.

This function takes as input a string value, that represents the path of the file to be opened. Returned is an INTEGER value which is to be used as a file handle for subsequent read, write and close operations. Note that there is no special file type, a normal INTEGER is used as file handle.

Files opened are globally accessible, and as a result, files opened in procedures are not automatically closed on return from the procedure. A maximum of 10 files may be opened by a DAL session. Valid file handles are in the range zero to 9 (inclusive). When a session is closed, all open files are closed as well.

Example

  /*
  ** Let's simply create a file in the directory where the
  ** iDAL session or server console you are using was
  ** started, open the newly created, close it, and delete
  ** it again immediately
  */



  $create( ":PrimeBase-Test" );
  INTEGER myFileHandle = $open( ":PrimeBase-Test" );
  $close( myFileHandle );
  $delete( ":PrimeBase-Test" );



  $create( ".\PrimeBase-Test" );
  INTEGER myFileHandle = $open( ".\PrimeBase-Test" );
  $close( myFileHandle );
  $delete( ".\PrimeBase-Test" );



  $create( "./PrimeBase-Test" );
  INTEGER myFileHandle = $open( "./PrimeBase-Test" );
  $close( myFileHandle );
  $delete( "./PrimeBase-Test" );
 
Note:

Also see the introductory information provided at the beginning of this appendix (B.5 File I/O Functions) about platform-specific differences in the usage of the functions described here.
 


INTEGER $openreadonly( <file> )

Open a text file for "read only".

<file> - name of the file (including filesystem path if necessary) to be opened in read-only mode.

This function takes as input a string value, that represents the path of the file to be opened. Returned is an INTEGER value which is to be used as a file handle for subsequent read and close operations. Note that there is no special file type, a normal INTEGER is used as file handle.

Files opened are globally accessible, and as a result, files opened in procedures are not automatically closed on return from the procedure. A maximum of 10 files may be opened by a DAL session. Valid file handles are in the range zero to 9 (inclusive). When a session is closed, all open files are closed as well.


VARBIN $read( <handle>, <length>, <offset> )

Read 'length' bytes at a particular offset from a previously opened file.

Additionally this function can be used to set the current position in the specifed file.

<handle> - an INTEGER value previously returned by $open() or $openreadonly().
<length> - the number of bytes to be read.
<offset> - the offset to start reading at.

The first byte in the file has offset 0.

To make the end of a file the current position, use a statement like the following:

  $read( myFile, 0, $geteof( myFile ) );
 
See $write() for an example of how to use this function.


VARCHAR $readline( <handle> )

Read a line from a previously opened file.

<handle> - an INTEGER value previously returned by $open() or $openreadonly().

This function returns the next line of an open file.The end-of-line indicator is not returned as part of the string. There is no limit to the length of a line. $readline() returns $null when the END OF FILE is reached.

Example

  /*
  ** Let's create a file named "PrimeBase-Test", open it,
  ** write one line of data into it and close it again.
  **
  ** Open the file again, read and print its first line,
  ** close the file and delete it again.
  */



  $create( ":PrimeBase-Test" );

  INTEGER myFileHandle = $open( ":PrimeBase-Test" );

  $writeline( myFileHandle,
              "This is just a line, just a line." );

  $close( myFileHandle );

  myFileHandle = $open( ":PrimeBase-Test" );

  PRINT $readline( myFileHandle );

  $close( myFileHandle );

  $delete( ":PrimeBase-Test" );




  $create( ".\PrimeBase-Test" );

  INTEGER myFileHandle = $open( ".\PrimeBase-Test" );

  $writeline( myFileHandle,
              "This is just a line, just a line." );

  $close( myFileHandle );

  myFileHandle = $open( ".\PrimeBase-Test" );

  PRINT $readline( myFileHandle );

  $close( myFileHandle );

  $delete( ".\PrimeBase-Test" );




  $create( "./PrimeBase-Test" );

  INTEGER myFileHandle = $open( "./PrimeBase-Test" );

  $writeline( myFileHandle,
              "This is just a line, just a line." );

  $close( myFileHandle );

  myFileHandle = $open( "./PrimeBase-Test" );

  PRINT $readline( myFileHandle );

  $close( myFileHandle );

  $delete( "./PrimeBase-Test" );


  Screen output:

  This is just a line, just a line.
 


$rename( <from_file>, <to_file> )

Rename a file.

<from_file> - name of the file (including filesystem path if necessary) to be renamed.
<to_file> - new name of the file (including filesystem path if necessary).

This function cannot be used to move files.

Example

  /*
  ** Let's simply create a file in the directory where the
  ** iDAL session or server console you are using was
  ** started, rename the newly created file, and delete it
  ** again immediately
  */



  $create( ":PrimeBase-Test" );
  $rename( ":PrimeBase-Test", ":PrimeBase-Test1" );
  $delete( ":PrimeBase-Test1" );



  $create( ".\PrimeBase-Test" );
  $rename( ".\PrimeBase-Test", ".\PrimeBase-Test1" );
  $delete( ".\PrimeBase-Test1" );



  $create( "./PrimeBase-Test" );
  $rename( "./PrimeBase-Test", "./PrimeBase-Test1" );
  $delete( "./PrimeBase-Test1" );
 
Note:

Also see the introductory information provided at the beginning of this appendix (B.5 File I/O Functions) about platform-specific differences in the usage of the functions described here.
 


$rmdir( <directory> )

Delete a directory.

<directory> - name of the directory (including filesystem path if necessary) to be deleted.

See $mkdir() for an example demonstrating this function.


$seteof( <handle>, <length> )

Set the length of a file.

<handle> - an INTEGER value previously returned by $open().
<length> - the length to set the file to.

Example

  /*
  ** Let's create a test file to work with.
  */

  VARCHAR myFilename = "PrimeBase-DAL-Test";

  $create( myFilename );


  /*
  ** Open the file, set it to 10KB size, close the file
  ** again.
  */

  INTEGER myFile = $open( myFilename );

  $seteof( myFile, 10 * 1024 );

  $close( myFile );

  PRINT "File '" + myFilename + "' set to 10KB size.";


  /*
  ** Let's cleanup again
  */

  $delete( myFilename );


  Screen output:

  File 'PrimeBase-DAL-Test' set to 10KB size.
 


$write( <handle>, <data>, <offset> )

Write a BINARY value to a particular offset in a previously opened file.

<handle> - an INTEGER value previously returned by $open() or $openreadonly().
<data> - a VARBIN value to be written.
<offset> - offset where the data is to be written at. If omitted, the current position in the file is assumed.

If the line needs to be appended to the end of the file, $read() should be used to set the current position in the file to the file end first.

Example

  VARBIN myVARBIN = VARBIN( "This is a test." );

  $create( "PrimeBase-Test-File" );

  INTEGER myFile = $open( "PrimeBase-Test-File" );

  $write( myFile, myVARBIN, 1 );

  PRINT $format( "%x", $read( myFile, 19, 1 ) ),
        "=",
        VARCHAR( $read( myFile, 19, 1 ) );


  /*
  ** Let's cleanup again
  */

  $close( myFile );

  $delete( "PrimeBase-Test-File" );


  Screen output:

  54686973206973206120746573742e = This is a test.
 


$writeline( <handle> ',' <line> )

Write a line to a previously opened file at the current position.

Warning

This function sets the END OF FILE to the end of <data>, thus effectively truncating any previously trailing data.
<handle> - an INTEGER value previously returned by $open() or $openreadonly().
<line> - a VARCHAR value containing the text of the line to be written (do not include the end-of-line character(s)).

If the line needs to be appended to the end of the file, $read() should be used to set the current position in the file to the end of the specified file first.

See $readline() for an example of how to use this function.


B.6 Miscellaneous Functions



LONGCHAR $base64encode( <data> )

This function takes a LONGBIN or LONGCHAR , and returns it as a base64 encoded LONGCHAR

<data> - the data to be base64 encoded.


VARCHAR $encrypt(password)

This function returns the string passed in as a 32 character encrypted string.

Example

  
  print $encrypt("PrimeBase");
 

  Screen output:
  A290CA33DB8C3BB20D513BB46A62DAE3
 



VARCHAR $errorstring( <errno> )

This function takes a single integer arguments, and returns the associated error string.

<errno> - an error number.


VARCHAR $getenv( <file>, <var_id> )

Retrieve the value of a particular environment variable.

<file> - must be the name of an environment file: 'server.env', 'webdal.env', etc.
<var_id> - is the ID of the environment variable (an INTEGER value).

See $putenv() for an example of how to use this function.


VARCHAR $md5( <public-part>[, <private-part>])

Encodes the two input parameters using the "RSA Data Security, Inc MD5 Message-Digest Algorithm" returning the digest as a hex string.


TIMESTAMP $now()

This function returns the current time as a TIMESTAMP value. It takes no arguments. Note that this is the time on the client machine, not the server.

Example

  TIMESTAMP start = $now();

  SELECT * FROM Golfers;

  INTEGER delta = $now() - start;

  PRINT "Seconds needed for the execution of the given";

  PRINT "SELECT:", delta;


  Screen output:

  Seconds needed for the execution of the given
  SELECT: 1
 
Note:

The actual number of seconds needed may vary, depending on the performance of your hardware.
 


 


$putenv( <file>, <var_id>, <value> )

Set the value of a particular environment variable.

<file> - must be the name of an environment file: 'server.env', 'webdal.env', etc.
<var_id> - is the ID of the environment variable (an INTEGER value).
<value> - is the value to be set.

Example

  VARCHAR envValue;

  envValue = $getenv( "client.env", 155 );

  $putenv( "client.env", 155, "Setup" );

  PRINT "Client Scripts Folder changed to 'Setup'.";

  PRINT "Changing back to previous value '" +
        envValue + "'.";

  $putenv( "client.env", 155, envValue );


  Screen output:

  Client Scripts Folder changed to 'Setup'.
  Changing back to previous value '..\Scripts'.
 


VARCHAR $ostype()

This function returns the operating system type that the program is currently executing on. One of: "MAC", "UNIX", or "WIN".

Example

  
  print $ostype();
 

  Screen output (Mac):
  MAC
  Screen output (Windows):
  WIN
  Screen output (Unix):
  UNIX
 


 


$sendmail( <from_address>, <recipients>, <subject>, <email_body>[, <content_type>] )

Sends an e-mail message. The body of the message is assumed to be plain text if the content type is not spacified. If the content type is specified then it is the resposibility of the user to make sure that the body of message matches the content type. The mail server's location is taken fron the PBVM environment  file variable 240.

<from_address> - The address of the person sending the e-mail.

<recipients> - The recipient address list is a comma delimited list of one or more address that the e-mail is to be sent to.

<subject> - The subject is the text to appear in the e-mails subject line.

<email_body> - The email body is the ASCII text to be sent in the email.

<content_type> - Optional; the content type of the email in the following form: "<mime_type>" or "<mime_type>; charset=<charset>".

Example

  declare procedure sendfile(dest, name)
  argument varchar dest = "Big.Bill@IBM.COM";
  argument varchar name;
  {
    $sendmail("Big.Bill@IBM.COM", dest, "File name: "
      + name, varchar($map(name)), "text/plain; charset=ISO-8859-1");
  }
  end procedure sendfile;


  Screen output:

  (none)
 


 


$sendmailxt( <from_address>, [<reply_address>], <recipients>, [<cc_recipients>], [<bcc_recipients>], <subject>, <email_body>[, <content_type>] )

This is an extended version of $sendmail() which optionaly allows the caller to supply a reply address that differs from the sender's address and optionaly supply a CC and BCC recipient list.

<from_address> - The address of the person sending the e-mail.

<reply_address> -(optional) The reply address is just that, the reply address for the e-mail. The default is the senders address.

<recipients> - The recipient address list is a comma delimited list of one or more address that the e-mail is to be sent to.

<cc_recipients> - (optional) The CC recipient address list is a comma delimited list of zero or more address to be CCed.

<bcc_recipients> - (optional) The BCC recipient address list is a comma delimited list of zero or more address to be 'blind' CCed.

<subject> - The subject is the text to appear in the e-mails subject line.

<email_body> - The email body is the ASCII text to be sent in the email.

<content_type> - Optional; the content type of the email in the following form: "<mime_type>" or "<mime_type>; charset=<charset>".

Example

  declare procedure sendfile(dest, name)
  argument varchar dest = "Big.Bill@IBM.COM";
  argument varchar name;
  {
    // Bob Small will get a copy of this e-mail.
    $sendmailxt("Big.Bill@IBM.COM", , dest, "bob.small@IBM.COM", , "File name: "
      + name, varchar($map(name)), "text/plain; charset=ISO-8859-1");
  }
  end procedure sendfile;


  Screen output:

  (none)
 


$yield()

Returns control immediately to the application after giving other processes the chance to run.

Use this function for example in a timing loops in order not to waste too much CPU cycles.

Example

  DECLARE PROCEDURE wait(seconds)

  ARGUMENT INTEGER seconds;
  {
    TIMESTAMP until = $now() + seconds;

    WHILE ( $now() < until )
    {
      $yield();
    }
  }
  END PROCEDURE wait;

  PRINT "Starting to wait 10 seconds...";

  wait( 10 );

  PRINT "Finished waiting 10 seconds.";


  Screen output:

  Starting to wait 10 seconds...
  Finished waiting 10 seconds.
 

 


C. Golfers Database

In some of the examples used in this reference manual, the golfers database is used to demonstrate the various commands of PrimeBaseTalk.

A brief description of the tables in this database is given here, followed by the creation script of the database, which also insert sample data which the examples work with.

You can copy and paste this script into a texteditor and store it in a file so it can be executed using the EXECUTE FILE command from within the PrimeBase Automation Client (PBAC) or the PrimeBase SQL Database Server console for example.
 
 

Database Description

The golfers database consists of six tables storing information on golfers, golf clubs, golf courses, competitions, results of competitions and scores.

Table - Golfers

The golfers table contains one entry for each golfer.
 
 
 

Column

Description

ID An identity number for each golfer.
This number is taken from the domain GolferID, which has a serial default defined on it.
The primary key for this table is defined on this column, as is an index.
SurName The last name of the golfer.This value is taken from the domain NameType, which is ordered using a case insensitive system collating sequence.
FirstNames The first names of each golfer. This value is taken from the domain NameType, whose values are defined to be case insensitive. This is done by specifying that the domain is to be ordered using the case insensitive system collating sequence.
Name This is a composite column, which takes its values from the columns, SurName and FirstName. This column is a candidate key for the table, which means the composite values of the column are table-wide unique.
Title This column stores the title of each golfer: for example, whether the golfer is a Mr., Mrs., Miss, etc., etc.
Gender The sex of each golfer. A rule has been defined on this column, allowing only the values 'M' and 'F'.
Nationality The nationality of each golfer. All values in this column are taken from the domain NameType.
DateOfBirth The date of birth of each golfer.
Status The status of each golfer; whether he/she is an amateur, or professional. All values in this column are taken from the domain StatusType, which has a rule define on it, allowing only the following values: Amateur, Pro and Pro/Am. The value Pro/Am, can be applied to competitions only, and means that the competition includes both amateur and professional golfers. Note that the values are casesensitive.
Handicap The handicap for each golfer is noted here. All values in this column are taken from the domain HandicapType. A rule has been defined on this domain, allowing only those values in the range between 36 and -5 (inclusive).
MemberOfClub This is an identity number for the club in which each golfer plays. A foreign key is defined on this column. All values are taken from the domain ClubID. The value may be $null if the player is not a member of any club.
Earnings This columns contains the amount of money this golfer has earned so far.

Table - Clubs

The Clubs table contains one entry for each club to which the golfers belong, or at which competitions take place.
 
 
 

Column

Description

ID An identity number for each club. All values are taken from the domain ClubID. The primary key for the table is defined on this column, as is an index.
Name The name of the club. All values are taken from the domain NameType. A candidate key is defined on this column.
Address The address of the club. The data type of this column is VARCHAR, with a maximum size of 120 characters.
City The city in which the club is situated.
Country The country in which the club is situated.
Founded The date when the club was founded.
Professional The ID of the professional golfer who works at the club. A foreign key is defined on this column. The foreign key automatically references the Golfers table over the domain GolferID.
NoOfCourses The number of golf courses at the club.
NoOfMembers The total number of members that belong to the club.

Table - Courses

Each club has a number of courses. The details of each hole of each course, are stored in the Courses table.
 
 
 

Column

Description

Club The ID of the club, to which this course belongs. The foreign key for the table is defined on this column.
Course The number of the course. All values are taken from the domain CourseNO, which has a rule defined on it, ensuring that all values are greater than zero. Courses are numbered 1,2,... up to the total number of courses for that particular club.
Hole The number of the hole on the course. All values are taken from the domain HoleNO. A rule is defined on this domain, allowing only number in the range 1 to 18.
Key A composite column, that takes its values from the simple columns, Club, Course, and Hole, in that order. The primary key for the table is defined on this composite column, as is an index.
Distance The total distance of the hole in meters.
Par The average number of strokes it takes a professional player to complete the hole. A rule is defined on this column, allowing only values 3, 4, and 5. A default has also been defined on this column, that automatically sets the par for a hole at 4.
Stroke This is the difficulty rating for each hole. This rating is a value between 1 and 18, (inclusive). All values are taken from the domain HoleNo, which has a rule enforcing this restriction.
Description A description of the hole. The data type of this column is VARCHAR, and the description may not be more than 120 characters long.

Table - Competitions

The Competitions table contains information on all competitions played by the golfers.
 
 
 

Column

Description

ID An identity number for each competition. All values are taken from the domain CompetitionID. The primary key for the table is defined on this column, as is the index.
Name The name of the competition. All values are taken from the domain NameType. A candidate key for the table is defined on this column.
Status The status of the competition; whether it is amateur, professional, or whether amateurs and professionals play together. All values are taken from the domain StatusType, which has a rule defined on it, allowing only the above values.
Month The month in which the competition takes place. The data type of this column is SMINT. It is not DATE, as this is a record of an annual competition, therefore there is no need to state a year.
Day The day on which the competition takes place. The data type of this column is SMINT.
Club The ID of the club where the competition takes place. All values are taken from the domain ClubID. A foreign key is defined on this column, that automatically references the Clubs table of the domain ClubID.
Course The number of the course where the competition takes place.All values are taken from the domain CourseNO.
NoOfRounds The number of rounds in the competition. This column has a default defined on it, setting the value automatically to 4. All values are taken from the domain RoundNO.
Scoring This column records the method of scoring at each competition. A rule is defined on this column that allows the scoring values, "Medal" (an absolute score count), "Stableford" (a point system), "Skins" (a game in which golfers play for money on each hole), and "PlusMinus" (another type of point scoring system)

Table - Results

Each competition has a number of results. The details are stored in the Results table.
 
 
 

Column

Description

Year The year the competition took place. The data type of this column is SMINT.
Competition The ID of the competition in question. A foreign key is defined on this column referring to the Competitions table.
Placing The placing (1st, 2nd, 3rd...) obtained by a golfer who played in the competition.
Key This is a composite column, which takes its values from the simple column, Year, Competition and Place. The primary key for the table is defined on this column, as is the index.
Golfer The ID of the golfer who played in the competition. A foreign key is defined on this column.
TotalScore The sum of the golfer's score for each round of the competition.
Points The value here depends on the type of competition. For example, in a Medal competition the value is the number of strokes above (a positive value) or below (a negative value) par at the end of the competition. In a Stableford competition, this field contains the total number of Stableford points obtained.
Winnings How much the golfer won in the competition. The data type of this column is MONEY[12,2].

Table - Scores

The Scores table contains the score for each hole obtained by each player in each competition.
 
 
 

Column

Description

Year The year in which the player took part in the competition.
Competition The ID for the competition. All values are taken from the domain CompetitionID. A foreign key is defined on this column, that references the Competitions table.
Golfer The ID for the golfer who took part in the competition. All values are taken from the domain GolfersID. A foreign key for the table is defined on this column.
Round The number of the round, in which this hole was played. All values are taken from the domain RoundNO.
Hole The number of the hole played.
Key This is a composite column, which takes its values from the simple columns, Year, Competition, Golfer, Round, and Hole. The primary key for the table is defined on this column, as is the index.
Score The number of strokes it took the golfer to play the hole. There is a rule defined on this column, that states that all values must be greater than zero.
HoledOut This is a column of BOOLEAN type. If FALSE, this means that the player did not complete the hole. In a medal competition this would disqualify the player, but in other types of Competitions, this would only mean the player obtains the worst possible score for the hole.
Points The number of points achieved at the hole. The value here depends on the coring system used in the competition.

Create Script

This script creates the golfers database and fills it with sample data:
 
 
/*
** GOLFERS:
*/
CREATE DATABASE "Golfers";

OPEN   DATABASE "Golfers";

CREATE COUNTER INTEGER GolferCnt = 1;

CREATE COUNTER INTEGER ClubCnt = 1;

CREATE COUNTER INTEGER CompetitionCnt = 1;

CREATE DOMAIN GolferID INTEGER NOT NULL;

CREATE DEFAULT GolferDef ON DOMAIN GolferID
AS SERIAL GolferCnt;

CREATE DOMAIN CompetitionID INTEGER NOT NULL;

CREATE DEFAULT CompetitionDef ON DOMAIN CompetitionID
AS SERIAL CompetitionCnt;

CREATE DOMAIN ClubID INTEGER;

CREATE DEFAULT ClubDef ON DOMAIN ClubID
AS SERIAL ClubCnt;

CREATE DOMAIN NameType VARCHAR[55] ORDER
AS CASE INSENSITIVE;

CREATE DOMAIN StatusType CHAR[8];

CREATE RULE StatusRule ON StatusType AS StatusType
IN ( 'Amateur', 'Pro', 'Pro/Am' );

CREATE DOMAIN CourseNO SMINT;

CREATE RULE CourseRule ON CourseNO AS CourseNO > 0;


/*
** Handicaps are stored as value that is subtracted from
** the final score:
*/

CREATE DOMAIN HandicapType SMINT;

CREATE RULE HandicapRule ON HandicapType
AS HandicapType BETWEEN 36 AND -5;

CREATE DOMAIN RoundNO SMINT;

CREATE RULE RoundRule ON RoundNO
AS RoundNO BETWEEN 1 AND 4;

CREATE DOMAIN HoleNO SMINT;

CREATE RULE HoleRule ON HoleNO
AS HoleNO BETWEEN 1 AND 18;


/*
** The golfers table contains one entry for each golfer.
*/

CREATE TABLE Golfers
(
        ID GolferID NOT NULL,
        SurName NameType NOT NULL,
        FirstNames NameType NOT NULL,
        Name (SurName, FirstNames),
        Title CHAR[10],
        Gender CHAR[1] NOT NULL,
        Nationality NameType,
        DateOfBirth DATE,
        Status StatusType,
        Handicap HandicapType,
        MemberOfClub ClubID,
        Earnings MONEY[12,2]
);

CREATE PRIMARY KEY GolfersPk ON Golfers.ID;

CREATE CANDIDATE KEY GolferNameCk ON Golfers.Name;

CREATE FOREIGN KEY GolferClubFk ON Golfers.MemberOfClub;

CREATE INDEX GolfersIndex ON Golfers (ID);

CREATE RULE GenderRule ON Golfers AS Gender
IN ( 'M', 'F' );


/*
** The Clubs table contains one entry for each club to
** which golfers belong, or at which competitions take
** place.
*/

CREATE TABLE Clubs
(
        ID ClubID NOT NULL,
        Name NameType NOT NULL,
        Address VARCHAR[120],
        City NameType,
        Country NameType,
        Founded DATE,
        Professional GolferID,
        NoOfCourses SMINT NOT NULL,
        NoOfMembers INTEGER NOT NULL
);

CREATE PRIMARY KEY ClubsPk ON Clubs.ID;

CREATE CANDIDATE KEY ClubNameCk ON Clubs.Name;

CREATE FOREIGN KEY ClubProFk ON Clubs.Professional
ON UPDATE CASCADE ON DELETE CASCADE;

CREATE INDEX ClubsIndex ON Clubs (ID);


/*
** Each club has a number of courses. The details of each
** hole of each course, are stored in the Courses table
*/

CREATE TABLE Courses
(
        Club ClubID NOT NULL,
        Course CourseNO NOT NULL,
        Hole HoleNO NOT NULL,
        Key ( Club, Course, Hole ),
        Distance SMINT,
        Par SMINT,
        Stroke HoleNO,
        Description VARCHAR[120]
);

CREATE PRIMARY KEY CoursesPk ON Courses.Key;

CREATE FOREIGN KEY CourseClubFk ON Courses.Club;

CREATE INDEX CoursesIndex ON Courses (Club, Course, Hole);

CREATE RULE ParRule ON Courses AS Par IN ( 3, 4, 5 );

CREATE DEFAULT ParDef ON Courses.Par AS 4;


/*
** The Competitions table contains information of all
** competitions played by the golfers.
*/

CREATE TABLE Competitions
(
        ID CompetitionID NOT NULL,
        Name NameType NOT NULL,
        Status StatusType NOT NULL,
        Month SMINT,
        Day SMINT,
        Club ClubID,
        Course CourseNO,
        NoOfRounds RoundNO,
        Scoring CHAR[20]
);

CREATE PRIMARY KEY CompetitionsPk ON Competitions.ID;

CREATE CANDIDATE KEY CompetitionNameCk
ON Competitions.Name;

CREATE FOREIGN KEY CompetitionClubFk ON Competitions.Club;

CREATE INDEX CompetitionsIndex ON Competitions (ID);

CREATE DEFAULT NoOfRoundsDef ON Competitions.NoOfRounds
AS 4;

CREATE RULE ScoringRule ON Competitions AS Scoring IN
( 'Medal', 'Stableford', 'Skins', 'PlusMinus' );


/*
** Each competition has a number of results. The details
** are stored in the Results table.
*/

CREATE TABLE Results
(
        Year SMINT NOT NULL,
        Competition CompetitionID NOT NULL,
        Placing SMINT NOT NULL,
        Key ( Year, Competition, Placing ),
        Golfer GolferID,
        TotalScore SMINT,
        Points SMINT,
        Winnings MONEY[10,2]
);

CREATE PRIMARY KEY ResultsPk ON Results.Key;

CREATE FOREIGN KEY ResultGolferFk ON Results.Golfer;

CREATE INDEX ResultsIndex
ON Results (Year, Competition, Placing);


/*
** The Scores table contains the score for each hole
** obtained by each player in each competition.
*/

CREATE TABLE Scores
(
        Year SMINT NOT NULL,
        Competition CompetitionID NOT NULL,
        Golfer GolferID NOT NULL,
        Round RoundNO NOT NULL,
        Hole HoleNO NOT NULL,
        Key ( Year, Competition, Golfer, Round, Hole ),
        Score SMINT,
        HoledOut BOOLEAN,
        Points SMINT
);

CREATE PRIMARY KEY ScoresPk ON Scores.Key;

CREATE FOREIGN KEY ScoreGolferFk ON Scores.Golfer;

CREATE INDEX ScoresIndex
ON Scores (Year, Competition, Golfer, Round, Hole);

CREATE RULE ScoreRule ON Scores AS Score > 0;


/*
** The View, GolfersAmateurs, includes all those golfers
** who have the Status, "Amateur"
*/

CREATE VIEW GolfersAmateurs
AS SELECT ID, Name, Title, Handicap, Status
FROM Golfers
WHERE Status = "Amateur" WITH CHECK OPTION;


/****************************
** Create 1st club
*/


/***************
** Create Club
*/

INSERT INTO Clubs
(
        ID,
        Name,
        Address,
        City,
        Country,
        Founded,
        Professional,
        NoOfCourses,
        NoOfMembers
)
VALUES
(
        1,
        "Sterling Club Hamburg",
        "20000 Hamburg, 12 Street of Joy",
        "Hamburg",
        "Germany",
        "01/01/1998",
        1,
        2,
        2
);


/***************
** Create 2 Courses
*/

INSERT INTO Courses
(
        Club,
        Course,
        Hole,
        Distance,
        Par,
        Stroke,
        Description
)
VALUES
(
        1,
        1,
        1,
        200,
        5,
        10,
        "Not exactly easy, but kept well in shape, this " +
        "course offers an interesting arrangement of " +
        "different elements."
);

INSERT INTO Courses
(
        Club,
        Course,
        Hole,
        Distance,
        Par,
        Stroke,
        Description
)
VALUES
(
        1,
        2,
        2,
        200,
        5,
        10,
        "Not exactly difficult this course offers an " +
        "interesting arrangement of different elements."
);


/***************
** Create 2 Golfers
*/

INSERT INTO Golfers
(
        ID,
        SurName,
        FirstNames,
        Title,
        Gender,
        Nationality,
        DateOfBirth,
        Status,
        Handicap,
        MemberOfClub,
        Earnings
)
VALUES
(
        1,
        "Bee",
        "Buzz",
        "Mr.",
        "M",
        "dutch",
        "10/01/1960",
        "Pro",
        20,
        1,
        "$5000"
);

INSERT INTO Golfers
(
        ID,
        SurName,
        FirstNames,
        Title,
        Gender,
        Nationality,
        DateOfBirth,
        Status,
        Handicap,
        MemberOfClub,
        Earnings
)
VALUES
(
        2,
        "Dry",
        "Tumble",
        "Mrs.",
        "F",
        "british",
        "12/10/1959",
        "Pro",
        -3,
        1,
        "$3,999"
);


/***************
** Create Competition
*/

INSERT INTO Competitions
(
        ID,
        Name,
        Status,
        Month,
        Day,
        Club,
        Course,
        NoOfRounds,
        Scoring
)
VALUES
(
        1,
        "Sterling Club Competition",
        "Pro/Am",
        6,
        7,
        1,
        1,
        4,
        "Medal"
);


/***************
** Create Results
*/

INSERT INTO Results
(
        Year,
        Competition,
        Placing,
        Golfer,
        TotalScore,
        Points,
        Winnings
)
VALUES
(
        1990,
        1,
        1,
        1,
        1000,
        2,
        "$1111.11"
);

INSERT INTO Results
(
        Year,
        Competition,
        Placing,
        Golfer,
        TotalScore,
        Points,
        Winnings
)
VALUES
(
        1990,
        1,
        2,
        2,
        500,
        3,
        "$333.33"
);


/****************************
** Create 2nd club
*/


/***************
** Create Club
*/

INSERT INTO Clubs
(
        ID,
        Name,
        Address,
        City,
        Country,
        Founded,
        Professional,
        NoOfCourses,
        NoOfMembers
)
VALUES
(
        2,
        "Astronauts Golf Club",
        "2001 Oddyssey, 12 Cape Canaveral Street",
        "Space-City",
        "Universe",
        "01/01/2001",
        1,
        2,
        2
);


/***************
** Create 2 Courses
*/

INSERT INTO Courses
(
        Club,
        Course,
        Hole,
        Distance,
        Par,
        Stroke,
        Description
)
VALUES
(
        2,
        1,
        1,
        200,
        5,
        10,
        "Not exactly easy, but kept well in shape, this " +
        "course offers an interesting arrangement of " +
        "different elements."
);

INSERT INTO Courses
(
        Club,
        Course,
        Hole,
        Distance,
        Par,
        Stroke,
        Description
)
VALUES
(
        2,
        2,
        2,
        200,
        5,
        10,
        "Not exactly difficult this course offers an " +
        "interesting arrangement of different elements."
);


/***************
** Create 2 Golfers
*/

INSERT INTO Golfers
(
        ID,
        SurName,
        FirstNames,
        Title,
        Gender,
        Nationality,
        DateOfBirth,
        Status,
        Handicap,
        MemberOfClub,
        Earnings
)
VALUES
(
        3,
        "Dumsdee",
        "Humsdee",
        "Mrs.",
        "F",
        "french",
        "01/21/1972",
        "Pro",
        20,
        2,
        "$10,999.99"
);

INSERT INTO Golfers
(
        ID,
        SurName,
        FirstNames,
        Title,
        Gender,
        Nationality,
        DateOfBirth,
        Status,
        Handicap,
        MemberOfClub,
        Earnings
)
VALUES
(
        4,
        "Head",
        "Fish",
        "Mr.",
        "M",
        "canadian",
        "02/07/1954",
        "Pro",
        -2,
        2,
        "$3,520"
);


/***************
** Create Competition
*/

INSERT INTO Competitions
(
        ID,
        Name,
        Status,
        Month,
        Day,
        Club,
        Course,
        NoOfRounds,
        Scoring
)
VALUES
(
        2,
        "Space Competition",
        "Pro",
        7,
        6,
        2,
        2,
        2,
        "Medal"
);


/***************
** Create Results
*/

INSERT INTO Results
(
        Year,
        Competition,
        Placing,
        Golfer,
        TotalScore,
        Points,
        Winnings
)
VALUES
(
        1990,
        2,
        1,
        2,
        823,
        1,
        "$2000"
);

INSERT INTO Results
(
        Year,
        Competition,
        Placing,
        Golfer,
        TotalScore,
        Points,
        Winnings
)
VALUES
(
        1990,
        2,
        2,
        4,
        503,
        3,
        "$600"
);
 

 

email: info@primebase.net

www: http://www.primebase.net

Copyright © 2008, PrimeBase Systems GmbH. All rights reserved.
 
 
 
 


email: info@primebase.net

www: http://www.primebase.net

Copyright © 2008, PrimeBase Systems GmbH. All rights reserved.