WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

ODBC
plus

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

Explanation of ODBC Extender Usage

Keywords:    Multiple qconnect qAllocConnect datasource data source DSN

Question:

I'm just wondering if anyone has any simple examples of using WinBatch with ODBC. I'm not overly familiar with ODBC or SQL, but I have created a couple of programs, but only simply deleting from tables and inserting fresh data.

What's confusing me now is when I have TWO datasources, and I'm wanting to write from one to the other. If anyone has any simple examples of such an application, I would truly appreciate it.

Answer:

Open Database Connectivity (ODBC) is a method of communication to client/server databases. ODBC is part of Microsoft’s Windows Open Systems Architecture (WOSA), which provides a series of application program interfaces (APIs) to simplify and provide standards for various programming activities. The goal is to have all applications communicating through the same set of APIs. ODBC is just one piece of the WOSA picture. Other components include telephone services (TAPI), messaging services (MAPI), and open data services (ODS).

Many users can share a database, but the methodology is actually that of sharing files. To enable users to share a database, all the applications that use the database must have access to the database files. Also, none of the applications can open the database in "exclusive" mode. When you open a database in exclusive mode, you prevent other applications from using the database.

Before accessing an ODBC database, you must install on your system the appropriate ODBC driver for that database. You install this driver by using the ODBC Manager applet in the Windows Control Panel.

A Data Source Name (DSN) is what ODBC uses to allow you to associate a database with a driver. You use the ODBC Administrator (in Control Panel) to configure give the DSN a unique name and then associate it with both a database and a driver.

When you open a connection to ODBC, you specify what DSN you want to work with. ODBC takes care of all the little details involved in opening that database.

Before you can access ODBC databases, you must configure the ODBC data source names, the ODBC drivers, and the configuration values used in ODBC.INI. You also should understand the structure of an ODBC driver and the ODBC API as well as some ODBC-related terminology.

In order for any application to access data in a database, it must establish a connection to that database through its corresponding ODBC driver. To do this, the application must request a connection from the ODBC Driver Administrator, specifying the datasource desired.

The Driver Manager must be initialized to be able to accept such a connection request. This initialization is performed when an qAllocEnv call is made, which both allocates memory for an environment handle and initializes ODBC. You must call this function first, before any other ODBC functions are called. For example:

AddExtender("wwodb34I.dll")
;THIS ALLOCATES A SQL ENVIRONMENT HANDLE
henv = qAllocEnv()
Once the file handle (henv) has been obtained, and before the driver connection can be requested, you must allocate the connection handle. Do this with the qAllocConnect function, which will allocate memory for a connection handle and return the new connection handle. For example:
;ALLOCATES A SQL CONNECTION HANDLE - MAXIMUM ;OF 10 OPEN CONNECTION HANDLES
hdbc = qAllocConnect(henv) 
If hdbc == -1  
  retcode = qLastCode()	 
  Message("qAllocConnect failed", retcode)  
  Exit
Endif
It is important to note the relationship between the ODBC environment allocated with qAllocEnv() (of which there only can be one), the ODBC connection (of which there can be up to 10 handles) allocated with qAllocConnect(henv), and an active statement handle for each connection, allocated with qAllocStmt(hdbc).

You can have multiple qAllocConnect handles (up to 10 connection handles):

;hdbc1 is the handle to the first datasource
hdbc1 = qAllocConnect(henv) 
If hdbc1 == -1 
  retcode = qLastCode() 
  Message("qAllocConnect failed", retcode) 
  Exit
Endif


;CONNECTS TO THE "FIRST DSN" DATA SOURCE
retcode = qConnect(hdbc1, "FIRST DSN", "", "") ;Notice variable hdbc1

If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
  Message("qConnect failed", retcode)
  Exit
Endif


;hdbc2 is the handle to the SECOND datasource
hdbc2 = qAllocConnect(henv) 
If hdbc2 == -1 
  retcode = qLastCode() 
  Message("qAllocConnect failed", retcode) 
  Exit
Endif

;CONNECTS TO THE "SECOND DSN" DATA SOURCE
retcode = qConnect(hdbc2, "SECOND DSN", "", "") 

If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
  Message("qConnect failed", retcode)
  Exit
Endif

Now you just use the appropriate SQL connection handle (in this case, hdbc1 or hdbc2) in any of the functions.
Article ID:   W12533
Filename:   Explanation of ODBC Usage.txt
File Created: 2017:07:28:12:58:32
Last Updated: 2001:03:01:14:41:46