DefineRecordset Method

Description

Defines a named recordset, for use with databases, replacing any existing recordset with the same name.

Syntax

Visual Basic
Public Function DefineRecordset( _ ByVal rs As String, _ ByVal connect As String, _ ByVal tables As String, _ ByVal columns As String, _ ByVal aliases As String, _ ByVal sqlwhere As String _ ) As String

Visual Basic

Public Function DefineRecordset( _
   ByVal rs As String, _
   ByVal connect As String, _
   ByVal tables As String, _
   ByVal columns As String, _
   ByVal aliases As String, _
   ByVal sqlwhere As String _
) As String

Parameters

rs

The named recordset to create or replace.

connect

The connect argument enables SIS to connect to the database containing the data to be mapped. The methods you can use to connect to a database are:

DAO (Microsoft Data Access Objects) Note: DAO is not available in 64-bit SIS.
ODBC (Open Database Connectivity)
Oracle (direct driver)
OracleCI (Oracle Instant Client)
ADO (Microsoft ActiveX Data Objects)
CSV (Comma Separated Values)

Using DAO the connect should be DAO, followed by a semi-colon, followed by the pathname of the database.
Using ODBC the connect should be ODBC, followed by a semi-colon, followed by a combination of the following components:

DSN=DataSourceName DATABASE=DatabasePathName UID=UserName PWD=Password LOGINTIMEOUT=seconds

Components should be separated by semi-colons.
If the DSN exists a connection will be established. The user name and password components are only necessary if the database to which the DSN refers requires them. If they are not required, you can omit these components altogether, or provide an empty string.
If the DSN does not exist you can create one "on-the-fly" by providing full connection details:

ODBC;DSN=Anything;DATABASE=C:\test.mdb;UID=MyName;PWD=topsecret
If insufficient information is provided then the standard Windows ODBC dialog will be displayed for the user to enter the required information.
Using Oracle the connect should be Oracle, and the following components:

Server=HostString User=UserName Password=Password
Components should be separated by semi-colons.
Using OracleCI (Oracle Instant Client) the connect should be OracleCI, followed by a semi-colon, followed by the connection string:

OracleCI;server=MyServer;user=UserName;Password=Password;Plaintext=True
For non-encrypted passwords Plaintext must be True. For encrypted passwords the Plaintext parameter must be omitted.
Using ADO the connect should be ADO, followed by a semi-colon, followed by the ADO Connection string.
For details on creating ADO connection strings refer to your programming language's documentation.

Using the CSV connection to load from a View Points overlay

To define a recordset from a CSV file (e.g. for subsequently creating a View Points overlay using “CreateDbPointOverlay”), the database connection string must be appended with a JSON string.

The following is a sample code in a C# environment:

string strConn = "CSV;<json configuration>";
@"CSV;
{
      "FileName": "G:\\Data\\incidents.csv",
      "CodePage" : 1252,
      "FileFormat" : "delimited",
      "FirstRowContainsFieldNames" :  true,
      "FieldDelimiter" : ",",
      "SkipLinesMatching" : "1",
      "HeaderLines" : 3,
      "KeepFirstLine" : true,
      "FooterLines" : 0,
      "DateOrder" : "DMY",
      "Columns" : [
             {
                    "Name" : "newColumnName",
                     "Type" : "double"
              },
             {
                    "Name" : "newColumnNameB",
                     "Type" : "double"
              }
        ]
}

SIS.DefineRecordset("rs", strConn, "incidents,incidents", "originx,originy", "originx#,originy#", "" );

Note: All parameters in this code are optional, except for FileName and Columns. Missing fields will be guessed by the CSV database connection.

The parameters that may be used for the Skip Lines options in a CSV file are; SkipLinesMatching, HeaderLines, KeepFirstLine, FooterLines.

tables

A comma-delimited list of tables which contain the columns referred to in the columns argument.

columns

A comma-delimited list of columns which contain the data which will be available when the named recordset is used.

aliases

A comma-delimited list of aliases for the columns referred to in the columns argument.

sqlwhere

An optional SQL WHERE expression, e.g. "(Table.StatusColumn = 'Pending' Or Table.StatusColumn = 'Agreed')". This expression should be wholly self-contained, using brackets if they are supported in the database, because under some circumstances, e.g. if a Spatial Reference is being used, SIS automatically generates a WHERE clause, with this expression appended using "And".

Note: The tables, columns and aliases arguments must all contain the same number of comma-delimited entries. The table/column/alias entry at each position in the each comma-delimited list must be consistent with the other two lists. When calling CreateDbBlobOverlay or CreateDbPointOverlay the nf arguments refer to positions within the table/column/alias comma-delimited lists, starting at 0.

Return Type

The full database connection string used.

Remarks

Available: GEO D OD OM SISpy

Groups:

Example

sis.DefineRecordset ("Depths", "", "Soundings,Soundings", "Northing,Easting", "X#,Y#", "Depth < 0")

Note: In the comma-delimited list in the example there must not be any spaces, i.e. "Soundings,Soundings" is correct but "Soundings, Soundings" (with a space) would not be correct.