ODBC Functions

This chapter contains the ODBC functions provided by the Ring programming language.

  • odbc_init()
  • odbc_drivers()
  • odbc_datasources()
  • odbc_close()
  • odbc_connect()
  • odbc_disconnect()
  • odbc_execute()
  • odbc_colcount()
  • odbc_fetch()
  • odbc_getdata()
  • odbc_tables()
  • odbc_columns()
  • odbc_autocommit()
  • odbc_commit()
  • odbc_rollback()

Before using the next function load the odbclib.ring library

load "odbclib.ring"
# Use ODBC functions

odbc_init() Function

We can create ODBC Handle using the odbc_init() function

Syntax:

odbc_init() ---> ODBC Handle

odbc_drivers() Function

We can get a list of ODBC drivers using the odbc_drivers() function

Syntax:

odbc_drivers(ODBC Handle) ---> List of Drivers

odbc_datasources() Function

We can get a list of ODBC data sources using the odbc_datasources() function

Syntax:

odbc_datasources(ODBC Handle) ---> List of Data sources

odbc_close() Function

After the end of using ODBC functions we can free resources using ODBC_Close() function

Syntax:

odbc_close(ODBC Handle)

odbc_connect() Function

We can connect to the database using the odbc_connect() function.

Syntax:

odbc_connect(ODBC Handle, cConnectionString)

odbc_disconnect() Function

We can close the connection to the database using the odbc_disconnect() function.

Syntax:

odbc_disconnect(ODBC Handle)

Open and Close Connection

The next example connect to the database then close the connection

See "ODBC test 3" + nl
pODBC = odbc_init()
See "Connect to database" + nl
see odbc_connect(pODBC,"DBQ=test.mdb;Driver={Microsoft Access Driver (*.mdb)}") + nl
See "disconnect" + nl
odbc_disconnect(pODBC)
See "Close database..." + nl
odbc_close(pODBC)

Output:

ODBC test 3
Connect to database
1
disconnect
Close database...

odbc_execute() Function

We can execute SQL Statements on the database using the odbc_execute() function.

Syntax:

odbc_execute(ODBC Handle, cSQLStatement)

odbc_colcount() Function

We can get columns count in the query result using the odbc_colcount() function.

Syntax:

odbc_colcount(ODBC Handle) ---> Columns Count as Number

odbc_fetch() Function

We can fetch a row from the query result using the odbc_fetch() function.

Syntax:

odbc_fetch(ODBC Handle)

odbc_getdata() Function

We can get column value from the fetched row using the odbc_getdata() function.

Syntax:

odbc_getdata(ODBC Handle, nColumnNumber) ---> Column Value

Execute Query and Print Result

The next example execute query then print the query result.

See "ODBC test 4" + nl
pODBC = odbc_init()
See "Connect to database" + nl
see odbc_connect(pODBC,"DBQ=test.mdb;Driver={Microsoft Access Driver (*.mdb)}") + nl
See "Select data" + nl
see odbc_execute(pODBC,"select * from person") + nl
nMax = odbc_colcount(pODBC)
See "Columns Count : " + nMax + nl
while odbc_fetch(pODBC)
        See "Row data:" + nl
        for x = 1 to nMax
                see odbc_getdata(pODBC,x) + " - "
        next
end
See "Close database..." + nl
odbc_disconnect(pODBC)
odbc_close(pODBC)

odbc_tables() Function

We can get a list of tables inside the database using the odbc_tables() function.

We can access the result of this function as we get any query result.

Syntax:

odbc_tables(ODBC Handle)

Example:

See "ODBC test - Get Database Tables" + nl
pODBC = odbc_init()
See "Connect to database" + nl
odbc_connect(pODBC,"DBQ=test.mdb;Driver={Microsoft Access Driver (*.mdb)}") + nl
See "Select data" + nl
odbc_tables(pODBC) + nl
nMax = odbc_colcount(pODBC)
See "Columns Count : " + nMax + nl
while odbc_fetch(pODBC)
        for x = 1 to nMax
                see odbc_getdata(pODBC,x)
                if x != nMax see " - " ok
        next
        See nl
end
See "Close database..."
odbc_disconnect(pODBC)
odbc_close(pODBC)

Output:

ODBC test - Get Database Tables
Connect to database
Select data
Columns Count : 5
.\test - NULL - Customer - TABLE - NULL
.\test - NULL - employee - TABLE - NULL
.\test - NULL - person - TABLE - NULL
.\test - NULL - tel - TABLE - NULL
Close database...

odbc_columns() Function

We can get a list of columns inside the table using the odbc_columns() function.

Syntax:

odbc_columns(ODBC Handle, cTableName)

Example:

See "ODBC test - Get Table Columns" + nl
pODBC = odbc_init()
See "Connect to database" + nl
odbc_connect(pODBC,"DBQ=test.mdb;Driver={Microsoft Access Driver (*.mdb)}") + nl
See "Get Columns inside the Person Table" + nl
odbc_columns(pODBC,"person") + nl
while odbc_fetch(pODBC)
        see odbc_getdata(pODBC,4) + nl
end
See "Close database..." + nl
odbc_disconnect(pODBC)
odbc_close(pODBC)

Output:

ODBC test - Get Table Columns
Connect to database
Get Columns inside the Person Table
FIRST
LAST
STREET
CITY
STATE
ZIP
HIREDATE
MARRIED
AGE
SALARY
NOTES
Close database...

odbc_autocommit() Function

We can enable or disable the auto commit feature using the odbc_autocommit() function.

Syntax:

odbc_autocommit(ODBC Handle, lStatus)   # lStatus can be True or False

odbc_commit() Function

We can commit updates to the database using the odbc_commit() function.

Syntax:

odbc_commit(ODBC Handle)

odbc_rollback() Function

We can rollback updates to the database using the odbc_rollback() function.

Syntax:

odbc_rollback(ODBC Handle)

Transactions and Using Commit and Rollback

Example:

See "ODBC Test - Transactions and using Commit and Rollback" + nl
pODBC = odbc_init()
See "Connect to database" + nl
see odbc_connect(pODBC,"DBQ=test.mdb;Driver={Microsoft Access Driver (*.mdb)}") + nl
see "insert data..." + nl
odbc_autocommit(pODBC,0)
for x = 1 to 10000
        odbc_execute(pODBC,"insert into tel values (" + x + ",'mahmoud')")
next
for x = 10001 to 15000
        odbc_execute(pODBC,"insert into tel values (" + x + ",'samir')")
next
odbc_commit(pODBC)

for x = 15001 to 20000
        odbc_execute(pODBC,"insert into tel values (" + x + ",'fayed')")
next

ODBC_ROLLBACK(pODBC)
odbc_execute(pODBC,"insert into tel values (" + x + ",'fayed')")
odbc_commit(pODBC)

See "Close database..." + nl
odbc_disconnect(pODBC)
odbc_close(pODBC)

Output:

ODBC Test - Transactions and using Commit and Rollback
Connect to database
1
insert data...
Close database...

Save and Restore images

The next example save an image inside the database

See "ODBC test - Save image in the database" + nl
pODBC = odbc_init()
See "Connect to database" + nl
see odbc_connect(pODBC,"DBQ=test.mdb;Driver={Microsoft Access Driver (*.mdb)}") + nl
see "Read Image File..." + nl
cFile = str2hex(read("tests\mahmoud.jpg"))
see "size " + len(CFile)+nl
see "Save image in the database..." + nl
stmt = "insert into tel values (20000,'mahmoud','" + cFile + "');"
odbc_execute(pODBC,stmt)
See "Close database..." + nl
odbc_disconnect(pODBC)
odbc_close(pODBC)

The next example restore the image from the database

See "ODBC Test - Restore image from the database" + nl
pODBC = odbc_init()
See "Connect to database" + nl
see odbc_connect(pODBC,"DBQ=test.mdb;Driver={Microsoft Access Driver (*.mdb)}") + nl
See "Select data" + nl
see odbc_execute(pODBC,"select * from tel where id = 20000") + nl
nMax = odbc_colcount(pODBC)
See "Columns Count : " + nMax + nl
if odbc_fetch(pODBC)
        See "Write image file" + nl
        write("tests\great.jpg",hex2str( odbc_getdata(pODBC,3) ) )
ok
See "Close database..." + nl
odbc_disconnect(pODBC)
odbc_close(pODBC)