SQLOPEN Verb - Open SQL Channel BBj
For this topic's original documentation, seeSQLOPEN Verb - Open SQL Channel.
BBj-Specific Information
ODBC Data Sources
Java 8 removes built-in support for ODBC database connectivity from the Java Virtual Machine. As a result, BBj no longer supports accessing ODBC data sources. However, most modern databases now offer JDBC access so this should not affect most users. In addition, version 15.00 adds built-in support for accessing Microsoft Access databases. For more information see DB-Accessing Third Party Databases.
In BBj 3.0 and higher, when opening a connection to an ODBC data source in BBj, in most cases a system DSN or a file DSN must be specified. BBjServices runs as a service on Windows NT/2000/XP. Therefore, BBjServices does not have access to most user DSNs. However, access will be provided to user DSNs that are available to the user and are used to start BBjServices. Remember that this may or may not be the same user that WAS used to log into the machine.
For example, if "bbjuser" is used to run BBjServices and there
is a user DSN created called "jsmith", there would NOT
be access to that DSN. If, however, there is a user DSN that was
created by "bbjuser", there would be access to it from BBj.
JDBC Data Sources
BBj can also connect to any JDBC data source. To connect to a JDBC data source:
-
Add the JDBC driver JAR file(s) to the BBj CLASSPATH - see the Enterprise Manager - Server Information (Classpath Tab) for information on adding a JAR to the CLASSPATH. Make sure to restart BBjServices after adding any JAR files.
-
In the BBj program, load the JDBC driver class that implements the java.sql.Driver interface (consult the third party documentation for the specific JDBC driver for details) using a statement similar to the following:
java.lang.Class.forName("com.mydriver.DriverClass")
-
Specify the JDBC connect URL in the SQLOPEN:
SQLOPEN(1, mode="user=uname, password=mypass")"jdbc:thecompany:myserver://theDB"
SQLOPEN Considerations Inside Stored Procedures
Stored procedures consist of BBj code that is executed in an interpreter inside the database server when the procedure is called from an SQL statement. Prior to BBj 15.01, if the stored procedure needs to return an SQL result set as the result of the procedure, the SQL channel that is returning the result must tell the interpreter that it is inside a stored procedure so that the channel does not get closed when the stored procedure code is finished executing. To do this, use the MODE="PROCEDURE" option on the SQLOPEN call. However, as of BBj 15.01, this is now the default and no longer needs to be explicitly included in the SQLOPEN call.
For example the following is code from a stored procedure that takes an item number as an input parameter and then executes a SELECT statement that uses that item number as filter criteria for the query. It then returns a result set containing the results of the query:
REM Get
an object containing the parameter values passed into the procedure.
sp! = BBJAPI().getFileSystem().getStoredProcedureData()
REM Get the item number passed into the procedure
itemNum$ = sp!.getParameter("ITEM_NUM")
REM Open an SQL channel to run the query on. We want to use the same database.
chan = SQLUNT
SQLOPEN(chan, MODE="PROCEDURE")sp!.getDatabaseName()
REM Run the query that will get the appropriate information
SQLPREP(chan)"select c.cust_num, cvs(c.last_name,3) + ', ' + cvs(c.first_name,3)
: cust_name from customer c, order_header o, order_line l
: where l.item_num = ? and o.order_num = l.order_num and c.cust_num = o.cust_num"
SQLEXEC(chan)itemNum$
REM Set the returning result set to be the result of the query.
sp!.setResultSet(chan)