Peculiarities of using particular RDBMS with CLIP are described below.
To be able to build an application using MySQL data base, appropriate packages must be installed. It includes MySQL development files and clip-mysql package. See Installation chapter for details.
Link your application with MySQL driver by adding -lclip-mysql to the end of clip command:
$clip -e test.prg -lclip-mysql
To obtain a connect to the MySQL server use ConnectNew() with the following syntax:
ConnectNew("MS",[<cHost>],[<cPort>],[<cLogin>],[<cPassword>],[<cDB>],;
[<cSocket>],[<cFlags>],[<cCharset>],[<cIsolation>])where
"MS" - identifier of MySQL driver; see also SQLList()
cHost - a string containing the name of the host MySQL server is running on
cPort - server port number as string
cLogin - a string containing user login
cPassword - a string containing user's password
cDB - a string containing the name of the database to be used
cSocket - a string containing UNIX socket or pipe name to be used as transport
cFlags - flags to be used with this connection as string; see MySQL manual for details
cCharset - charset used by server; if not passed, SET("SQL_CHARSET") is used
cIsolation - a string containing the name of isolation level to be used by default (see MySQL manual for details); if not passed, SET("MS_ISOLATION_LEVEL") value is used; possible values:
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
the same values are possible for <cIsolation> parameter of TRowset:Start().
To bind rows in the data base and rows in the local set a field with AUTO_INCREMENT clause is used (see description of CREATE TABLE statement in the MySQL manual). See also TConnect:CreateRowset() description for details.
The following table shows implemented MySQL data types and their XBase equivalents.
Table 13-1. Implemented MySQL data types
| MySQL data type | CLIP data type |
|---|---|
| DECIMAL | NUMERIC |
| TINY | NUMERIC |
| SHORT | NUMERIC |
| LONG | NUMERIC |
| FLOAT | NUMERIC |
| DOUBLE | NUMERIC |
| LONGLONG | NUMERIC |
| INT24 | NUMERIC |
| TIMESTAMP | DATE |
| DATE | DATE |
| TIME | ARRAY {h,m,s}; h,m,s - NUMERIC |
| DATETIME | DATE |
| YEAR | NUMERIC |
| ENUM | CHARACTER |
| SET | CHARACTER |
| TINY_BLOB | CHARACTER |
| MEDIUM_BLOB | CHARACTER |
| LONG_BLOB | CHARACTER |
| BLOB | CHARACTER |
| VAR_STRING | CHARACTER |
| STRING | CHARACTER |
To be able to build an application using PostgreSQL data base, appropriate packages must be installed. It includes PostgreSQL development files and clip-postgres package. See Installation chapter for details.
Link your application with PostgreSQL driver by adding -lclip-postgres to the end of clip command:
$clip -e test.prg -lclip-postgres
To obtain a connect to the PostgreSQL server use ConnectNew() with the following syntax:
ConnectNew("PG",[<cHost>],[<cPort>],[<cLogin>],[<cPassword>],[<cDB>],;
[<cTTY>],[<cOptions>],[<cCharset>],[<cIsolation>])where
"PG" - identifier of PostgreSQL driver; see also SQLList()
cHost - a string containing the name of the host PostgreSQL server is running on
cPort - server port number as string
cLogin - a string containing user login
cPassword - a string containing user's password
cDB - a string containing the name of the database to be used
cTTY - the name of debug tty
cOptions - optional parameters of the connection; see PostgreSQL manual for details
cCharset - charset used by server; if not passed, SET("SQL_CHARSET") is used
cIsolation - a string containing the name of isolation level to be used by default (see PostgreSQL manual for details); if not passed, SET("PG_ISOLATION_LEVEL") value is used; possible values:
READ COMMITTED
SERIALIZABLE
the same values are possible for <cIsolation> parameter of TRowset:Start().
To bind rows in the data base and rows in the local set a oid field is used. See TConnect:CreateRowset() description and PostgreSQL manual for details.
The following table shows implemented PostgreSQL data types and their XBase equivalents.
Table 13-2. Implemented PostgreSQL data types
| PostgreSQL data type | CLIP data type |
|---|---|
| DECIMAL | NUMERIC |
| ABSTIME | DATE |
| BOOL | LOGICAL |
| BOX | ARRAY {{x1,y1},{x2,y2}}; x1,y1,x2,y2 - NUMERIC |
| BPCHAR | CHARACTER |
| BYTEA | CHARACTER |
| CHAR | CHARACTER |
| CID | NUMERIC |
| CIDR | ARRAY {x1,...}; x1,... - NUMERIC |
| CIRCLE | ARRAY {x1,x2,x3}; x1,x2,x3 - NUMERIC |
| DATE | DATE |
| FILENAME | CHARACTER |
| FLOAT4 | NUMERIC |
| FLOAT8 | NUMERIC |
| INET | ARRAY {x1,x2,x3,x4,x5}; x1,x2,x3,x4,x5 - NUMERIC |
| INT2 | NUMERIC |
| INT28 | ARRAY {x1,x2,...,x8}; x1,x2,...,x8 - NUMERIC |
| INT4 | NUMERIC |
| LSEG | ARRAY {{x1,y1},{x2,y2}}; x1,y1,x2,y2 - NUMERIC |
| MONEY | NUMERIC |
| NAME | CHARACTER |
| NUMERIC | NUMERIC |
| OID | NUMERIC |
| OID8 | ARRAY {x1,x2,...,x8}; x1,x2,...,x8 - NUMERIC |
| PATH | CHARACTER |
| POINT | ARRAY {x,y}; x,y - NUMERIC |
| POLYGON | ARRAY {{x1,y1},...{xn,yn}}; x1..xn, y1...yn - NUMERIC |
| RELTIME | NUMERIC |
| TEXT | CHARACTER |
| TIMESTAMP | DATE |
| VARCHAR | CHARACTER |
| XID | NUMERIC |
To be able to build an application using Oracle data base, appropriate packages must be installed. It includes Oracle development files and clip-oracle package. See Installation chapter for details.
Link your application with Oracle driver by adding -lclip-oracle to the end of clip command:
$clip -e test.prg -lclip-oracle
To obtain a connect to the Oracle server use ConnectNew() with the following syntax:
ConnectNew("OR",NIL,NIL,<cLogin>,<cPassword>,[<cDB>],NIL,NIL,[<cCharset>],[<cIsolation>])where
"OR" - identifier of Oracle driver; see also SQLList()
cLogin - a string containing user login
cPassword - a string containing user's password
cDB - a string containing the name of the database to be used (connect string)
cCharset - charset used by server; if not passed, SET("SQL_CHARSET") is used
cIsolation - a string containing the name of isolation level to be used by default (see Oracle manual for details); if not passed, SET("OR_ISOLATION_LEVEL") value is used; values containing one or more of the following strings divided by ';' are possible:
READ COMMITTED
SERIALIZABLE
READ ONLY
READ WRITE
the same values are possible for <cIsolation> parameter of TRowset:Start().
To bind rows in the data base and rows in the local set a rowid field is used (see Oracle manual). See also TConnect:CreateRowset() description for details.
An array with SQL parameters (used in <aParameters> parameter of TConnect:Command() and TConnect:CreateRowset() functions) must have third element (SQL data type) in each row with Oracle. Data types are defined in oracle.ch include file. For example:
#include "oracle.ch"
// ...
conn:Command("INSERT INTO mytable (fname,lname) VALUES (:fname,:lname)",;
{{"fname","John",ORT_VARCHAR2},{"lname","Smith",ORT_VARCHAR2}})INSERT statement passed in <cInsertSQL> parameter to TRowset constructor TConnect:CreateRowset() must have RETURNING rowid clause. For example:
INSERT INTO mytable (fname,lname) VALUES (:fname,:lname) RETURNING rowid INTO :rowid
The following table shows implemented Oracle data types and their XBase equivalents.
Table 13-3. Implemented Oracle data types
| Oracle data type | CLIP data type |
|---|---|
| VARCHAR2 | CHARACTER |
| CHAR | CHARACTER |
| RAW | CHARACTER |
| NUMBER | NUMERIC |
| INTEGER | NUMERIC |
| FLOAT | NUMERIC |
| LONG | CHARACTER |
| LONG VARCHAR | CHARACTER |
| LONG RAW | CHARACTER |
| DATE | DATE |
| CLOB | CHARACTER |
| BLOB | CHARACTER |
| FILE | CHARACTER |
To be able to build an application using Interbase data base, appropriate packages must be installed. It includes Interbase development files and clip-interbase package. See Installation chapter for details.
Link your application with Interbase driver by adding -lclip-interbase to the end of clip command:
$clip -e test.prg -lclip-interbase
To obtain a connect to the Interbase server use ConnectNew() with the following syntax:
ConnectNew("IB",NIL,NIL,<cLogin>,<cPassword>,<cDB>,NIL,NIL,[<cCharset>],[<cIsolation>])where
"IB" - identifier of Interbase driver; see also SQLList()
cLogin - a string containing user login
cPassword - a string containing user's password
cDB - a string containing the name of the database to be used (the file name)
cCharset - charset used by server; if not passed, SET("SQL_CHARSET") is used
cIsolation - a string containing the name of isolation level to be used by default (see Interbase manual for details); if not passed, SET("IB_ISOLATION_LEVEL") value is used; values containing one or more of the following strings divided by ';' are possible:
CONCURRENCY
READ COMMITTED or READ_COMMITTED
REC VERSION or REC_VERSION
NO REC VERSION or NO_REC_VERSION
CONSISTENCY
WAIT
NOWAIT
READ
WRITE
the same values are possible for <cIsolation> parameter of TRowset:Start().
To bind rows in the data base and rows in the local set a field with the name passed in <cIdName> parameter to TRowset constructor is used. See TConnect:CreateRowset() description for details.
With Interbase, TConnect:Start() may receive the second parameter <cLockTables> describing what tables and how they should be locked during transaction. The string being contained in <cLockTables> should have the following form:
<table_name>=<lock_type>[;<table_name>=<lock_type>,...]
where <table_name> - the name of the table to be locked; <lock_type> - lock type; the following types are possible:
SHARED_READ
SHARED_WRITE
PROTECTED_READ
PROTECTED_WRITE
See Interbase manual for details.
The following table shows implemented Interbase data types and their XBase equivalents.
Table 13-4. Implemented Interbase data types
| Interbase data type | CLIP data type |
|---|---|
| ARRAY | ARRAY |
| BLOB | CHARACTER |
| CHAR | CHARACTER |
| DATE | DATE |
| DECIMAL | NUMERIC |
| DOUBLE PRECISON | NUMERIC |
| INTEGER | NUMERIC |
| FLOAT | NUMERIC |
| NUMERIC | NUMERIC |
| SMALLINT | NUMERIC |
| TIME | DATETIME |
| TIMESTAMP | DATETIME |
| VARCHAR | CHARACTER |
To be able to build an application using ODBC manager, appropriate packages must be installed. It includes ODBC development files (iODBC2 or unixodbc) and clip-odbc package. See Installation chapter for details.
Link your application with ODBC driver by adding -lclip-odbc to the end of clip command:
$clip -e test.prg -lclip-odbc
To obtain a connect to the database server through ODBC manager use ConnectNew() with the following syntax:
ConnectNew("ODBC",<cDSN>,[<cLogin>],[<cPassword>],NIL,NIL,NIL,NIL,[<cCharset>])where
"ODBC" - identifier of ODBC driver; see also SQLList()
cDSN - a string containing Data Source Name (see ODBC manual and odbc.ini)
cLogin - a string containing user login
cPassword - a string containing user's password
cCharset - charset used by server; if not passed, SET("SQL_CHARSET") is used
To bind rows in the data base and rows in the local set a field with the name passed in <cIdName> parameter to TRowset constructor is used. See TConnect:CreateRowset() description for details.
With ODBC you have to set appropriate transactions isolation level manually. TConnect:Start() ignores passed parameters.
The following table shows implemented ODBC data types and their XBase equivalents.
Table 13-5. Implemented ODBC data types
| ODBC data type | CLIP data type |
|---|---|
| TINYINT | NUMERIC |
| SMALLINT | NUMERIC |
| INTEGER | NUMERIC |
| BIGINT | NUMERIC |
| DECIMAL | NUMERIC |
| DOUBLE | NUMERIC |
| NUMERIC | NUMERIC |
| FLOAT | NUMERIC |
| REAL | NUMERIC |
| CHAR | CHARACTER |
| VARCHAR | CHARACTER |
| LONGVARCHAR | CHARACTER |
| DATE | DATE |
| TIME | ARRAY {h,m,s}; h,m,s - NUMERIC |
| TIMESTAMP | DATETIME |
| LONGVARBINARY | CHARARCTER |
To be able to build an application using DBTCP proxy, appropriate packages must be installed. It includes DBTCP development files and clip-dbtcp package. See Installation chapter for details.
Link your application with DBTCP driver by adding -lclip-dbtcp to the end of clip command:
$clip -e test.prg -lclip-dbtcp
To obtain a connect to the database server through DBTCP proxy use ConnectNew() with the following syntax:
ConnectNew("DBTCP",[<cHost>],[<cPort>],[<cLogin>],[<cPassword>],<cDSN>,NIL,NIL,[<cCharset>])where
"DBTCP" - identifier of DBTCP driver; see also SQLList()
cHost - host name DBTCP server running on
cPort - server port number as string
cLogin - a string containing user login
cPassword - a string containing user's password
cDSN - a string containing Data Source Name
cCharset - charset used by server; if not passed, SET("SQL_CHARSET") is used
To bind rows in the data base and rows in the local set a field with the name passed in <cIdName> parameter to TRowset constructor is used. See TConnect:CreateRowset() description for details.
With DBTCP you have to set appropriate transactions isolation level manually. TConnect:Start() ignores passed parameters.