Differences between revisions 1 and 3 (spanning 2 versions)
⇤ ← Revision 1 as of 2009-03-18 03:56:32 →
Size: 5064
Editor: JonCo
Comment:
← Revision 3 as of 2009-03-18 04:26:46 → ⇥
Size: 3365
Editor: JonCo
Comment:
Deletions are marked like this. Additions are marked like this.
Line 3: Line 3:
 1. 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 =
 }}}
 1. 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''':		  1. 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 '''3'''. For '''PostgreSql''':
Line 38: Line 12:
 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 ;-)
 1. Run '''coaad''' and select the company. Goto the second page, and set the option ''Use SQL backend?'' to '''YES'''		  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 ;-)
 1. Go to Start->All Programs->Administrative Tools->Data Sources (ODBC)
Go to the System DSN tab, click Add and enter the following information:
Line 42: Line 16:
 1. 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.  . {{{
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>
 }}}
 * 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.
Line 44: Line 37:
[: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		 Z:\> createccdb
Line 51: Line 43:
 1. Set up user environment:  * 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.
Line 53: Line 45:
. /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)
 }}}
 1. 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.		 Z:\> cd \DEMOSQL1> mkdir backup
Z:\DEMOSQL1\> importccdb
Import file = All, Move processed files = No, directory: Z:\/DEMOSQL1/None
Company code: DEMOSQL1, Company name: SQL DEMO COMPANY
Line 71: Line 53:

 1. 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=>
 }}}

 1. '''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.

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

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

 1. Congratulations, your Control system is now running on an SQL backend! ;-)		  * '''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! ;-)
Line 94: Line 58:
 CategorySql  . CategorySql

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. 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 3. 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 ;-)

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

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

    Z:\> 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

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

    Z:\> cd \DEMOSQL1> mkdir backup
Z:\DEMOSQL1\> importccdb
Import file = All,  Move processed files = No,  directory: Z:\/DEMOSQL1/None
Company code: DEMOSQL1, Company name: SQL DEMO COMPANY
Do you want to proceed (yes/no)?
yes
import tables [All] successfull, see log for detail

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

SqlCompanyWindows (last edited 2013-09-18 06:09:34 by localhost)