[ic] MySQL Problems - One answer is to...

[Kyle Cook]

At 01:46 PM 6/20/01, you wrote:
>Hi,
>
>I've been banging my head against the wall trying to integrate a MySQL 
>database with Interchange.
>
>I've run the gauntlet of errors ( I don't even want to talk about PGP) and 
>I was wondering if someone could post, or email me, a sample 'catalog.cfg' 
>and any other config files that enable a connection to a MySQL database.
>
>Some sample errors that I've gotten lately are:
>
>rob4 config error: DBI: no column names returned for affiliate
>
>Error: Table 'ic_test_create' already exists
>
>robert19 config error: No database object for table: gift_certs
>Probable mismatch of Database directive to database type,for example 
>calling DBI without proper modules or database access.
>
>
>I'm on a SunOS 5.7 and I have gone through the documentation but would 
>really like to see an example.
>
>Thank's
>Rob


Rob,

Maybe part of the below will help.

Its some notes I kept on getting MySQL set up in a custom site.
I cleaned them up a little and maybe it will help you or others.

(I try to help anyway :-)

Here is one way to set up MySQL, but you must set
up the database and tables manually via telnet,
PhpMyAdmin, etc.

##############################################
General steps (refer to documentation about
specific Variables, Permissions, fields for
products tables etc )

##############################################
#1. Set up Mysql db & tables

Administer, set up tables, import data manually
from outside IC. Set up the interch (or XXXXX) user
for appropriate permissions with a password and
use this info in catalog.cfg (below)

##############################################
#2. relevant cat.cfg lines

# --- excerpt catalog.cfg (FILL IN THE XXXXXX's) ---------
Variable SQLDSN    dbi:mysql:XXXXXXX:localhost:3333
Variable SQLDB     XXXXXXX
Variable SQLUSER   XXXXXXX
Variable SQLPASS   XXXXXXX

Message Using MySQL, DSN=__SQLDSN__.

# Tell the default DBM we are using something else...
Variable  SOME_DATABASE  1

# The table defs are in separate files in the dbconf
# directory, the ones kept in DBM are in TABLENAME.dbm files.
#include dbconf/*

# set any NON MySQL tables here:
Database  country     country.txt     TAB
Database  salestax    salestax.asc    TAB
Database  state       state.asc       TAB
Database  Ground      gndres.csv      CSV
Database  2ndDayAir   2da.csv         CSV
Database  NextDayAir  1da.csv         CSV
# ----------------------------------------------------------

##############################################
#3. Save some files in catalog directories for ea table

Note XXX below indicates each table that you need
in IC like products, inventory, etc.

Leave some empty XXX.sql files in your products
directory for each table (to prevent IC from
attempting to import data upon restart)

Also add some XXX.mysql files in dbconf directory.
At a minimum, these should look like:

# -------------------------------------------------
# MiniVend database definition
Database  products  products.txt __SQLDSN__
#ifdef SQLUSER
Database  products  USER         __SQLUSER__
#endif
#ifdef SQLPASS
Database  products  PASS         __SQLPASS__
#endif
# -------------------------------------------------

Note don't really need all the #ifdef conditions
if you know you will always use a username and pass
to connect to MySQL so could shorten to:

# -------------------------------------------------
# MiniVend database definition
Database  products  products.txt __SQLDSN__
Database  products  USER         __SQLUSER__
Database  products  PASS         __SQLPASS__
# -------------------------------------------------

##############################################
#4. Try a restart/reconfig and see what happens.

If problems, look at/post your error logs.

##############################################

Good Luck!
Kyle (KC)