%SQL.Migration.Import

Connect to the client system via an SQL Gateway Connection (ODBC or JDBC), and initializes some structures

Arguments:

dsn - is the name of the SQL Gateway Connection to connect to

localuser - obsolete

isJDBC - 1/0 flag. If this SQL Gateway connection is via JDBC use 1, otherwise use 0. 0 (ODBC) is the default parameter value

CopyData (for ODBC connection) - Copies the data from the source system to this system

1 - get list of fields on source table (through gateway)

2 - construct select list for source

3 - construct update list for dest

4 - select loop on source

* for each, insert into destination

* for each Blob on source:

* while whole blob not fetched

* fetch blob block from source

* add blob to destination blob

5 - create indices, ....or anything

Arguments:

TbOwner - Name of the owner/schema of the target table

TbName - Name of the target table

ScrubRoutine - Name of a routine to call to scrub/modify the incoming data

DATA SCRUBBING:
For each data import process, a Routine can be speficied to modify field values prior to inserting them into Caché. This also allows to skip specific rows of data, preventing their insertion into Caché. The user-written Data scrubbing routine must provide 2 entry points.

Metadata(TbOwner,TbName,colNums,colSqlTypes,colStreamTypes)

Data(colData)

Metadata is called once for each table to copy, to pass information about the TableOwner, TableName, ColumnNames and Datatypes. It is a good place to perform the processing that needs to be done once per table.
Arguments:

TbOwner - The SQL Schema in the origin datasource

TbName - The SQL Table name

colNums- An array 1..N. One element per column in the table. colNum(i)="Column_Name"

colSqlTypes - An array 1..N One element per column in the table. The value is the SQL Datatype in the source Database. Values are ODBC Values as defined in %SQLMigration.inc:

#define SQLCHAR 1

#define SQLBIGINT -5

#define SQLBINARY -2

#define SQLBIT -7

#define SQLDATE 9

#define SQLDECIMAL 3

#define SQLDOUBLE 8

#define SQLFLOAT 6

#define SQLGUID -11

#define SQLINTEGER 4

#define SQLLONGVARCHAR -1

#define SQLLONGVARBINARY -4

#define SQLNUMERIC 2

#define SQLREAL 7

#define SQLSMALLINT 5

#define SQLTINYINT -6

#define SQLTIME 10

#define SQLTIMESTAMP 11

#define SQLVARBINARY -3

#define SQLVARCHAR 12

colStreamTypes - An array 1..N. One element per column in the table. The value is

0: if the column data is not a stream

1: if the column data is contained in a %CharacterStream

2: if the column data is contained in a %BinaryStream
Return value: none (Quit with no argument)

Data is called once for each row fetched from the origin datasource. This function must return 1 to insert this Row, and 0 if the Row should not be inserted into Caché.
Arguments:

colData() - Is an array 1..N of values. colData(i) is the column Data in ODBC format if the column is not a Stream (a Date is represented in the YYYY-MM-DD Format. An empty String is represented by a $c(0) character, and a NULL value is represented as ""). For BLOB columns, represented in Caché as Streams, it is an ID to an Open %CharacterStream or %BinaryStream as specified in the colStreamTypes array that was passed to the Metadata function. To change the value of a column 'i', the Data function can simply modify the value in colData(i). To change the value of a Blob column, the Data function can modify the Stream, or modify the Stream reference. It must return in colData(i) a $zobjint() of an oref to a Stream.

CopyDataJ (for JDBC connection) - Copies the data from the source system to this system

1 - get list of fields on source table (through gateway)

2 - construct select list for source

3 - construct update list for dest

4 - select loop on source

* for each, insert into destination

* for each Blob on source:

* while whole blob not fetched

* fetch blob block from source

* add blob to destination blob

5 - create indices, ....or anything

Arguments:

TbOwner - Name of the owner/schema of the target table

TbName - Name of the target table

ScrubRoutine - Name of a routine to call to scrub/modify the incoming data

DATA SCRUBBING:
For each data import process, a Routine can be speficied to modify field values prior to inserting them into Caché. This also allows to skip specific rows of data, preventing their insertion into Caché. The user-written Data scrubbing routine must provide 2 entry points.

Metadata(TbOwner,TbName,colNums,colSqlTypes,colStreamTypes)

Data(colData)

Metadata is called once for each table to copy, to pass information about the TableOwner, TableName, ColumnNames and Datatypes. It is a good place to perform the processing that needs to be done once per table.
Arguments:

TbOwner - The SQL Schema in the origin datasource

TbName - The SQL Table name

colNums- An array 1..N. One element per column in the table. colNum(i)="Column_Name"

colSqlTypes - An array 1..N One element per column in the table. The value is the SQL Datatype in the source Database. Values are ODBC Values as defined in %SQLMigration.inc:

#define SQLCHAR 1

#define SQLBIGINT -5

#define SQLBINARY -2

#define SQLBIT -7

#define SQLDATE 9

#define SQLDECIMAL 3

#define SQLDOUBLE 8

#define SQLFLOAT 6

#define SQLGUID -11

#define SQLINTEGER 4

#define SQLLONGVARCHAR -1

#define SQLLONGVARBINARY -4

#define SQLNUMERIC 2

#define SQLREAL 7

#define SQLSMALLINT 5

#define SQLTINYINT -6

#define SQLTIME 10

#define SQLTIMESTAMP 11

#define SQLVARBINARY -3

#define SQLVARCHAR 12

colStreamTypes - An array 1..N. One element per column in the table. The value is

0: if the column data is not a stream

1: if the column data is contained in a %CharacterStream

2: if the column data is contained in a %BinaryStream
Return value: none (Quit with no argument)

Data is called once for each row fetched from the origin datasource. This function must return 1 to insert this Row, and 0 if the Row should not be inserted into Caché.
Arguments:

colData() - Is an array 1..N of values. colData(i) is the column Data in ODBC format if the column is not a Stream (a Date is represented in the YYYY-MM-DD Format. An empty String is represented by a $c(0) character, and a NULL value is represented as ""). For BLOB columns, represented in Caché as Streams, it is an ID to an Open %CharacterStream or %BinaryStream as specified in the colStreamTypes array that was passed to the Metadata function. To change the value of a column 'i', the Data function can simply modify the value in colData(i). To change the value of a Blob column, the Data function can modify the Stream, or modify the Stream reference. It must return in colData(i) a $zobjint() of an oref to a Stream.

Copy Foreign Keys from remote DB via ODBC to Cache for the given FK Table.
Arguments:

FkTbOwner - Name of the owner/schema of the foreign key table

FkTbName - Name of the target table to get Foreign Keys from

Copy Foreign Keys from remote DB via JDBC to Cache for the given FK Table.
Arguments:

FkTbOwner - Name of the owner/schema of the foreign key table

FkTbName - Name of the target table to get Foreign Keys from

Cleanup previous version of table (Table,Index,Foreign Keys), re-create table with Fields, Unique and Primary Key constraints, and Index Definitions
Arguments:

TbOwner - Name of the owner/schema of the table

TbName - Name of the target table

verbose - 1/0 flag. If 1 display output to screen. Default os 0.

Copy One View and its dependencies via ODBC
Arguments:

VOwner - Name of the owner/schema of the view

VName - Name of the target view

Copy One View and its dependencies via JDBC
Arguments:

VOwner - Name of the owner/schema of the view

VName - Name of the target view

Find dependencies. Copy each dependency View via ODBC
Arguments:

VOwner - Name of the owner/schema of the view

VName - Name of the target view

Find dependencies. Copy each dependency View via JDBC
Arguments:

VOwner - Name of the owner/schema of the view

VName - Name of the target view

Disconnect from the SQL Gateway (ODBC)

Returns the current number of import errors.

Returns Database handle for current connection ("" or 0 if not connected)