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:
Last updated