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:

Last updated