Printed Icetips Article

When you use the Clarion ODBC driver in your program, you get one connection (to a server) at a time. With the MSSQL driver, though, you can connect to different servers at the same time. It might be possible with other Clarion native SQL drivers, but I don't know about that.

Let's say your program needs to establish and maintain two connections at the same time, and you are going to use the CWODBC object that is at http://www.icetips.com/downloadfile.php?FileID=59. The trick to doing this is to write a little code to use the flexibility of an INTERFACE (remember that the cwodbc object is written as an interface).

I'll go into interfaces elsewhere at another time. But I'll illustrate here how to make two simultaneous connections to MSSQL servers.

You need two procedures that you write yourself. Don't worry, the code is short. Here are the two procedures. First, the prototypes:

    map
        NewServerAConnection(), *cwodbc
        NewServerBConnection(), *cwodbc
    end

Now for the code for each procedure:

NewServerAConnection    procedure
MyFile  file, driver('mssql')
          record  ! use fields if you want; 
                  ! this is for illustration
          end
        end
conn    &cwodbc
    code
    myfile{prop:owner} = 'ServerA,mydatase'
    conn &= newcwodbc()
    conn.setfile(myfile)
    conn.settableinfo('sometablename', 'somefieldname')
    return conn

NewServerBConnection    procedure
MyFile  file, driver('mssql')
          record  ! use fields if you want; 
                  ! this is for illustration
          end
        end
conn    &cwodbc
    code
    myfile{prop:owner} = 'ServerB,mydatase'
    conn &= newcwodbc()
    conn.setfile(myfile)
    conn.settableinfo('sometablename', 'somefieldname')
    return conn

Now when you want to query something on ServerA, you create an instance of the cwodbc object like this:

    sql &= newServerAConnection()
    ! your code here
    sql.Release()

When you want to query something on ServerB, the code is very similar:

    sql &= newServerBConnection()
    ! your code here
    sql.Release()

Although you can't maintain two simultaneous connections with the ODBC driver, this technique can come in handy with ODBC anyway. Say you want one object that will handle queries that don't return TEXT, IMAGE, and other large type fields, and you want one that is optimized for speed, but won't handle such fields. They you write two procedures like this:


NewSpeedySQL        procedure
MyFile    file, driver('odbc')
                record
                cstring(256)
                cstring(256)
                cstring(256)
                cstring(256)
                cstring(256)  ! add as many of these as 
                              ! you will need to  handle 
                              ! your largest column count 
                              ! (Press Ctrl-2)
                end
                end
conn    &cwodbc
    code
    myfile{prop:owner} = 'myconnectstring'
    conn &= newcwodbc()
    conn.setfile(myfile)
    conn.settableinfo('sometablename', 'somefieldname')
    return conn

NewBlobSQL    procedure
MyFile    file, driver('odbc')
                record
                end           ! use NO fields in this 
                              ! one, so it can handle 
                              ! large columns, and any 
                              ! number of columns
                end
conn    &cwodbc
    code
    myfile{prop:owner} = 'myconnectstring'
    conn &= newcwodbc()
    conn.setfile(myfile)
    conn.settableinfo('sometablename', 'somefieldname')
    return conn

Now when you want a speedy cwodbc object, you create it like this:

    sql &= newSpeedySQL()

And when you want one that handles large type columns, like this:

    sql &= newBlobSQL()