Configuration
Everything that the PPJ Framework needs to know to connect to a database is configured in the sql.config file using XML. Sql.config is loaded at runtime using the following search order:
Current directory
Application's startup directory
Full path specified in the application's setting <sqlconfig>
Embedded resource file sql.config in the executable
User defined path used at runtime
All the settings that can be configured in sql.config can also be added programmatically using the PPJ.Runtime.Sql.SqlProperties class in which case an application may not need an sql.config file at all. See the PPJ Framework Reference for additional help on this class.
Specify full path of sql.config
To specify the full path of the sql.config file you need to add an application setting name "sqlconfig" to App.config.
This is the setting looks like:
The setting sqlconfig has been changed to Sql.Config for naming consistency. The old key is still supported.
User defined path
Starting with the PPJ 2010 release we have added a new method to SqlProperties to allow a developer to load the data sources configuration from any file. To load the SqlProperties collection use:
Settings
The values in italic are the default values when the setting is omitted.
Defines the ADO.NET driver. The name is the name of the provider, the assembly is the location of the assembly containing the ADO.NET data provider. For drivers that are part of the standard .NET framework (or are loaded in memory by the application), the assembly location can be omitted. The version string is simply used to return the value for DBP_VERSION. The brand value, which must be numeric, is used to return the value of DBP_BRAND.
It's very important to enter the correct name for the provider. it is an ADO.NET standard that the public classes in the driver must follow the naming convention of using the name of the provider followed by the type of class, for example: SqlDataReader, OracleDataReader, SQLBaseDataReader, etc.
Being able to alter the DBP_BRAND value for a data source allows you to control how an application is adapted to the different database servers.
Enables the autocommit mode. When autocommit is on, there is no transaction initiated and every executed sql statement is committed immediately.
When this flag is turned on, strings of length zero are converted to DBNull. This is the default for Oracle and changing this setting has no effect on Oracle databases.
If the "reading" optional setting is set to true, empty strings stored in the database as empty strings are converted to null strings when reading the bind values.
This setting can be used to configure the DbType used for string parameters. It's usually needed if the ADO.NET driver has problems converting UNICODE string parameters to ASCII. The only known instance where this setting is needed so far is when an older version of Oracle OCI is used. In most cases this setting can be omitted.
This setting can be used to configure the DbType used for DateTime parameters. Some ADO.NET drivers may require DateTime values to be passed as DbType.Date instead of DbType.DateTime, or DbType.String (in which case the PPJ Framework will format the date into a string).
If the format string is not specified, the PPJ Framework will use the format known to work with the current database.
The precision property truncates the DateTime value to the requested precision. This is necessary for databases like Oracle when the value passed by the .NET application contains milliseconds or microseconds and the database cannot use it in a where clause. A common safe setting is precision="second".
This setting can be used to configure the default DbType used for Decimal parameters. Some ADO.NET drivers may require that Decimal value are passed as string and/or that their precision is truncated at a certain number of digits.
The precision value is the maximum number of digits allowed for the Decimal parameter. Some ADO.NET drivers may not support .NET's full decimal precision and/or may not be able to round the parameter automatically.
Defines the DbType used for string parameters. It is important to select the ANSI type when connecting to a database that is not UNICODE and viceversa, otherwise some database servers (like SQLServer) may decline to use indexes, which may result in a big loss of performance.
When this flag is turned on, string parameters and into values are trimmed for trailing spaces. White space characters other than spaces (tabs, no-break space, etc.) are not trimmed. This setting is meant to allow the application to use CHAR columns as VARCHAR columns when the database engine or driver doesn't support autotrimming.
This is the default timeout value used by the connection.
When the database supports MARS, this parameters should be set to true to enable the PPJ to handle multiple result sets with the same connection. When ResultSet mode is on there is no need to support MARS since the entire result set is loaded in memory when the sql statement is executed.
Defines the placeholder to use when substituting bind variables in sql statements. When it's set to false (default), the standard "?" symbol is used as a placeholder. Newer ADO.NET drivers, like SQLServer or Oracle, require the use of named parameters instead of the question mark. When this setting is true, the PPJ substitutes bind variables using "p1,p2,p3,...". Additionally, some ADO.NET drivers (Oracle) require that the placeholder is prefixed. For Oracle the prefix is ":", while for SQLServer the prefix is "@".
When the preserve property is set to false, the PPJ Framework will recreate the parameters before each execution. This setting may be necessary for some databases like Sybase where the parameter object is altered by the execution of the statement and may interfere with the next execution. i.e. the size of the parameter is too small for the next value.
When the positional property is set to true, the PPJ Framework will create parameters named using their position, i.e. ":1. :2, :3", etc. Some ADO.NET drivers require this kind of syntax when defining sql parameters.
Determines whether the PPJ Framework opens a new database session for each connected handle, or if connections are pooled using the connection string. In CTD all connections to the same database use the same session, therefore a single COMMIT can be used to commit all handles (in CTD you can enable multiple connections mode in sql.ini).
The additional thread_shared setting indicates that when connections are pooled, this connection can be reused by different threads.
This is the actual connection string that is used by the ADO.NET driver to open the database session. The content of the connection string is highly dependent on the driver. You have to read the documentation about the driver you are using to find out all the different options. In the connection string you can also specify the user (User Id) and password (Password), when they are not specified (usually they should not be placed in the sql.config file), the PPJ appends the user/password information to the connection string.
One oversight in the ADO.NET specs in our opinion is the lack of a base class for sql exceptions. As a result, every ADO.NET driver has its own sql exception class and there is no common way to retrieve the error code and the error position, as there is no way to determine that an exception is caused by the database. The PPJ Framework can recognize sql exceptions coming from the following ADO.NET drivers: OLEDB, ODBC, SQLServer, Microsoft Oracle, Oracle Oracle, Gupta SQLBase.
The sql_exception setting allows you to specify the class that should be recognized as the sql exception (full name) and which fields or properties should be queried by the PPJ Framework to read the error code and error position.
When this property is set to true, the PPJ Framework converts binary values to their encoded string equivalent when populating table windows with automatically created columns.
This setting allows you to add any number of custom properties to the data source. Custom properties are accessible using the SqlProperties.CustomProperties string dictionary collection. To use it with a SalSqlHandle instance use hSql.Properties.CustomProperties["{name}"].
When this setting is set to true, the PPJ Framework will call the Prepare() method once before calling Execute(). Otherwise by default the Prepare() method is not used. Some databases, like Informix, need this setting or discard and re-prepare the executed statement each time.
When this setting is set to true, the PPJ Framework will use Oracles LOB data types (Clob, NClob, Blob) when passing binary values or strings longer than 4000 characters (or 2000 when encoding = Unicode).
When this setting is set to true, the PPJ Framework will automatically close the datareader when Commit or Rollback are invoked. Otherwise, some ADO.NET drivers may throw an exception if a data reader is open and not fully read.
This setting is similar to <null_empty_strings>, when it is set to true, date/time values that are equal to DateTime.MinValue are saved as nulls to the database.
Sample Configuration
This is a sample for the content of the sql.config file with the most typical configuration for known drivers.
Dynamic Configuration
All the properties that are configured in the sql.config file are also available at runtime. You can read, modify and create new connection properties at runtime without changing the sql.config file.
Existing sql connection properties are available through the static method SqlProperties.GetInstance(name). New database connections can be added using SqlProperties.Add(name). Both methods return an instance of SqlProperties. You can also retrieve the specific SqlProperties instance being used by a sql handle using the SalSqlHandle.Properties member.
Once you have an instance of SqlProperties, you can read or change any property listed below:
Name
The name of the database connection. This is the name that is selected by setting Sql.Database.
ConnectionString
The full connection string that is passed to the ADO.NET driver.
Autocommit
Autocommit flag.
NamedParameters
Indicates wether the database requires named parameters placeholders.
ParametersPrefix
The prefix that must be used with the database driver when the named parameters flag is true. Typically it's "@" or ":".
PreserveParameters
Indicates that the command parameters should be re-created internally before being assigned new values prior to calling Execute().
MultipleConnections
Turns on or off the multiple connection mode.
ThreadShared
When set to true and if MultipleConnection is off, the connection can be reused from other threads.
NullEmptyStrings
Flags that indicates if empty strings should be passed to the database as NULL.
Encoding
Determines the encoding for string values.
MarsSupported
Flag that indicates if the database driver supports Multiple Active Result Sets.
Timeout
The default command timeout value. Not to be confused with the connection timeout, which should be set in the connection string.
IsolationLevel
The default isolation level.
LongVarcharToString
This flag indicates that byte[] values coming from the database must be converted to strings using the default encoding on the client machine. It may be necessary to use this setting when the database stores longvarchar data in blob fields that are returned as byte[] arrays in ADO.NET.
DefaultStringType
The default DbType to use for string parameters. For example, It may be necessary for some ADO.NET drivers to specify DbType.StringFixedLength.
DefaultDateTimeType
The default DbType to use for DateTime parameters. Some ADO.NET drivers may require DateTime values to be passed as DbType.Date instead of DbType.DateTime, or DbType.String (in which case the PPJ Framework will format the date into a string).
DefaultDateTimeFormat
This is the formatting string to use when DefaultDateTimeType = DbType.String. If it's not specified, the PPJ Framework will use the format known to work with the current database.
DefaultDateTimePrecision
This is the truncation precision of DateTime values. If set to -1 (default) there is no truncation. Valid values are TimeSpan.TicksPer* constants.
DefaultDecimalType
The default DbType to use for Decimal parameters. Some ADO.NET drivers may require that Decimal value are passed as string.
DefaultDecimalPrecision
This setting is the maximum number of digits allowed for the Decimal parameter. Some ADO.NET drivers may not support .NET's full decimal precision and/or may not be able to round the parameter automatically.
ProviderName
The name of the ADO.NET provider. Typically this is the name of the database: Oracle, SqlServer, Sybase, SqlBase.
ProviderBrand
This is the code that is returned by the PPJ Framework when reading the DBP_BRAND value.
ProviderVersion
This is the string returned by the PPJ Framework when reading the DBP_VERSION value.
ProviderAssembly
The name of the assembly to load for the ADO.NET provider.
ProviderNamespace
The default namespace for the ADO.NET provider.
ProviderConnectionClass
The type of the Connection class for the provider. If left null, this is auto generated by adding "Connection" to ProviderName (which is an ADO.NET default naming convention).
ProviderAdapterClass
The type of the DataAdapter class for the provider. If left null, this is auto generated by adding "DataAdapter" to ProviderName (which is an ADO.NET default naming convention).
ProviderSqlExceptionName
The name of the exception class for the provider. Since ADO.NET doesn't specify a common base sql exception type, we have to detect sql exception using their name.
ProviderSqlExceptionErrorCode
The name of the member of the sql exception that contains the error code.
ProviderSqlExceptionErrorPosition
The name of the member of the sql exception that contains the error position.
CustomProperties
Collection of user-defined properties stored in sql.config in the form <add name="" value="" />.
UseOracleLOB
Determines when to use Oracle's LOB data types (Clob, NClob, Blob).
UsePrepare
Enables calling the Prepare() method internally once before calling Execute().
Last updated