Steps for setting up Control with a SQL backend

1. Create a regular company, or use an existing one. Once you complete the SQL conversion process you cannot go back

2. Edit the file /etc/odbc.ini with the following information:

   #Company name:
   [FAYEZ]
   # This is the implementation specific driver for the database. 
   # Different databases (mysql, oracle) would have different drivers
   #for PostgreSql:
   Driver     = /usr/local/lib/psqlodbcw.so 
   #for Oracle (on server)
   Driver     = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/libsqora.so.10.1
   #for Oracle (on client, if we do have one)
   Driver     = /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/libsqora.so.10.1
   Port       = 5432
   #if database and Control run on the same machine or on the server:
   ServerName = localhost
   #on client (if we do have one):
   ServerName = server_name
   # Ensure that the database name is lowercase (for PostgreSql). 
   # The name does not have to correspond to the company name
   Database   = fayez 
   # This username should have already been created.
   UserName   = ccc
   # Leave this blank if you want to be prompted for a password ???
   Password   =

3. Log into the database with a user who can create new databases and specify owners (an admin user, such as postgres), and create the database specified in step 2.

   For PostgreSql:

   -bash-3.00$ su -postgres
   -bash-3.00$ psql 
   Password: 
   Welcome to psql 8.1.3, the PostgreSQL interactive terminal.
   postgres=# create database fayez with owner = ccc;
   CREATE DATABASE

   For MS SQL: use MS SQL Enterprise manager

   For Oracle: free version comes with pre installed database XE and it is not possible to create extra or rename it. For commercial installation: follow instructions

4. Run coaad and select the company. Goto the second page, and set the option Use SQL backend? to YES

5. Change into the SQL environment and then run createccdb to create the required tables and sequences. Other options are available to createdb, you can view these by using the --help option.

   [:X11:DEV]/home/usr/fayez/mod/work.!> gosql  #temporary, untill we will update all our rel9 customers and make sql mode standart one (it can handle both, file and sql backend)
   [:X11:DEV:sql]/home/usr/fayez/mod/work.!> createccdb 
   
   Create tables: All, drop existant tables: No, Company code: FAYEZ, Company name: DEMONSTRATION CO.
   
   Do you want to proceed (yes/no)?
   yes
   
   create tables [All] successfull, see log for detail

6. Set up user environment:

   . /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh   #for Oracle
   add /usr/local/bin to PATH and /usr/local/lib to LD_LIBRARY_PATH #for PostgreSQL if it is installed in /usr/local/lib
   
   CCLOGFILE=logfile_name             #if we want to use different log file name (default: odbc_log.log)
   CCUSER=username                    #if we want to use different DATABASE user (default is ccc)
   CCPASSWD=password                  #if we want to use different password (default is support)

7. Change to the company directory and create a new directory for the datafiles. All successfully converted files will be moved to this directory. You should also move the index files there. This is not nesscarry but is useful if you need to rollback, or if the import fails. Run importccdb to import all the data into the database. This will take a while if you have a large amount of data.

   [:X11:DEV:sql]/home/usr/fayez/mod/work.!> cd /ccdev/FAYEZ
   [:X11:DEV:sql]/ccdev/FAYEZ.!> mkdir backup
   [:X11:DEV:sql]/ccdev/FAYEZ.!> cp *.idx backup/
   [:X11:DEV:sql]/ccdev/FAYEZ.!> importccdb -m backup 
   Import file = All,  Move processed files = Yes,  directory: /u/ccdev//FAYEZ/backup
   Company code: FAYEZ, Company name: DEMONSTRATION CO.
   
   Do you want to proceed (yes/no)?
   yes
   
   import tables [All] successfull, see log for detail

8. To manually log into to the databases, you can do the following. The password is usually support.

   -bash-3.00$ psql -U ccc fayez          #postgres
   -bash-3.00$ sqlplus ccc@server_name    #oracle
   
   Password for user ccc: 
   Welcome to psql 8.1.3, the PostgreSQL interactive terminal.
   
   Type:  \copyright for distribution terms
          \h for help with SQL commands
          \? for help with psql commands
          \g or terminate with semicolon to execute query
          \q to quit
   
   fayez=> 

9. updateccdd should be run everytime a new field is introduced to a structure or one is moved or removed. It is probably safer to run this everytime new programs are compiled or copied across to customer sites. It is very quick and requires no user interaction.

10. dropccdd -f filename is a utility to be used to drop tables as updateccdd only removes columns.

11. createccdb -f filename is a utility to be used to add tables as updateccdd only add columns.

12. Congratulations, your Control system is now running on an SQL backend!

CategorySql