jBASE ODBC Connector (jODBC)


Developing Client Applications

 

ODBC CLI is an API written in C but other frameworks like e.g. .NET provide ODBC wrapper classes. The following example is written in Visual Basic .NET and uses the Microsoft.Data.Odbc module read data from a jBASE file:

 

Imports System

Imports Microsoft.Data.Odbc

Module Module1

    Sub Main()

        Dim connectionString As String = "DSN=T24;UID=test;PWD=test"

        Dim SQL As String = "SELECT * FROM ODBCTEST"

        Dim conn As New OdbcConnection(connectionString)

        Dim cmd As New OdbcCommand(SQL)

        cmd.Connection = conn

        conn.Open()

        Dim reader As OdbcDataReader = cmd.ExecuteReader()

        While reader.Read()

            Console.Write(("ID:" + reader.GetString(0).ToString()))

            Console.Write(" ,")

            Console.Write(("NAME:" + reader.GetString(1).ToString()))

            Console.Write(" ,")

            Console.WriteLine(("AGE:" + reader.GetInt32(2).ToString()))

        End While

        reader.Close()

        conn.Close()

    End Sub

End Module

 

SQLGetFunction() provides a list of supported SQL CLI methods.

Stored procedures are supported via the ODBC CALL statement and provide way of calling jBASE subroutines.

 

 

Handling Multi-values in SQL

 

As SQL cannot correctly return data stored in Multi-values it is necessary to create an association to ensure that data is returned in the expected format.

 

 

MV Associations

 

MV Associations are where the relationship between an attribute and any others is defined, this is so that when the SQL engine normalises the results from a query, and data does not get repeated making the results garbage…

 

A good example could be the JCUSTOMERS file, consider the following fields

 

                SYSTEMTYPE

                HARDWARE

                OS

                USERS

 

Test Data  ( [ = @VM )

 

PICK]JBASE

SUN]HP

UNIX]XP

1]2

 

Without any associations, a SELECT would return

 

SQLSELECT SYSTEMTYPE,HARDWARE,OS,NUMUSERS FROM JCUSTOMERS

 

 

SYSTEMTYPE               HARDWARE        OS              NUMUSE

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

PICK                     SUN             UNIX            1

PICK                     SUN             UNIX            2

PICK                     SUN             XP              1

PICK                     SUN             XP              2

PICK                     HP              UNIX            1

PICK                     HP              UNIX            2

PICK                     HP              XP              1

PICK                     HP              XP              2

JBASE                    SUN             UNIX            1

JBASE                    SUN             UNIX            2

JBASE                    SUN             XP              1

JBASE                    SUN             XP              2

JBASE                    HP              UNIX            1

JBASE                    HP              UNIX            2

JBASE                    HP              XP              1

JBASE                    HP              XP              2

 

Selected 16 rows.

 

This is obviously wrong as there should be a one to one relationship between each MV. To overcome this issue we use associations. For example, if we set

 

       SYSTEMTYPE    Controller

       HARDWARE      Dependant on SYSTEMTYPE

       OS            Dependant on SYSTEMTYPE

       USERS         Dependant on SYSTEMTYPE

 

The same query will result in

 

SQLSELECT SYSTEMTYPE,HARDWARE,OS,NUMUSERS FROM JCUSTOMERS

 

SYSTEMTYPE               HARDWARE        OS              NUMUSE

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

PICK                     SUN             UNIX            1

JBASE                    HP              XP              2

 

Selected 2 rows.

 

Since all four columns have been flagged as part of the same results set they don’t need expanding.

 


 

How are Associations Defined

 

Associations are defined in the dictionary of each file, there are 3 methods used to define associations.

  • Pick Associations

  • Prime Associations

  • Via the Extended dictionary

Pick Associations

Field 4 of an A type specifies any Controlling attribute / dependent attribute relationship between this attribute and any others. For example,  we could have something like

 

C;1;4;7

 

in field 4 of the A type dictionary, indicating that it controls attributes 1, 4, and 7, field 4 of the dependent dictionaries will have an entry such as

 

D;5

 

This indicates that the dictionary controlling attribute 5 should be used as the controlling MV,  if any of the dependant MV have more MV than the controller they will be ignored.

 

e.g.

 

Attrib 1 :  a]b]c]d]e]f

Attrib 4 :  z]z]z]z]z]z]z]z]z]z]z]z]z

Attrib 5    :1]2]3]4]5]6

Attrib 7 : z]x]c]v]b]n]m]k]s

 

A Query on this data would only include the first 6 values because that’s all that’s in the controller.

 

 

Prime Associations

Prime associations use field 7 of the dictionary item to hold the name of a phrase descriptor record containing details of how this and any other fields are associated together

 

e.g.

 

001 D                     

002 1                      

003                         

004 HARDWARE   

005 20L                  

006 M                     

007 EQUIP              << Association

 

The phrase descriptor, is another entry in the dictionary, can look something like

 

<<EQUIP>>

001: PH

002: HARDWARE OS NUMUSERS

 

In this example HARDWARE, OS and NUMUSERS, all should be used in any associated queries.

 

 

Extended Dictionary Associations

The definition for the extended dictionary looks something like

 

jDEF_EXTDICTSTART          30    

jDEF_DATA_TYPE             31    

jDEF_LENGTH                32    

jDEF_DESCRIPTION           33    

jDEF_COLUMN_NAME           34    

jDEF_MV_GROUP_NAME         35    

jDEF_SMV_GROUP_NAME        36    

jDEF_MAX_VALUES            37    

jDEF_MAX_SUBVALUES         38    

jDEF_FLAGS                 39    

jDEF_STORAGE_FORMAT        40    

jDEF_FOREIGN_KEY_TABLE     41    

jDEF_FOREIGN_KEY_COLUMNS   42    

jDEF_EXTDICTEND            43          

 

For testing we should be looking at

 

DICT ID , jDEF_MV_GROUP_NAME (35)

 

The rule in the SQL engine is simple,

 

A controlling field has a matching DICT ID and jDEF_MV_GROUP_NAME

 

A dependant field has the name of the controlling field in jDEF_MV_GROUP_NAME.

 

E.g.

 

LIST  F.CURRENCY]D *A0 *A34 *A35 *A36 WITH *A35 ID.SUP

 

If you look at all of the entry’s, there is one big group ‘CURRENCY_MARKET’ (*A35)

 

File F.CURRENCY]D , Record 'CURRENCY.MARKET'

Command->

0001 D

0002 12

0003

0004 CURRENCY.MARKET

0005 2R

0006 M

0030 JBASE_EDICT_START

0034 CURRENCY_MARKET

0035 CURRENCY.MARKET

0047 JBASE_EDICT_END

 

This indicates that it is a controlling attribute,  this is because the dictionary ID matches *A35

 

However looking at

 

File F.CURRENCY]D , Record 'REVAL.RATE'

Command->

0001 D

0002 24

0003

0004 REVAL.RATE

0005 16R

0006 M

0030 JBASE_EDICT_START

0034 REVAL_RATE

0035 CURRENCY.MARKET

0047 JBASE_EDICT_END

 

Although it has ‘CURRENCY.MARKET’ in *A35, this is a dependant field because *A35 dose not match the DICT ID.

 

Details of the Extended Dictionary Definitions can be found here.

 

 

 

 


jBASE ODBC Connector