WebDNA Software

Technical References - ODBC

UNIX: Setting up WebDNA - ODBC - Database Server

numero = 213
interpreted = N
texte = To access your SQL database using the WebDNA [SQL] context, WebDNA requires that the ODBC Driver Manager libraries be installed, and that a data source name (DSN) has been setup. For this example, we will assume the following: UnixODBC is installed at location /usr/local/unixODBC. You can download the sotware from the UNIX ODBC Website MyODBC is installed as a shared library at /usr/local/MyODBC/lib. MyODBC is the ODBC Driver for the MySQL database server. It can be download from the MySQL website MySQL database server is installed at location /usr/local/mysql. It can also be download from the MySQL website If you have installed another database server, just use its driver in place of MyODBC, and create its own data source name format. You can find out more infomation about how to create a DSN with unixODBC hereConfiguring the Data Source Name:Use a text editor to make the following changes to the 'odbc.int' and 'odbcins.ini' filesInsert these lines into the file /usr/local/unixODBC/etc/odbc.ini

[MySQL]Description = test for MySQL serverDriver      = /usr/local/MyODBC/lib/libmyodbc.soTrace       = NoTraceFile   =Server      = localhostUser        = testmysqlPassword    = anythingPort        = 3306Database    = test

Insert these lines into the file /usr/local/unixODBC/etc/odbcins.ini

[MyODBC]Description = MyODBC driver for MySQLDriver      = /usr/local/MyODBC/lib/libmyodbc.soSetup       = /usr/local/unixODBC/lib/libodbcmyS.so

Configuring WebDNA for unixODBC:Add the following line to your "WebCatalog Prefs" file.

SQLLibraryPath/usr/local/unixODBC/lib/libodbc.so

After adding this line, restart WebDNA. You are now ready to use the WebDNA [SQL] context.WebDNA Example: Assuming the MySQL database server is running, and it contains a table named "userlist" within a database named "test", you can access the userlist table using the following WebDNA code.

[SQL dsn=MySQL&username=testmysql&password=anything&statement=SELECT * FROM userlist]Found [numfound] items<br>[founditems][name]<br>[/founditems][/SQL]

Whenever WebDNA encounters the [SQL] context, it uses ODBC to make a connection to the DSN you specify. It then executes the SQL statement and retrieves the results, if any. For SQL SELECT statements, you almost always put a [founditems]...[/founditems] context inside the [SQL] context so you can display the information from the matching records.Notice for Mac OS X users:After installing unixODBC, you need to do these extra steps to change the format of libodbc shared libraryLogin as root and go to the folder /usr/local/unixODBC/lib Recompile libodbc.dylib with the command

cc -bundle -o libodbc.so libodbc.1.0.0.dylib

You now need to compile MyODBC. Assuming you are compiling MyODBC with unixODBC for MySQL Server, after you finish the "configure" & "make" steps, you need to run the following command: (all in one line)

cc -bundle -o libmyodbc.so catalog.o connect.o dll.o execute.o info.omyodbc.o options.o prefare.o results.o transact.o utility.o misc.o-L/usr/local/mysql/lib/mysql -lmysqlclient -lz-L/usr/local/unixODBC/lib -lodbc

If you want to use MyODBC with libiodbc, just use it in place of unixODBC when you compile MyODBC.Configuring WebDNA for ORACLE and Windows:You first need to install the ODBC driver for ORACLE as described here.Once the DSN is setup you can find a file called odbc.ini in C:\windowsodbc.ini has the path to the driver as follows:[ORCL]Driver32=C:\oracle\product\10.2.0\db_1\BIN\SQORA32.DLLYou now need to add SQLLibraryPath and paste the path in "WebCatalog Prefs" file:

SQLLibraryPath C:\oracle\product\10.2.0\db_1\BIN\SQORA32.DLL

To access your SQL database using the WebDNA [SQL] context, WebDNA requires that the ODBC Driver Manager libraries be installed, and that a data source name (DSN) has been setup. For this example, we will assume the following:

UnixODBC is installed at location /usr/local/unixODBC. You can download the sotware from the ODBC.org> UNIX ODBC Website
MyODBC is installed as a shared library at /usr/local/MyODBC/lib. MyODBC is the ODBC Driver for the MySQL database server. It can be download from the MySQL website
MySQL database server is installed at location /usr/local/mysql. It can also be download from the MySQL website

If you have installed another database server, just use its driver in place of MyODBC, and create its own data source name format. You can find out more infomation about how to create a DSN with unixODBC ODBC.org/ODBCinst.html>here

Configuring the Data Source Name:
Use a text editor to make the following changes to the 'ODBC.int' and 'ODBCins.ini' files

Insert these lines into the file /usr/local/unixODBC/etc/ODBC.ini

[MySQL]
Description = test for MySQL server
Driver      = /usr/local/MyODBC/lib/libmyODBC.so
Trace       = No
TraceFile   =
Server      = localhost
User        = testmysql
Password    = anything
Port        = 3306
Database    = test

Insert these lines into the file /usr/local/unixODBC/etc/ODBCins.ini

[MyODBC]
Description = MyODBC driver for MySQL
Driver      = /usr/local/MyODBC/lib/libmyODBC.so
Setup       = /usr/local/unixODBC/lib/libODBCmyS.so

Configuring WebDNA for unixODBC:
Add the following line to your "WebCatalog Prefs" file.


SQLLibraryPath/usr/local/unixODBC/lib/libODBC.so

After adding this line, restart WebDNA. You are now ready to use the WebDNA [SQL] context.

WebDNA Example:
Assuming the MySQL database server is running, and it contains a table named "userlist" within a database named "test", you can access the userlist table using the following WebDNA code.

[SQL dsn=MySQL&username=testmysql&password=anything&statement=SELECT * FROM userlist]
Found [numfound] items<br>
[founditems]
[name]<br>
[/founditems]
[/SQL]

Whenever WebDNA encounters the [SQL] context, it uses ODBC to make a connection to the DSN you specify. It then executes the SQL statement and retrieves the results, if any. For SQL SELECT statements, you almost always put a [founditems]...[/founditems] context inside the [SQL] context so you can display the information from the matching records.

Notice for Mac OS X users:
After installing unixODBC, you need to do these extra steps to change the format of libODBC shared library

Login as root and go to the folder /usr/local/unixODBC/lib
Recompile libODBC.dylib with the command

cc -bundle -o libODBC.so libODBC.1.0.0.dylib

cc -bundle -o libmyODBC.so catalog.o connect.o dll.o execute.o info.o
myODBC.o options.o prefare.o results.o transact.o utility.o misc.o
-L/usr/local/mysql/lib/mysql -lmysqlclient -lz
-L/usr/local/unixODBC/lib -lODBC

If you want to use MyODBC with libiODBC, just use it in place of unixODBC when you compile MyODBC.

Configuring WebDNA for ORACLE and Windows:
You first need to install the ODBC driver for ORACLE as described ODBC" target="_blank">here.
Once the DSN is setup you can find a file called ODBC.ini in C:\windows

ODBC.ini has the path to the driver as follows:

[ORCL]
Driver32=C:\oracle\product\10.2.0\db_1\BIN\SQORA32.DLL

You now need to add SQLLibraryPath and paste the path in "WebCatalog Prefs" file:

SQLLibraryPath C:\oracle\product\10.2.0\db_1\BIN\SQORA32.DLL

DOWNLOAD WEBDNA NOW!

Technical References - ODBC

Top Articles:

Related Readings: