class %SQL.Migration.Import
extends %RegisteredObject, Util
Imports Objects from Relational Databases
|
Parameters
|
Properties
|
Methods
|
Queries
|
Indices
|
ForeignKeys
|
Triggers
|
|
|
|
18
|
|
|
|
|
• method Connect(dsn As %String, localuser As %String = "", isJDBC As %Boolean = 0)
as %Status
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
• method CopyData(TbOwner As %String, TbName As %String, ScrubRoutine As %String)
as %Status
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.
• method CopyDataJ(TbOwner As %String, TbName As %String, ScrubRoutine As %String)
as %Status
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.
• method CopyTableFKeys(FkTbOwner As %Library.String, FkTbName As %Library.String)
as %Status
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
• method CopyTableFKeysJ(FkTbOwner As %Library.String, FkTbName As %Library.String, verbose As %Library.Boolean = 0)
as %Status
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
• method CopyTableStruct(TbOwner As %String, TbName As %String, verbose As %Boolean = 0)
as %Status
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.
• method CopyView(VOwner As %String, VName As %String)
as %Status
Copy One View and its dependencies via ODBC
Arguments:
VOwner - Name of the owner/schema of the view
VName - Name of the target view
• method CopyViewJ(VOwner As %String, VName As %String)
as %Status
Copy One View and its dependencies via JDBC
Arguments:
VOwner - Name of the owner/schema of the view
VName - Name of the target view
• method CopyViewRecursive(VOwner As %String, VName As %String, hstmt As %String)
Find dependencies. Copy each dependency View via ODBC
Arguments:
VOwner - Name of the owner/schema of the view
VName - Name of the target view
• method CopyViewRecursiveJ(VOwner As %String, VName As %String)
Find dependencies. Copy each dependency View via JDBC
Arguments:
VOwner - Name of the owner/schema of the view
VName - Name of the target view
• method Disconnect()
as %Status
Disconnect from the SQL Gateway (ODBC)
• method ErrorCount()
as %Integer
Returns the current number of import errors.
• method Gethdbc()
as %String
Returns Database handle for current connection ("" or 0 if not connected)
