Learn PowerBuilder Quickly - Part -1.4 Creating Database Profiles in PowerBuilder

Hi everybody

On the last post we have seen the major events of an application object. Now we are going to see how to connect to the database. In future more over in all projects we are going to access the databases. Today we will see how to connect the database through ODBC and OLEDB

-----------------------

Before we get into the topic there are some basic requirements need to be ensured

Requirements 

• Having Admin Rights or Equal to Admin Rights User Account (To Access ODBC)
• Any of the database servers. Ex – MS SQL Server, Sybase Central
• Get the Server Name of the Database Engine
• Get the path the Database, where it’s located.

Let’s Start our Practical’s Now

Connect the Database via ODBC

To access the ODBC Database first we want to set up the database into the ODBC drive

To do that open the ODBC drive using control panel -> Administrative tools -> Data Sources (ODBC ) or Open the Run command and type odbcad32 and press Enter.

This is the sample window of the odbc drive

 Figure 1.4.1

Here click on the add button to add new data sources.

I have MS Sql server installed on my system. This demo is for MS SQL server. (If you are using a Sybase Central choose Adaptive Anywhere).

When you click Add create new data source widow will appear like this.

  Figure 1.4.2

Scroll down and select the Sql server and then click finish

In the next window enter the Name for the data source, description (optional) and select the server. 
Like shown below

Figure 1.4.3 

Click next to select the authentication mode. If you are logging with Window NT Authentication then don’t change anything and select next , if you are using sqlserver authentication then select that and give the user name and password. Then select next. Sample is shown below

  Figure 1.4.4

Next select the database name and press next. In here my database name is pb_practicals. 
Select and click next

 Figure 1.4.5

After clicking the next the following window will appear

  Figure 1.4.6

Click finish and a pop window will appear, in that click the test connection button to check the database connection is correct or not. If not check the go back and check the server name, database name and do the same process again.
When the data source connection is successful you will get the new entry in the ODBC window like following with your data source name

 Figure 1.4.7

Now close the odbc , we have created the Datasource for our database.

The next process is create the connection to the ODBC data source from our PB. For that

Open the PB.

Open the Database Profile, here I show how to open it.

Go to Tools -> Select Database Profile

  Figure 1.4.8

When you choose the database profile the pop up window will appear like shown below

 Figure 1.4.9

Here select the ODB ODBC and Right click on that link. In that select the New Profile. 
The following window will appear

  Figure 1.4.10

In here give the profile name (any name)

And from the combo box select the data source that we have created. (In my practical I have created the data source as pb_practicals).

If your Sql server requires username and password then give those details. Else uncheck that two check boxes.

Next click on the Transactions Tab and uncheck the static bind option. Why because in future we are going to write Sql queries and store procedures in PB itself, but we want that code should be executed in back end (Sql server). If check this static bind option then Sql queries will be executed in front end. This is unnecessary to avoid this we are doing this.

After this go to preview tab in the same window, and in there click the test connection. You will get the Connection Successful Message box like below

 Figure 1.4.11

Press ok. Now we have created the connectivity to the Database. You can see the created profile will be listed under ODBC in powerbuilder database profile like shown below.

  Figure 1.4.12

Later we will see how to access the database in the project.

Connect the Database via OLE Microsoft OLE DB (ONLY FOR MS SQL SERVER DB)

            This is the direct method to connect the database from the PB. For this there is no need to create ODBC data source and all. What is needed is when the installation time of powerbuilder you must select the all components to install. If your PB not having this feature means, then you want to reinstall to get this.

Not go the database profile window. In above we have seen how to go to this view menu. Now we will how to go to this via clicking the icon.

  Figure 1.4.13

Select this icon. In the left side under the ODB ODBC that OLE Microsoft OLE DB is located. Right Click on that link and select New Profile Option. You will get the following window

 Figure 1.4.14

Give the profile name, and select the SQLOLEDB as the provider. If your Sql server has separate user name and password other than Windows NT authentication then provide those details in the User id and password field. In the extended field give the below code

            Database=”Your database name without quotes”;Trusted_connection=Yes;

Next select the system tab in this window, and uncheck the static bind option. As shown below

  Figure 1.4.15

Then select the syntax tab as like below

 

 Figure 1.4.16

Make sure the Enclose tables and column names in quotes having Tick symbol, if not Tick that

Now select the preview tab

  Figure 1.4.17

Now click the test connection button, if you get the connection successful message then connection is successfully created

As for as now we have successfully created the database profile. On the next post we will see how to do code to access the data in PB.

----------------

Regards
Pushparaj