Steps for setting up Control with a SQL backend under windows

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

2. Go to Start->All Programs->Administrative Tools->Data Sources (ODBC)

Go to the System DSN tab, click Add and enter the following information:

• SQL Server 
  
  <Finish>
  
  Name: COMPANY CODE
  Description: anything here
  Sever: select your SQL server from drop down list (local if on same computer)
  
  <next>
  
  <select>With Windows NT authentication using the network logon ID
  <check>Connect to SQL Server to obtain default settings for the additional configuration options
  
  <next>
  
  <check>Change the default database to:
  choose your database from drop down list
  <check> Use ANSI quoted identifiers.
  <check> Use ANSI nulls, paddings and warnings.
  
  <next>
  
  <check>Perform translation for character data
  
  <Finish>

• 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

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

• 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

• 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)

• 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

• 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=>

• 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.

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

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

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

• CategorySql