: fyi: getting ODBC MySQL ready for WebCat

This WebDNA talk-list message is from

2001


It keeps the original formatting.
numero = 37611
interpreted = N
texte = Hi, I'm new to SQL / ODBC I could not find all of this info in one place, so I want to share it. This is another draft of a mini HowTo for getting Linux, unixODBC, MyODBC to work with MySQL on remote computers. You can get this set up and working before you enable the WebCat ODBC I have the WebCat settings at the bottom. This is a supplement to the docs on http://www.unixodbc.org/. I have used this setup successfully on four RH 6.2 and two RH 7.0 servers * * * but WebCat ODBC only works on two of them * * * * * * I'll ask that question in another post * * * * * * At least the ODBC install is working on all servers :-) * * * There is also a tiny test db to test 'data all the way though the system'. If anyone thinks this is helpful for others feel free to tweek it and share it. If you think its junk, sorry to waste your time.## ODBC was written by Microsoft so all the books talk about running it on Windows ## and using windows the configure ODBC ## On linux with no GUI, you can use a text editor to edit the odbc.ini files## on Linux, a setup may look like this 1) Application (StarOffice, WebCatalog, or ...) 2) ODBC Driver Manager (unixODBC) 3) ODBC Driver (MyODBC) --- internet --- 4) DB - MySQL server One -- MySQL server Two -- etc.############################## ## start ############################## ## check http://www.mysql.com/downloads/ to find the latest version numbers ## find your favorite mirror site and change the URL's below ## you can copy and paste these cmds into a term window (if your system supports it)## ssh into the target computer and get the needed MySQL software ncftpget ftp://mirror.sit.wisc.edu/mirrors/mysql/Downloads/MySQL-3.23/MySQL-3.23.40-1.i386.rpm ncftpget ftp://mirror.sit.wisc.edu/mirrors/mysql/Downloads/MySQL-3.23/MySQL-client-3.23.40-1.i386.rpm ncftpget ftp://mirror.sit.wisc.edu/mirrors/mysql/Downloads/MySQL-3.23/MySQL-shared-3.23.40-1.i386.rpm ncftpget ftp://mirror.sit.wisc.edu/mirrors/mysql/Downloads/MySQL-3.23/MySQL-devel-3.23.40-1.i386.rpm ncftpget ftp://mirror.sit.wisc.edu/mirrors/mysql/Downloads/MyODBC/MyODBC-2.50.37.tar.gz## check http://www.unixodbc.org/ for the latest version number for unixODBC lynx http://www.unixodbc.org/unixODBC-2.0.8.tar.gz## install the MySQL RPMs rpm -Uvh MySQL-shared-3.23.40-1.i386.rpm rpm -Uvh MySQL-devel-3.23.40-1.i386.rpm rpm -Uvh MySQL-client-3.23.40-1.i386.rpm rpm -Uvh MySQL-3.23.40-1.i386.rpm## get ready to compile - move to /usr/local/ ifs these files are not there mv MyODBC-2.50.37.tar.gz /usr/local/ mv unixODBC-2.0.8.tar.gz /usr/local/cd /usr/local/ tar xzf MyODBC-2.50.37.tar.gz tar xzf unixODBC-2.0.8.tar.gzcd /usr/local/unixODBC-2.0.8/ ./configure \ --prefix=/usr/local/unixODBC \ --x-include=/usr/X11R6/include \ --x-libraries=/usr/X11R6/lib ## this make can take an hour make make install cd /usr/local/MyODBC-2.50.37 ./configure \ --prefix=/usr/local/unixODBC \ --with-mysql-dirs=/usr/lib/mysql \ --with-mysql-includes=/usr/include/mysql \ --with-unixODBC=/usr/local/unixODBC make make install ################################ ## that's it, the ODBC Driver Manager and Driver are installed. ## If you do this on two linux servers, you can test ## connectivity between servers ## I'll assume they are behind a NAT router with the ## ip addresses 192.168.168.21 192.168.168.22 ## every thing else depends on your db etc. ## I'll show a simple db named band with a table named Beatles ## odbc.ini needs to know the db name so 1st set up a db ############################### - - - - - - My SQL setup - - - - - - - - - -# Set up the MySQL db's however you want. # I use Webmin # http://www.webmin.com/webmin/ # Using the MySQL area Webmin # On each server add a: # - db called band # - table called Beatles # With varchar 30 fields: Name and inst # user name martin with password number9# Then I pasted into the 'Execute SQL INSERT INTO Beatles VALUES('George','Lead'); INSERT INTO Beatles VALUES('John','Rhythm'); INSERT INTO Beatles VALUES('Paul','Bass'); INSERT INTO Beatles VALUES('Ringo','Drums'); # on server 1 INSERT INTO Beatles VALUES('Server','One'); # on server 2 INSERT INTO Beatles VALUES('Server','Two'); - - - - - - - - - - - - - - - - - - - - -## configure the odbcinst.ini and odbc.ini files ## using the same info from the working server cd /usr/local/unixODBC/etc/## - - - - - - odbcinst.ini - - - - - - - - - [Myodbc] Description = Myodbc driver for MySQL Driver = /usr/local/unixODBC/lib/libmyodbc.so Setup = /usr/local/unixODBC/lib/libodbcmyS.so ## - - - - - - - - - - - - - - - - - - - - -## this file should be in both servers so they and ## 'talk' to each other ## - - - - - - odbc.ini - - - - - - - - - [MySQLone] Description = test for server 1- band db Driver = /usr/local/unixODBC/lib/libmyodbc.so Trace = No TraceFile = # need IP address for the server below Server = 192.168.168.21 User = martin Password = number9 Port = 3306 Socket = Database = band # # repeat for each db and server # [MySQLtwo] Description = test for server 2 - band db Driver = /usr/local/unixODBC/lib/libmyodbc.so Trace = No TraceFile = # need IP address for the server below Server = 192.168.168.22 User = martin Password = number9 Port = 3306 Socket = Database = band ## - - - - - - - - - - - - - - - - - - - - -############################## ## ready to test ################################ on server one ## connect to yourself ## the -v flag will give you verbose errors if the connection fails/usr/local/unixODBC/bin/isql -v MySQLone martin number9+---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>select * from Beatles +-------+-------+ | Name | inst | +-------+-------+ | George| Lead | | John | Rhythm| | Paul | Bass | | Ringo | Drums | | Server| One | +-------+-------+ 5 rows affected SQL>quit## on server one ## connect to server two/usr/local/unixODBC/bin/isql -v MySQLtwo martin number9 ## SQL>select * from Beatles +-------+-------+ | Name | inst | +-------+-------+ | George| Lead | | John | Rhythm| | Paul | Bass | | Ringo | Drums | | Server| Two | +-------+-------+ 5 rows affected SQL>quit ############################## ## it worked! you are ready to install your ODBC app. ###################################################### ###################################################### ## If you want to set up WebCatalog ############################## ## http://www.smithmicro.com/isd/webcatalog/## - - - - -Edit - WebCatalog Prefs - - - - - - - - - - SQLLibraryPath /usr/local/unixODBC/lib/libodbc.so ## - - - - - - - - - - - - - - - - - - - - -# WebCat html page: this sample talks to server one # - - - - - - testone.tpl - - - - - - - - - - [thisURL]


[sql dsn=MySQLone&username=martin&password=number9&statement=SELECT * FROM Beatles;] Found [NumFound] items
[FoundItems] Name = [Name], inst = [inst]
[/FoundItems] [/sql] DONE QUERY MySQL

# - - - - - - - - - - - - - - - - - - - - -# # # # hope this helps someone - Jim------------------------------------------------------------- This message is sent to you because you are subscribed to the mailing list . To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://search.smithmicro.com/ Associated Messages, from the most recent to the oldest:

    
  1. : fyi: getting ODBC MySQL ready for WebCat (Jim Lanford 2001)
Hi, I'm new to SQL / ODBC I could not find all of this info in one place, so I want to share it. This is another draft of a mini HowTo for getting Linux, unixODBC, MyODBC to work with MySQL on remote computers. You can get this set up and working before you enable the WebCat ODBC I have the WebCat settings at the bottom. This is a supplement to the docs on http://www.unixODBC.org/. I have used this setup successfully on four RH 6.2 and two RH 7.0 servers * * * but WebCat ODBC only works on two of them * * * * * * I'll ask that question in another post * * * * * * At least the ODBC install is working on all servers :-) * * * There is also a tiny test db to test 'data all the way though the system'. If anyone thinks this is helpful for others feel free to tweek it and share it. If you think its junk, sorry to waste your time.## ODBC was written by Microsoft so all the books talk about running it on Windows ## and using windows the configure ODBC ## On linux with no GUI, you can use a text editor to edit the ODBC.ini files## on Linux, a setup may look like this 1) Application (StarOffice, WebCatalog, or ...) 2) ODBC Driver Manager (unixODBC) 3) ODBC Driver (MyODBC) --- internet --- 4) DB - MySQL server One -- MySQL server Two -- etc.############################## ## start ############################## ## check http://www.mysql.com/downloads/ to find the latest version numbers ## find your favorite mirror site and change the URL's below ## you can copy and paste these cmds into a term window (if your system supports it)## ssh into the target computer and get the needed MySQL software ncftpget ftp://mirror.sit.wisc.edu/mirrors/mysql/Downloads/MySQL-3.23/MySQL-3.23.40-1.i386.rpm ncftpget ftp://mirror.sit.wisc.edu/mirrors/mysql/Downloads/MySQL-3.23/MySQL-client-3.23.40-1.i386.rpm ncftpget ftp://mirror.sit.wisc.edu/mirrors/mysql/Downloads/MySQL-3.23/MySQL-shared-3.23.40-1.i386.rpm ncftpget ftp://mirror.sit.wisc.edu/mirrors/mysql/Downloads/MySQL-3.23/MySQL-devel-3.23.40-1.i386.rpm ncftpget ftp://mirror.sit.wisc.edu/mirrors/mysql/Downloads/MyODBC/MyODBC-2.50.37.tar.gz## check http://www.unixODBC.org/ for the latest version number for unixODBC lynx http://www.unixODBC.org/unixODBC-2.0.8.tar.gz## install the MySQL RPMs rpm -Uvh MySQL-shared-3.23.40-1.i386.rpm rpm -Uvh MySQL-devel-3.23.40-1.i386.rpm rpm -Uvh MySQL-client-3.23.40-1.i386.rpm rpm -Uvh MySQL-3.23.40-1.i386.rpm## get ready to compile - move to /usr/local/ ifs these files are not there mv MyODBC-2.50.37.tar.gz /usr/local/ mv unixODBC-2.0.8.tar.gz /usr/local/cd /usr/local/ tar xzf MyODBC-2.50.37.tar.gz tar xzf unixODBC-2.0.8.tar.gzcd /usr/local/unixODBC-2.0.8/ ./configure \ --prefix=/usr/local/unixODBC \ --x-include=/usr/X11R6/include \ --x-libraries=/usr/X11R6/lib ## this make can take an hour make make install cd /usr/local/MyODBC-2.50.37 ./configure \ --prefix=/usr/local/unixODBC \ --with-mysql-dirs=/usr/lib/mysql \ --with-mysql-includes=/usr/include/mysql \ --with-unixODBC=/usr/local/unixODBC make make install ################################ ## that's it, the ODBC Driver Manager and Driver are installed. ## If you do this on two linux servers, you can test ## connectivity between servers ## I'll assume they are behind a NAT router with the ## ip addresses 192.168.168.21 192.168.168.22 ## every thing else depends on your db etc. ## I'll show a simple db named band with a table named Beatles ## ODBC.ini needs to know the db name so 1st set up a db ############################### - - - - - - My SQL setup - - - - - - - - - -# Set up the MySQL db's however you want. # I use Webmin # http://www.webmin.com/webmin/ # Using the MySQL area Webmin # On each server add a: # - db called band # - table called Beatles # With varchar 30 fields: Name and inst # user name martin with password number9# Then I pasted into the 'Execute SQL INSERT INTO Beatles VALUES('George','Lead'); INSERT INTO Beatles VALUES('John','Rhythm'); INSERT INTO Beatles VALUES('Paul','Bass'); INSERT INTO Beatles VALUES('Ringo','Drums'); # on server 1 INSERT INTO Beatles VALUES('Server','One'); # on server 2 INSERT INTO Beatles VALUES('Server','Two'); - - - - - - - - - - - - - - - - - - - - -## configure the ODBCinst.ini and ODBC.ini files ## using the same info from the working server cd /usr/local/unixODBC/etc/## - - - - - - ODBCinst.ini - - - - - - - - - [MyODBC] Description = MyODBC driver for MySQL Driver = /usr/local/unixODBC/lib/libmyODBC.so Setup = /usr/local/unixODBC/lib/libODBCmyS.so ## - - - - - - - - - - - - - - - - - - - - -## this file should be in both servers so they and ## 'talk' to each other ## - - - - - - ODBC.ini - - - - - - - - - [MySQLone] Description = test for server 1- band db Driver = /usr/local/unixODBC/lib/libmyODBC.so Trace = No TraceFile = # need IP address for the server below Server = 192.168.168.21 User = martin Password = number9 Port = 3306 Socket = Database = band # # repeat for each db and server # [MySQLtwo] Description = test for server 2 - band db Driver = /usr/local/unixODBC/lib/libmyODBC.so Trace = No TraceFile = # need IP address for the server below Server = 192.168.168.22 User = martin Password = number9 Port = 3306 Socket = Database = band ## - - - - - - - - - - - - - - - - - - - - -############################## ## ready to test ################################ on server one ## connect to yourself ## the -v flag will give you verbose errors if the connection fails/usr/local/unixODBC/bin/isql -v MySQLone martin number9+---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>select * from Beatles +-------+-------+ | Name | inst | +-------+-------+ | George| Lead | | John | Rhythm| | Paul | Bass | | Ringo | Drums | | Server| One | +-------+-------+ 5 rows affected SQL>quit## on server one ## connect to server two/usr/local/unixODBC/bin/isql -v MySQLtwo martin number9 ## SQL>select * from Beatles +-------+-------+ | Name | inst | +-------+-------+ | George| Lead | | John | Rhythm| | Paul | Bass | | Ringo | Drums | | Server| Two | +-------+-------+ 5 rows affected SQL>quit ############################## ## it worked! you are ready to install your ODBC app. ###################################################### ###################################################### ## If you want to set up WebCatalog ############################## ## http://www.smithmicro.com/isd/webcatalog/## - - - - -Edit - WebCatalog Prefs - - - - - - - - - - SQLLibraryPath /usr/local/unixODBC/lib/libODBC.so ## - - - - - - - - - - - - - - - - - - - - -# WebCat html page: this sample talks to server one # - - - - - - testone.tpl - - - - - - - - - - [thisurl]


[sql dsn=MySQLone&username=martin&password=number9&statement=SELECT * FROM Beatles;] Found [NumFound] items
[founditems] Name = [Name], inst = [inst]
[/FoundItems] [/sql] DONE QUERY MySQL

# - - - - - - - - - - - - - - - - - - - - -# # # # hope this helps someone - Jim------------------------------------------------------------- This message is sent to you because you are subscribed to the mailing list . To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://search.smithmicro.com/ Jim Lanford

DOWNLOAD WEBDNA NOW!

Top Articles:

Talk List

The WebDNA community talk-list is the best place to get some help: several hundred extremely proficient programmers with an excellent knowledge of WebDNA and an excellent spirit will deliver all the tips and tricks you can imagine...

Related Readings:

Bug? (1997) searching more then one (1999) Shopping Cart Limits? (1998) SiteGuard Admin Feature ? (1997) Unexpected error (1997) Test (2002) WebCat2.0 acgi vs plugin (1997) MATH PROBLEM (1997) View order not right (1997) [WebDNA] TCPconnect code snippet needed (2008) Checking two required fields (1998) Sort Order on a page search (1997) Web Catalog 2 demo (1997) How To question on setting up downloads (1997) WebCatalog for guestbook ? (1997) Checkboxes (1997) Quit revisited (1997) PIXO support (1997) Database field limit? (1998) [WebDNA] Search question (2012)