Welcome!

PowerBuilder Authors: Chris Pollach, Yeshim Deniz, Jayaram Krishnaswamy, Kevin Benedict, Avi Rosenthal

Related Topics: PowerBuilder

PowerBuilder: Article

Dynamic SQL with Sybase's Open Client

Beyond PowerBuilder's dynamic querying capabilities

Processing Error Messages
When an error occurs in the database a message queue is updated. This message queue can be accessed a few different ways using the ct_diag function.

Call ct_diag like so:

ct_diag(SQLCA.DBHandle(), CS_GET, SQLCA_TYPE, 1, strsqlca)

strsqlca is a structure that can be seen in Listing 7. Use the elements of this structure to display an appropriate error message to the user.

Alternatively you can use ct_diag with the CS_SERVERMSG_TYPE message type instead of SQLCA_TYPE. Using CS_SERVERMSG_TYPE will give you more information about the error. Use the function of_DisplayShowPlan in the sample application provided as an example of how to use it.

Processing Show Plans
Yes, you can display show plan information for your SQL statements. To do so you have to set some query options. Use ct_options to do this:

ct_options(SQLCA.DBHandle(), CS_SET, CS_OPT_SHOWPLAN, CS_TRUE, CS_UNUSED, ll_null)
ct_options(SQLCA.DBHandle(), CS_SET, CS_OPT_NOEXEC, CS_TRUE, CS_UNUSED, ll_null)

Now when a command is sent to the database using ct_send, the results will be put in the database's message queue. Accessing the information is similar to what was described in the "Processing Error Messages" section.

If you remember, in Listing 4 we processed a result type called CS_CMD_DONE. This is a great place to process and display the show plan data (see Listing 8 for an example). All information is retrieved in the text element of the cs_servermsg structure (seen in Listing 7).

Using n_cst_openclient from the Sample Application
In theory, the non-visual object n_cst_openclient should be ready to use in your application. n_cst_openclient contains three events and a few functions to help you interact with your ASE database.

EVENTS

  • ue_error: Gets triggered whenever an error is encountered.
  • ue_newrow: Gets triggered whenever a new row is inserted in the active datawindow.
  • ue_resultchanged: Gets triggered when an executed SQL statements result type changes. This is where you'd change the active datawindow so you can see results from a batch script.
FUNCTIONS
  • of_cancel: Call this function to cancel the current database operation.
  • of_execute: Use this function to send an SQL statement to the database.
  • of_setactivedw: Sets the active datawindow.
  • of_setparentwindow: Sets a pointer to the parent window.
Conclusion
The list of items that can be processed can go on and on. The sample application provided should offer you enough information to keep you going. Once I learned how to work with the Open Client API, I found that the ODBC API wasn't all that different and a lot of my knowledge was transferable. I'd imagine that this would be the same for any DBMS.

More Stories By Brad Wery

Brad Wery is the President of Werysoft Inc. (www.werysoft.com) and the creator of www.PowerToTheBuilder.com, a site dedicated to helping PowerBuilder developers create visually appealing user interfaces. He has been a member of TeamSybase since 2006 and is an active participant in the PowerBuilder Newsgroups.

Comments (3) View Comments

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


Most Recent Comments
PBDJ News Desk 08/11/06 10:59:29 AM EDT

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?

SYS-CON Brazil News Desk 08/10/06 04:34:57 PM EDT

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?

PBDJ News Desk 08/10/06 01:01:07 PM EDT

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?