PowerBuilder

PowerBuilder offers some dynamic querying capabilities. For simple scenarios it works quite well. But what if your scenario isn't simple? What if you wanted to write a database-querying tool that provides stored procedure compilation, ShowPlan display, or the execution of large complicated batch scripts?

I was writing a database-querying tool that was destined to work against our ASE database (and ODBC connections but, that's a whole different article). At first I tried to use the PowerBuilder dynamic SQL functions but found it to be very limiting. That solution was too simple and restrictive for my needs. The only other option I had was to go directly to the Open Client API.

Going directly to the Open Client API is much easier said than done. I'll be honest; it wasn't a simple task. I had a great deal of help from Jim O'Neil of TeamSybase. Once I got going and got used to the way it worked it became smooth sailing.

Sybase Open Client
There are many functions in the Open Client API. Your most valuable resource will be the reference manual provided by Sybase: http://manuals.sybase.com/onlinebooks/group-cn/cng1250e/ctref. Your second most valuable resource will be the header files included in your Open Client installation: C:\sybase\OCS-15_0\include.

Connecting to the Database
This is probably the easiest part of the whole process. Connect to your database using SQLCA (or what ever transaction object you want) just as you would in any other application. You automatically get a handle to the database connection when connecting with a transaction object. This handle can be retrieved by calling the transaction objects DBHandle() function.

Instance Variables
I mentioned the Open Client header files earlier. These header files contain many variable declarations that are needed by the API. Listing 1 contains most of the common ones needed for basic operations. Two of the more important sets are described as "Result Types" and "Data Types." Later I'll discuss how to make use of them.

Local External Functions
Refer to Listing 2 for a list of the local external functions needed to send and retrieve data to and from an ASE database.

• cs_dt_crack: This function is used to convert raw date, datetime, and time values returned from the database so they're human-readable.
• ct_bind: This function binds a database column to a program variable. Refer to the ct_fetch function for more information.
• ct_cancel: Use this function to cancel your connection's currently running process on the database.
• ct_cmd_alloc: Allocates a new command structure for the database connection. Output from this function is used for any other function that requires a command structure parameter. A command structure will essentially be a handle to your SQL statement once sent to the database.
• ct_cmd_drop: Drops the command structure created by ct_cmd_alloc.
• ct_command: Use this function to associate an SQL statement to a command structure.
• ct_con_props: Gets or sets connection level properties.
• ct_describe: We'll use this function to determine the datatype of all the columns returned from the database when we run a select statement.
• ct_diag: Returns messages from the database. You would use this function to return things like errors, print statements, or show plan data.
• ct_fetch: This function is called for every row in a result set. If you run a select statement that returns 10 rows you'll loop 10 times calling this function. Calling this function populates any variables set using ct_bind.
• ct_options: Gets or sets properties at a query level. We'll use this function later on when I describe ShowPlans.
• ct_res_info: Retrieve information for a result set. In my example I'll use it to retrieve the number of columns returned to the application from the database.
• ct_results: This function is used to loop through all the result sets returned from the database. Ct_results lets us execute and display batch SQL script.
• ct_send: This function sends an SQL command to the database for processing. The command is allocated and set using ct_cmd_alloc and ct_command.

Converting Data Types
There are many database datatypes that can be easily mapped to a single PowerBuilder datatype. Refer to Listing 3 for a sample function that does the conversion. This type of conversion is required when we're ready to call ct_bind. Ct_bind can be overloaded to handle many different datatypes. We need to know which version of the ct_bind function to call.

Executing SQL
I've included a sample application that illustrates how to execute a database command and retrieve its results. If you get your hands on the source please review of_Execute in n_cst_openclient.

Review listing 4 to see what's happening at a high level.

Retrieving Row Results
In Listing 4 we saw a result type of CS_ROW_RESULT being processed. This result type is triggered when rows and columns are returned from the database (i.e., the result of a Select statement).

Column information is retrieved using ct_describe and stored in a structure, cs_datafmt (Listing 5). One element of the structure is the datatype. This value will be converted using the code described in Listing 3.

Listing 6 describes at a high level what is happening (again, view the example application to see exactly how it works).

Processing Decimal Data Types
In Listing 6 you'll see that decimal datatypes are bound to a blob variable. This has to be done because PowerBuilder's decimal datatype is too complicated for the ct_bind function. There's more going on behind the scenes than PowerBuilder lets you see. Ct_bind will store the decimal value from the database into a blob; we'll then convert the blob using the built-in Dec function found in PowerBuilder.

There's some setup required before doing the bind as seen here:

blob variable = blob(Space(datafmt.maxlength))
datafmt.format = CS_FMT_NULLTERM
datafmt.datatype = CS_CHAR_TYPE

ct_bind(<command>, <col number>, <datafmt>, <blob variable>, <null>, <null>)

Later when we process the blob variable after calling ct_fetch we just have to cast it to a decimal using the Dec function.

Processing Date, Time, and DateTime Datatypes
As with decimals, dates and times are unique and have to be handled differently than the other datatypes. These datatypes will be bound to a blob variable. Before binding you'll need to initialize the variable like so:

blob variable = Blob("XX")

The magic happens when we convert the blob to an actual date, time, or datetime datatype. To do this we need another structure (cs_daterec from Listing 5) and the functions cs_dt_crack and ct_con_props.

Use ct_con_props to get a handle to the database connections context. This handle will be used in cs_dt_crack like so:

cs_dt_crack(<context>, <datafmt.datatype>, <blob variable>, <cs_daterec structure>)

Using the elements of the cs_daterec structure you can assemble the date, time, or datetime datatype. For example:

DateTime(Date(cs_daterec.dateyear, cs_daterec.datemonth + 1,
cs_daterec.datedmonth), Time(cs_daterec.datehour,
cs_daterec.dateminute,
cs_daterec.datesecond, cs_daterec.datemsecond)).