Home > Class Reference > ENSLIB namespace > %SQL.Import

%SQL.Import

class %SQL.Import

Parameters

parameter RTNPREFIX = IRISSQLImport;

Methods

classmethod AllocateRoutine(ByRef rtn As %String) as %Status [ Language = objectscript ]
Allocates a routine that we will use to generate an importer method
classmethod CreateWorkerRoutine(Table, Routine, ByRef HeaderFile, ByRef HeaderInsert) [ Language = objectscript ]
This Method generates an importer routine that will take in a full row, parse out the columns one-by-one and align them to be put on disk by INSERT-ing via SQL. This is the easiest way to go about things but is slower than setting globals directly
This method assumes that there are no triggers to run, does no referential integrity checks, and assumes the data from the CSV has already been checked and is correct

The following takes the name of a Table, a reference to a Routine created by calling ..AllocateRoutine() and then 2 arrays HeaderFile is an array of the columns from the Header file in the order they appear (and numbered 1..N for N columns)
HeaderInsert is the location in the array that the rows need to be in order to be inserted correctly
classmethod CreateWorkerRoutineDirect(Table, Routine, ByRef HeaderFile, ByRef ListLocation, ByRef TypeBySQLName, DataLocation="") [ Language = objectscript ]
This Method generates an importer routine that will take in a full row, parse out the columns one-by-one and align them to be put on disk by setting the global directly.
This method assumes that there are no triggers to run, does no referential integrity checks, and assumes the data from the CSV has already been checked and is correct

The following takes the name of a Table, a reference to a Routine created by calling ..AllocateRoutine() and then 3 arrays Header File is an array of the columns from the Header file in the order they appear (and numbered 1..N for N columns)
List Location is an array subscripted by column names, with values of where on the $LB they should go
TypeBySQLName is an array of the datatypes by SQL name. If they are one of the types we need to convert we add the conversion macro
The last parameter is the global name that where we put the data (including the leading ^).
classmethod DetermineStorage(Table As %String, ByRef Columnarr, ByRef ListLocations, ByRef DataLocation, ByRef TypeBySQLName) as %Boolean [ Language = objectscript ]
DetermineStorage looks at the class definition and determines whether or not we can use the direct-global way of importing data. This method tests: 1) Are we using standard storage - if not we will use the SQL method. While we could put in the work to do this for SQL Storage data, the work there is much larger, and too easy to get wrong for all the edge cases that SQL Storage invites. Might want to test more exotic types, or the SQL types at some point
classmethod GetClassName(Table As %String) as %String [ Language = objectscript ]
Gets the class name from a table name - this method should maybe be moved to a generat %SQL.Util class?
classmethod GetFunctionAndNameLookups(ClassName As %String, ByRef SQLNameByPropName, ByRef TypeBySQLName) as %Boolean [ Language = objectscript ]
The following method takes a class name and returns (by reference) 2 arrays. One (SQLNameByPropName) that gives the SQL name of each property in the given class and one that gives the datatype for each column (by SQLName) so we can reference a macro later and convert from the ODBC format (which the file should be in) to logical format
classmethod GetHeaderColumns(Table, Header, ByRef HeaderColNum) [ Language = objectscript ]
GetHeaderColumns takes the Table name and the Header column from the CSV and returns an array (HeaderColNum) that gives the order the columns need to be received in, subscripted by the column name.
classmethod ImportCSV(File As %String = "", Table As %String = "") [ Language = objectscript ]
Takes the name of the file to import, and the Table to import it into and imports the CSV file into the table.
classmethod ImportWorker(ByRef Header, ImportID, Start, End, Routine) [ Language = objectscript ]
Import worker Takes the Header array (Column names by order in the CSV), the Import ID (so we can have multiple imports running at once) the range of the global the worker should import, and the routine to call.

The worker gets a chunk off the temp global and has to pick it apart to find a row which it then passes off to the generated routine that will use similar techniques to pull the columns from the row and then put it onto disk - whether by SQL or by direct global set