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:

1. Current directory

2. Application's startup directory

3. Full path specified in the application's setting <sqlconfig>

4. Embedded resource file sql.config in the executable

5. 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:

<?xml version="1.0"?>
<configuration>
     <appSettings>
        <add key="Sql.Config" value="z:\users\support\config\sql.config"/>
   </appSettings>
</configuration>
 

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:

SqlProperties.LoadSqlConfig( fullPath);

Settings

The values in italic are the default values when the setting is omitted.

<provider name="" namespace="" assembly="" version="" brand=""/>

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.

<autocommit value="true|false" isolation="RL|CS|RR|RO|SE"/>

Enables the autocommit mode. When autocommit is on, there is no transaction initiated and every executed sql statement is committed immediately.

<null_empty_strings value="true|false" reading="true|false"/>

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.

<default_string_type 
    value="String|StringFixedLength|AnsiString|AnsiStringFixedLength"
/>

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.

<default_datetime_type 
    value="Date|DateTime|String" 
    format="{format chars}" 
    precision="day|hour|minute|second|millisecond|microsecond"
/>

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".

<default_decimal_type value="Decimal|String" precision="{# of digits}" />

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.

<encoding value="ansi|unicode"/>

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.

<autotrim value="true|false" />

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.

<timeout value=""/>

This is the default timeout value used by the connection.

<mars_supported value="true|false"/>

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.

<named_parameters 
    value="true|false" prefix="@|:" 
    preserve="true|false" 
    positional="true|false"
/>

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.

<multiple_connections
    value="true|false" 
    thread_shared="true|false"
/>

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.

<connection_string value=""/>

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.

<sql_exception name="" code="" position=""/>

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.

<longvarchar_tostring value="true|false" />

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.

<custom_properties><add name="{name}" value="{value}"/>...</custom_properties>

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}"].

<use_prepare value="true|false" />

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.

<use_oracle_lob value="true|false" />

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).

<autoclosereaders value="true|false" />

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.

<null_minvalue_dates value="true|false" />

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.

<databases>
  <database name="NORTHWIND">
     <provider name="Sql" namespace="System.Data.SqlClient" assembly="" brand="31" />
     <autocommit value="false" />
     <named_parameters value="true" prefix="@" />
     <multiple_connections value="false" />
     <null_empty_strings value="true" />
     <connection_string value="Data Source=.;Initial Catalog=Northwind;Integrated Security=True" />
  </database>
  <database name="EMPLOYEE">
     <provider name="Oracle" namespace="Oracle.DataAccess.Client" assembly="Oracle.DataAccess" brand="28"/>
     <autocommit value="true"/>
     <named_parameters value="true" prefix=":"/>
     <multiple_connections value="false"/>
     <connection_string value="Data Source=EMPLOYEE"/>
  </database>
  <database name="ISLAND.OLEDB">
     <provider name="OleDb" namespace="System.Data.OleDb" assembly="" &#160;brand="1"/>
     <autocommit value="true" />
     <named_parameters value="false" />
     <multiple_connections value="false" />
     <connection_string value="Provider=SQLBASEOLEDB.1;Data Source=ISLAND" />
  </database>
  <database name="ISLAND.NET">
     <provider name="SQLBase" namespace="Gupta.SQLBase.Data" assembly="Gupta.SQLBase.Data" brand="1" />
     <autocommit value="true" />
     <named_parameters value="false" />
     <multiple_connections value="false" />
     <connection_string value="Database=ISLAND" />
  </database>
</databases>

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().