|
The .NET Data Provider Object
Generic .NET Provider object used for all 3rd party .NET data providers. If your database vendor supplies a .NET data provider specific to their database, it is best to use this data object. In most cases, the database specific data provider provided by your database vendor is optimized for use with their particular database. For instance, Oracle® offers ODP.NET (Oracle Data Provider for .NET) which is highly optimized for use with Oracle databases and uses direct access through SQL*Net.
To use this data object, simply drag and drop it onto the designer window and dbl-click it (or press F4) to fill it the properties specific to your database. Most properties are similar among all the database type objects, however; the most important property to note for this object is the DataProviderPrefix property.
Each .NET data provider's functions are named (prefixed) specifically for each database. For instance, the MS SQL Server connection command is SqlConnection, whereas Oracle's .NET data provider is OracleConnection. So, to connect to any .NET data provider you simply need to fill in the DataProviderPrefix with the correct prefix for that data provider. In the case of Oracle's .NET data provider, it would simply be: Oracle. IBM® has also recently released a .NET Data provider for DB2. In this case, the DataProviderPrefix would be: DB2
DataDirect Technologies also offers a wide array of .NET Data providers. http://www.datadirect-technologies.com
Properties
Action |
Describes the action to take for output streams. Ignored for input streams. Valid options are:
| ||||||||||||||
AppendSQLWith |
This property will only be used when employing SQL Batching - when the RowsPerTransaction property is greater than 1. This is the character that will be appended to the end of each SQL statement in the batch. Default = ; | ||||||||||||||
Catalog |
Database catalog name. For instance: Northwind. Some databases do not use the concept of catalogs in which this property should be left blank. | ||||||||||||||
ColumnLayout |
The column layout (or file structure) of the table. Click on the Ellipse (...) button to fill in the column layout, or select an existing column layout from the repository. See also (Column level Write Flags) | ||||||||||||||
CommandTimeout |
Specify the maximum number of seconds to wait for any command that is sent to the database. Once this time is exceeded you will receive a timeout error. | ||||||||||||||
ConnectString |
(read only) This property displays the ConnectString that will be used to connect to the data source. It is built automatically by deriving the information from the other properties. | ||||||||||||||
ConnectStringOtherParams |
Any custom parameters to be included in the ConnectString. (example: see OleDb) | ||||||||||||||
CreateTable |
The create table property allows you to create your output table in the database during the job execution. You can also choose to DROP the table prior to creating it. The create table wizard will also generate your create table statement for you based on the pre-defined ColumnLayout. | ||||||||||||||
DataProviderPrefix |
The prefix for the .NET Data provider. For instance: Sql, Oracle or DB2 | ||||||||||||||
DateTimeFormat |
DateTime format mask to use when loading the data. This is a useful way to format all columns defined as DateTime to standard format. It saves you time from having to format each column individually in the Join object. If the column is left blank the date will be read as it is received from the database. Click on the ellipse to use the mask builder tool. | ||||||||||||||
RowsPerTransaction |
This data object supports batch SQL transactions which can greatly enhance the loading speed of a BlueSky Integration Studio job. Simply enter the number of SQL statements to place in the internal queue before submitting them to the database. For instance; by entering 1000 in this property, BlueSky Integration Studio will not attempt to send the sql commands until 1000 records have been read from the input source, or until the last record is read (i.e. if there are only 300 input records, of course it will then write them to the database). The database can process 1000 sql statements at once, much faster than 1000 individually sent sql statements. (see also: What is SQL Batching?) | ||||||||||||||
Server |
The server name where your database resides. This can be tricky depending on the database. For instance, for MS SQL Server you would enter the DNS name or TCP/IP address of the server. For Oracle you would enter the database SID. | ||||||||||||||
Signon |
The user name and password to use to connect to the database. The password is hidden in this property. Click the Ellipse (...) button on the property where you can enter both the user name and hidden password. | ||||||||||||||
Tablename |
Database table name | ||||||||||||||
TextEncoding |
| ||||||||||||||
Comments |
Comment area to give descriptive text about the data object and its purpose. | ||||||||||||||
Alias |
Alias identifier. Aliases are used on all design objects to simplify coding and to ensure each object has a unique identifier. | ||||||||||||||
Name |
Descriptive name for this object. You can enter any text to describe the object | ||||||||||||||
GenerateSql |
Yes / No. By default, the data object will automatically generate the SQL Select statement for you deriving the information from other properties such as the Tablename and ColumnLayout properties. By changing this property to No you can hand-code the SQL statement in the SQLSelect property window. This gives you complete flexibility to enter SQL statements that join multiple tables together and/or use database optimizer hints. | ||||||||||||||
OnPostDMLFailure |
ContinueOn / AbortJob : When DML (data manipulation language) statements are entered into the PostDML property, you can control whether your job continues executing if the DML statement fails for some reason. DML statements include any valid UPDATE, INSERT or DELETE type of SQL statements. (see Pre / Post DML example) | ||||||||||||||
OnPreDMLFailure |
ContinueOn / AbortJob : When DML (data manipulation language) statements are entered into the PreDML property, you can control whether your job continues executing if the DML statement fails for some reason. DML statements include any valid UPDATE, INSERT or DELETE type of SQL statements. (see Pre / Post DML example) | ||||||||||||||
PostDML |
DML (data manipulation language) statement to execute after the job or Join object completes. Any valid INSERT, UPDATE or DELETE type of SQL Statement. (see Pre / Post DML example) | ||||||||||||||
PreDML |
DML (data manipulation language) statement to execute before the job or Join object starts. Any valid INSERT, UPDATE or DELETE type of SQL Statement. (see Pre / Post DML example) | ||||||||||||||
PreTransactionSQL |
Some databases, like Oracle, require that multiple batched SQL statements are wrapped in a SQL code block. For instance; when using SQL Batching in Oracle, you must set this property to BEGIN (see How to use SQL Batching in Oracle) so that the SQL Statements are within a PL/SQL code block. This property will only be used when RowsPerTransaction is greater than 1. | ||||||||||||||
PostTransactionSQL |
Some databases, like Oracle, require that multiple batched SQL statements are wrapped in a SQL code block. For instance; when using SQL Batching in Oracle, you must set this property to END; (see How to use SQL Batching in Oracle) so that the SQL Statements are within a PL/SQL code block. This property will only be used when RowsPerTransaction is greater than 1. | ||||||||||||||
SQLOutputFile |
(output only). This SQLOutputFile is the path and filename to a file where you want the actual SQL statements being generated to be logged. This can come in handy when you are trying to debug a job, or some problematic data. In production it is best to leave this property empty. For instance: c:\jobsql.log | ||||||||||||||
SQLSelect |
The SQL Select statement that will be used for data objects used in input streams. The SQL statement is built automatically by deriving information from other properties such as the Tablename and ColumnLayout properties. You can override this SQL Statement by setting the GenerateSQL property to No. |
© 2003 - 2007 Relational Solutions, Inc. - All rights reserved