Welcome!

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

Related Topics: PowerBuilder

PowerBuilder: Article

PowerBuilder and ODBC 101

PowerBuilder and ODBC 101

Your boss pays you a visit and brings you the pathname of a 30MB Adaptive Server Anywhere (ASA) database with a 6MB log file. "Just got this from the New York office; they created it in ASA. Can we look at it?"

Your boss isn't technically oriented, and you are a PowerBuilder guru, right? Gulp! Now you'll have to use the PB Database Painter, Profile Painter, and the Microsoft Open DataBase Connectivity (ODBC) Administrator to "hook it up and look at it."

Strangely enough, many seasoned programmers, those types who can recite external function prototypes from memory, get cold and clammy when they hear "ODBC Administrator," "PB Database Painter," or "PB DB Profile Painter." Granted, only a small percentage of your programming time will be spent in these areas, but the lack of even basic knowledge in any of these tools is a serious problem.

Open Database Connectivity (ODBC) Theory
The concept behind ODBC is to have a simple, generic way to attach front-end applications to databases. By having a virtual machine (VM) for each supported DBMS, ODBC is able to talk to almost any DB on the market. When you attempt to retrieve data, the ODBC middleware takes your PowerScript embedded SQL calls and "converts" them into the proper native syntax for your database. Since it can "speak" generically, it's widely supported and used, at a price.

Because of the VM approach to translation, ODBC is slower than using a direct native interface that doesn't require any conversion (SYC or ORA, for example). Another drawback is that ODBC may not support all the features your DB may have. Remember, the objective was a generic connection tool, not a way to support every feature in every database.

PowerBuilder Database Profile Painter
The PB DB Profile painter gives a hierarchical view of your PowerBuilder database profiles organized by vendor type. The dialog allows you to test, edit, add, or delete a profile. Every PowerBuilder ODBC profile uses a data source created in the Microsoft ODBC Administrator. We're going to make an ODBC profile in PowerBuilder, but first we must make a data source in the MS ODBC Administrator for our profile to use.

Expand the Utilities folder under ODB ODBC and double-click on ODBC Administrator (see Figure 1).

ODBC Administrator
To create a new data source in the ODBC Administrator, you'll need to gather a few pieces of information. The Parameters table is a set of sample parameters for a new data source that connects to the EAS Demo DB V4 database (easdemodb.db). There are many other parameters, but these are the minimum required.

One worth mentioning is the AutoCommit option. Generally, this should be turned off. When it's on, the DBMS will issue an implicit commit after every SQL statement processed. Hence, each line of SQL is considered a committed transaction. We use transactions to allow us to recover from errors and keep the database in a consistent state. If one statement in our transaction fails, we want to roll back all the SQL statements since the beginning of the transaction (assuming we're not using "save points"), leaving our database in a consistent state. When AutoCommit is true, you have no way of explicitly rolling back SQL statements. This would allow an inconsistent database (partial deletes, partial inserts, partial updates, etc.) to exist.

Creating a New Data Source
All the DBMS vendor drivers you have installed will be shown in the Create New Data Source dialog. When you install PowerBuilder, the Adaptive Server Anywhere driver is automatically installed for you, along with a sample ASA database easdemodb.db. In addition, it also creates an ODBC Administrator profile called EAS Demo DB V4 and a PowerBuilder DB Profile called EAS Demo DB V4. By the way, the DB ending in IM stands for a special version of the sample for InfoMaker. Click on the System DSN tab and then click Add (see Figure 2).

The next dialog, Create New Data Source, shows the database vendor drivers that you have installed on your PC. Click on Adaptive Server Anywhere 7.0 and then click on Finish.

We need to fill in the parameter information from Table 1. This requires the use of three tabs: ODBC, Login, and Database. The ODBC tab is where we name our DSN and can optionally give a brief description (see Figure 3). We can also test our connection once we've filled in all the required information. Click on the ODBC tab and put in the Data Source Name. Add a comment if you wish while you're here. The next step is to provide the login information.

Click on the Login tab. The User ID and Password parameters are not case sensitive in this instance. Be cautious about this when using a database server that is case sensitive, like Adaptive Server Enterprise (by default). Type the user ID (dba) and then the password (sql). You'll see asterisks instead of letters in the Password prompt. This is a security measure in case someone is peering over your shoulder as you type in the password. Fill in the Login tab by selecting "Supply User ID and Password" and filling in the blanks.

Click the Database tab. The only information you need to provide for an ASA-type DSN on the Database tab is where the physical database file (easdemodb.db) resides. PowerBuilder knows how to automatically start the ASA engine. If you're using some other DBMS, you'll need to enter the DB server name, the database name, and the DOS command line required to start that vendor's DB engine.

Enter a fully qualified pathname in the database file prompt (c:\Sybase\ Shared\PowerBuilder\easdemodb.db). You can manually type it in or use the Browse feature. Be sure to have both the start and shutdown automatic options turned on. You can now return to the ODBC tab and test the connection. Once you've made a successful connection, press the OK button to close the ODBC Configuration dialog and return to the ODBC Data Source Administrator (see Figure 1). You should now have your new DSN listed. Press OK to close the ODBC Data Source Administrator. Now we'll create our PowerBuilder DB Profile using the new DSN.

PowerBuilder Database Profile Painter
To connect to a database via the Database Painter or our application directly, we must have a PB Database Profile that points to a valid ODBC DSN. Select New to open up the Database Profile Setup dialog (see Figure 4).

The Profile Name does not have to be the same name as the DSN you created. Let's keep things simple and give it the same name as the DSN, ASA Sample DB.

The Connect Information group parameters are required. Click the dropdown arrow on the Data Source entry and select the System DSN you just created. Notice that the checkboxes by the user ID and password entries are checked. This will override the DSN user ID and password. Did we need to check them? No, but then the connection would use the system administrator login parameters from our DSN. Normally, you would enter the user's login and password here. By default, PowerBuilder leaves the entries empty, but checks the boxes. If the DSN user ID and password are what you want to connect with, take off the checks. Opening the PowerBuilder Help file for these options, we find:

The user name is required to connect to the data source. To override the user ID setting in your ODBC data source definition, select the User ID checkbox (its default setting) and specify the name you want to use to access the data source in the User ID textbox. The name you specify displays as the UID value in the ConnectString DBParm parameter...If instead you want PowerBuilder or InfoMaker to obtain the user ID from your ODBC data source definition (assuming your data source definition requires a user ID), clear the User ID checkbox. This disables the User ID textbox. If the User ID checkbox is cleared and no user ID is specified in your data source definition, PowerBuilder or InfoMaker prompts you for the user ID when you connect to the data source.

The verbiage for the password is identical.

Keep in mind that when you put a password in an ODBC DSN, it displays in your ODBC initialization. For security purposes, you may want to enter the password at execution time as the PWD value in the ConnectString DBParm parameter or in response to a prompt.

The AutoCommit feature has already been discussed and, as you can see, it's turned off. Commit on Disconnect is selected. This accounts for those times where you were in an ISQL Session in the Database Painter, executed some inserts, and did not issue a Commit. You then disconnected, thinking those inserts didn't happen, only to find upon reentry that the records are there! If you don't want this, turn it off. Keep in mind that if you do, any unfinished transactions will be lost when you disconnect. In a perfect world, this is not what we want, but it can be useful while using the PowerBuilder ISQL tool.

Those Annoying Quotes...
Do those quote marks around your table names in DataWindow Syntax preview mode annoy the daylights out of you too? On the Syntax tab of the Database Profile Setup dialog, simply uncheck the "Enclose Table and Column Names in Quotes".

A Little Cheat...
You now have a complete PowerBuilder Database Profile. How do you use it in your application? Simple. Go back to the DB Profile Painter, highlight the profile, and select Edit. Go to the Syntax tab and press Copy. Paste the code into a PowerBuilder event; the Clicked! event of your login window's OK button is a good place. Add a Connect Using SQLCA statement and then check the return code. The following code sample works for this application:

// Fill out the SQLCA properties using the ASA Sample DB
SQLCA.DBMS = "ODBC"
SQLCA.AutoCommit = False
// Take out the UID and PWD parms to use the Profile or DSN parms.
SQLCA.DBParm = "ConnectString='DSN=ASA Sample DB;UID=dba;PWD=sql'"

CONNECT USING SQLCA;

IF SQLCA.SqlCode <> 0 THEN
MessageBox ( 'Could not connect', 'RC : ' + String ( SQLCA.SqlCode ) )
END IF

PB DB Painter
Select the PB Database Painter. You can do many things with your new DB connection profile. Select "ASA Sample DB" and try some of these items:

  • Select Properties from the Object or popup menu (right-click) or use the Properties button.
  • Use the Import and Export Profiles menu selections to copy profiles.
  • Connect to a database: Highlight a profile and select Connect from the File or popup menu, or use the Connect button. With File->Recent Connections you can review and return to earlier connections. Database connections can also be made via the Database Profile button. Note: You can have multiple "connections" but only one can be used at a time.
  • Create new profiles, tables, views, columns, keys, indexes, and groups: Highlight the database object and select New from the Object or popup menu or use the Create button.
  • Modify database objects: Drag the object to the Object Details view to graphically display tables. Drag the table icon from the list in the Objects view to the Object Layout view or highlight the table and select Add to Layout from the Object or popup menu.
  • Manipulate data: Highlight the table and select Grid, Tabular, or Freeform (DataWindow presentation styles) from the Object->Data menu, the popup menu Edit Data item, or use the appropriate Data Manipulation button.
  • Build, execute, or explain SQL: Use the ISQL view to build SQL statements. Click the Paste SQL button to paste SELECT, INSERT, UPDATE, and DELETE statements or type them directly into the view's workspace. To execute or explain SQL, press CTRL-L, select Execute SQL and/or Explain SQL from the Design or popup menu.
  • Define or modify extended attributes: Select the type of extended attribute you want to define or modify from Object->Insert menu, or highlight the extended attribute from the list in the Extended Attributes view and select New or Properties from the popup menu.
  • Specify extended attributes for a column: Drag the column to the Object Details view and select the Extended Attributes tab.
  • Use the database utilities: Double-click a utility in the objects view to launch it.
  • Log your work to incorporate it in a batch file later on: Select Start Log from the design menu. To see the SQL syntax generated, display the Activity Log view.

    Like most things in this world, the bark is worse than the bite, and connecting to your DB via PowerBuilder and ODBC is no different.

    Happy holidays!

  • More Stories By David Hart

    David Hart is a former Sybase Tools and Database Instructor. He is a certified PB developer and certified ASE DBA from Rancho Cucamonga, CA. He is currently on contract assignments in Southern California.

    Comments (0)

    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.