Data Source Object

Data source is a sub-object to the data object, but can be instantiated independently.  It provides database connection information and allows running of SQL commands against a connected database resource (table, view or stored procedure). In most cases, the data source is set up through the Input data source editor.

The object is exposed as ITDataSource interface.

Properties

valid

The state of the data source object. True: the database resource is ready to be used through other methods of the data source object.  False: the data object is not ready.

You can set this property to false to forcefully invalidate an object.

Read/Write property

Type: boolean

To get the variable's value:

   If DS.valid Then DS.Create

To set the variable's value:

   DS.valid = false

dsn

Dsn stands for Data Source Name, which is a server name for an OLEDB connection and an ODBC DSN in for an ODBC connection. This property is not in use for file-based data sources.

Read/Write property

Type:string

DS.dsn = "SQLServer"    'OLEDB server name

DS.dsn = "DSNMsAccess"  'ODBC dsn

database

A database name or a catalog name of the associated database resource. For file-based data sources it is the folder path where the database table is located.

Read/Write property

Type:string

DS.database = "MyDatabase" 'OLEDB, ODBC

DS.database = "c:\data\dbase_files" 'DBase

DS.database = "c:\data\access\my.mdb" 'File based databases

DS.table    = "Accounts"  ' the data source is set to the "Accounts" table of "my.mdb" access database

alias

An alias for the table that can be used in place of the table name. Use it to simplify SQL expressions, which use this resource by name. For example, a complex fully qualified database table name like [Server].[Database].[Schema].[Table Name] can be replaced with something as simply as "Table1".

Read/Write property

Type:string

DS.alias = "Table1"

table

Name of the database resource( table, view, function... ).

Read/Write property

Type:string

DS.database = "c:\data\access\my.mdb" 'File based databases

DS.table    = "Accounts"  ' the data source is set to the "Accounts" table of "my.mdb" access database

extension

The database file extension when the database is file-based.

Read/Write property

Type:string

DS.database  = "c:\data\dbase_files"

DS.table     = "accounts"

DS.extension = ".dbf"

clsid

ClassID or ProgID for OLEDB providers.

Read/Write property

Type:string

DS.clsid = "MSDASQL"

schema

The schema name of the database resource for data sources which support schemas.

Read/Write property

Type:string

DS.schema = "dbo"

user

User ID for authentication purposes.

Write only property

Type:string

DS.user = "administrator"

password

User password for authentication purposes.

Write only property

Type:string

csv_positional

Text/CSV file related property specifying if the output file will be created as delimited or fields will be positioned accordingly to the fields widths in the output dictionary.

True - positional output;

False - delimited output, Read/Write property

Type:boolean

csv_col_separator

Text/CSV file related property specifying the character(s) used to separate fields/column values in a CSV or Text file. 

Read/Write property

Type:string

DS.csv_positional = false     ‘delimited file, not positional output

DS.csv_text_qualifier = "||"  ‘set the text qualifier to ||

DS.csv_col_separator = ","    ‘set field delimiter to comma

csv_include_column_names

Text/CSV file related property specifying if the file contains the column names within the file.

Read/Write property

Type:boolean

csv_text_qualifier

Text/CSV file related property specifying the character(s) which enclose field values.

Read/Write property

Type:string

Methods

GetAsString

Returns a fully qualified data source name in form of a string.  For example: C:\data\accounts.dbf, SQLServer\DB1\dbo\accounts, etc.

Function GetAsString()

Parameters:

none

Return value:

string - a string representation of the data source resource.

An exception is thrown in case of errors.

DS.GetAsString()

SetDS

Initializes the data source object from another data source object or from a string. (variant)

Function SetDS( ds )

Parameters:

variant ds - string: a file path to the database resource for file based databases; data source object: an initialized data source object from which to copy the settings. 

Return value:

none

An exception is thrown in case of errors.

Examples:

You can set output table using two methods: setting the table name (assuming you are connected to a database) - (a) and setting the entire data source - (b):

If you are writing script outside TextConverter the API looks like this:

Set DS = TextConverter.GetOutputDS()

DS.table = "tablename" )               ‘- (a)

DS.SetDS( "c:\data\output.dbf" )       ‘- (b)

If you are writing script inside TextConverter it looks like this:

This.DSOut.table = "tablename" )             ‘- (a)

This.DSOut.SetDS( "c:\data\output.dbf" )     ‘- (b)

      An Excel file is also considered as a database, so you can set the worksheet as:

   Set DS = TextConverter.GetOutputDS()

   DS.table = "worksheetname" )               ‘- (a)

DS.SetDS( "...\file.mdb:worksheetname" )   ‘- (b)

      Additional examples:

 'Sample 1

 DS.SetDS( ds1 ) 'ds1 - pre initialized data source object

 'Sample 2

 DS.SetDS( "c:\data\access.mdb" )

 DS.table = "Table1"

 'Sample 3

 DS.SetDS( "c:\data\input.csv" )

TableExists

Check if the underlying database resource exists in the database.

Function TableExists()

Parameters:

none

Return value:

boolean - true: the underlying database resource exists in the database, otherwise false.

An exception is thrown in case of errors.

If DS.TableExists() Then DS.Drop()

Drop

Removes the data source resource from the database.

Function Drop()

Parameters:

none

Return value:

none

An exception is thrown in case of errors.

If DS.TableExists() Then DS.Drop()

GetRelation

Returns a relation object, which defines relations between the two tables, which are passed in as parameters. Use of aliases to identify the tables rather than table names is recommended.

Function GetRelation( left, right )

Parameters:

string left - the left table identified by an alias or a table name.

string right - the right table identified by an alias or a table name.

Return value:

variant - a relation object (IRelation): an object representing the found relation, Empty otherwise.

An exception is thrown in case of errors.

Dim rel

Set rel = DS.GetRelation( "Accounts", "Items" )

rel.definition = "Accounts.AccountID=Items.AccountID"

ReplaceTable

Replaces a table contained in a multi-table dynamic view with another table.

Function ReplaceTable( old_name, new_name )

Parameters:

string old_name - a table alias or name for a table contained in the data source, which is a multi-table dynamic view.

string new_name - a new name to be set.

Return value:

none

An exception is thrown in case of errors.

DS.ReplaceTable  "Accounts", AccountsNew"

Execute

Executes an SQL statement against an underlying data source.

Function Execute( sql, ignore(optional, default = false) )

Parameters:

string  sql - an SQL expression to be executed.

boolean ignore - true: throw an exception in case of errors; false: do not throw an exception in case of errors.

Return value:

integer - the number of rows affected by this operation, if applicable, Empty otherwise.

Dim count

count = DS.Execute( "UPDATE Employees SET Salary=65000 WHERE Age > 45" )

DS.Execute "DELETE FROM Employees WHERE EmployeeID = 112

Create

Creates a database table according to this data source object setup.

Function Create()

Parameters:

none

Return value:

none

An exception is thrown in case of errors. 

DS.Create()