UNIX: Setting up WebDNA - ODBC - Database Server

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:

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 here

Configuring the Data Sourse Name:

Use a text editor to make the following changes to the 'odbc.int' and 'odbcins.ini' files
		[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
		[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
  • 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 one line)
  • 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.