Built-in Stored Procedures
GET_RESULT_SET (file_path, template)
In BBj 12.0 and higher, GET_RESULT_SET is a special stored procedure available in all BBj databases (including the system database BBJSYS). This procedure takes as parameters, the location of a data file, and a string template that describes the fields which comprise the record data in the file and returns an SQL result set. Use this stored procedure in reports or SQL queries where no data dictionary definition is yet available for the data file. The SQL engine allows for joining multiple calls to the stored procedure to provide joins between files or tables in a database. The stored procedure can be called from any BBj database, but if there is no database defined for a set of files, connect to the BBJSYS database (built in system database) and execute the stored procedure from there without any need for a data dictionary.
The example uses the following variables to specify the parameters in the stored procedure:
name$ - The location and file name for the BBj/BBx data file to read.
template$ - The string template that describes the record layout of the data in the file. This also describes the result set columns that will be returned from the stored procedure.
Example 1
|
name$ = "C:\Program Files\basis\demos\chiledd\data\CATEGORY" template$ = "PROD_CAT:C(2),DESC:C(30),COST_METHOD:C(5),COST_ACCOUNT:C(8),SALE_ACCOUNT:C(8),INV_ACCOUNT:C(8)" REM Open an SQL channel and execute the SPROC SQLOPEN(1) "BBJSYS" SQLPREP(1) "CALL get_result_set(?, ?)" SQLEXEC(1) name$, template$ DIM rec$:SQLTMPL(1) REM Fetch the first result rec$ = SQLFETCH(1) |
Example 2
|
name$ = "C:\Program Files\basis\demos\chiledd\data\CATEGORY" template$ = "PROD_CAT:C(2),DESC:C(30),COST_METHOD:C(5),COST_ACCOUNT:C(8),SALE_ACCOUNT:C(8),INV_ACCOUNT:C(8)" REM Open an SQL channel and execute the SPROC SQLOPEN(1) "ChileCompany" SQLPREP(1) "SELECT * FROM item i INNER JOIN (CALL get_result_set(?, ?)) p ON i.prod_cat = p.prod_cat" SQLEXEC(1) name$, template$ DIM rec$:SQLTMPL(1) REM Fetch the first result rec$ = SQLFETCH(1) |
GET_RESULT_SET_CSV (file_path, template, recordDelimiter)
In BBj 12.0 and higher, GET_RESULT_SET_CSV is a special stored procedure available in all BBj databases (including the system database BBJSYS). This procedure takes as parameters, the location of a comma separated values (CSV) data file, and a string template that describes the fields for each row in the file and returns an SQL result set. Use this stored procedure in reports or SQL queries where a combination of BBj data files and CSV files is required. The SQL engine allows for joining multiple calls to the stored procedure to provide joins between CSV files and BBj tables in a database. The stored procedure can be called from any BBj database, but if there is no database defined for a set of files, connect to the BBJSYS database (built in system database) and execute the stored procedure from there without any need for a data dictionary.
The example uses the following variables to specify the parameters in the stored procedure:
name$ - The location and file name for the CSV format data file to read.
template$ - The string template that describes the record layout of the result set columns that will be returned from the stored procedure.
recordDelimiter$ - The record delimiter character(s) that indicate the end-of-line for each record in the file.
Example 1
The example below assumes there is a CSV file called mycsvfile.csv and it contain data that looks something like this:
"somevalue", "a value for col2"
"another", "another col2 val"
|
name$ = "mycsvfile.csv" |
GET_RESULT_SET_DSV (file_path, template, field_delimiter, record_delimiter)
In BBj 15.0 and higher, GET_RESULT_SET_DSV is a special stored procedure available in all BBj databases (including the system database BBJSYS). This procedure takes as parameters, the location of a comma-separated values (CSV) data file, a string template that describes the fields for each row in the file, a field delimiter, and a record delimiter and returns an SQL result set. Using the field delimiter, queries can access files that use a field delimiter other than a comma.
The example uses the following variables to specify the parameters in the stored procedure:
name$ - The location and file name for the CSV format data file to read.
template$ - The string template that describes the record layout of the result set columns that will be returned from the stored procedure.
fieldDelimiter$ - The delimiter character that separates each field in the file (e.g. tab, comma, space, etc.).
recordDelimiter$ - The record delimiter character(s) that indicate the end-of-line for each record in the file.
Example 1
The example below assumes there is a CSV file called mycsvfile.csv and it contains data that looks something like this (where the fields are separated by a tab character):
"somevalue" "a value for col2"
"another" "another col2 val"
|
name$ = "mycsvfile.csv" template$ = "COL1:C(10*),COL2:C(20*)" recordDelimiter$ = CHAR(10)+CHAR(13) fieldDelimiter$ = CHAR(9) REM Open an SQL channel and execute the SPROC SQLOPEN(1) "BBJSYS" SQLPREP(1) "CALL get_result_set_csv(?, ?, ?, ?)" SQLEXEC(1) name$, template$, fieldDelimiter$, recordDelimiter$ DIM rec$:SQLTMPL(1) REM Fetch the first result rec$ = SQLFETCH(1) |
BBJ_EXEC_SQL (connect_url, driver, sql, max_results)
In BBj 15.10 and higher, BBJ_EXEC_SQL makes it possible to access data in an alternate BBj database or third party database. Using this system SPROC, SQL statements have the ability to join data from multiple databases with a single SQL statement. This SPROC also provides the ability to import data from another database into a BBj database table using a single SQL statement.
Parameters
| connect_url | A valid JDBC connection URL or the name of a local BBj database. The CALL returns data from this connection. |
| driver | JDBC driver’s java.sql.Driver implementation class. See the 3rd party JDBC driver’s documentation for this information. NOTE: BBj and MS Access databases can use an empty string for this parameter since BBj automatically loads the necessary classes for BBj and MS Access connections. |
| sql | The SQL statement to execute. The SPROC supports any statement type and simply passes the query string directly to the underlying JDBC driver. NOTE: Make sure to escape single quotes per standard SQL single quote escape syntax (double single quotes). |
| max_results | The maximum number of results to return from the CALL to this SPROC. Zero indicates no limit. |
Example 1
This example makes a simple CALL to the BBJ_EXEC_SQL SPROC to return all of the data in a table called MY_TABLE from an MS Access database.
|
connectUrl$ = "jdbc:ucanaccess:///mydatabase/mydata.accdb" |
Example 2
This example shows how to join the results from a third party database table with data from the BBj database. The example demonstrates how results of a CALL statements can be used as a virtual table in a SELECT statement. Using a third party database such as MySQL, SQL Server or Oracle requires adding the appropriate JDBC driver JAR file to the BBjServices configuration. For more information see Enterprise Manager - BBjServices: Java Settings.
|
connectUrl$ = "jdbc:mysql://myserver/mydatabase" |
Example 3
This example demonstrates how to import data from a third party database into a BBj table using a single SQL statement. Using a third party database such as MySQL, SQL Server or Oracle requires adding the appropriate JDBC driver JAR file to the BBjServices configuration. For more information see Enterprise Manager - BBjServices: Java Settings.
|
connectUrl$ = "jdbc:mysql://myserver/mydatabase" |