Acquiring data from case-insensitive and case-sensitive databases

Database case-sensitivity considerations for when specifying the select query

Most databases treat table column names as case-insensitive. However, some databases, for example Sybase, treat table column names as case-sensitive. When defining the query that the probe will use to select events from the database, you must make sure that you do not use the same column name twice in different cases; for example, the query cannot contain both ColumnName and COLUMNNAME.

Case-insensitive and case-sensitive databases

Setting the MarkerColumnSensitive property correctly allows you to use the JDBC Probe to acquire data from both types of database. In most cases, you will set the MarkerColumnSensitive property set to false. This will support situations in which the probe compares the column of the result set returned with the marker column, ignoring the case when matching.

The examples in the rest of this topic describe various scenarios and how to set the MarkerColumnSensitive property in each situation.

Example configuration 1: Case-insensitive environment, query matching case of marker column

Suppose the database operates in a case-insensitive environment (for example: Microsoft SQL), and the query result is in the same case as the marker column selected.

For example, suppose the MarkerColumn is set to:

“DATEDIFF(s, '19700101', sd.AlertDateTime) AS UnixTime”

And the file specified by the SelectSqlFile property contains the following SQL command:

“SELECT DATEDIFF(s, '19700101', sd.AlertDateTime) AS UnixTime, SensorDataRowID FROM SensorData1 sd”

The probe will try to match the marker column UnixTime with the query result returned. MySQL will return the result as UnixTime and so the probe can find the marker column correctly.

In this scenario, it does not matter whether the MarkerColumnSensitive property is set to either TRUE or FALSE. This is because the probe can match the marker column either case-sensitive or case-insensitive. So leave this property set to its default value of FALSE.

Example configuration 2: Case-insensitive environment, query not matching case of marker column

Suppose the database operates in a case-insensitive environment (for example: DB2) and the query result is in a different case to that of the marker column selected.

For example, suppose the MarkerColumn is set to:

“(timestampdiff(2, char(lastmodified - timestamp('1970-01-01-00.00.00')))) AS unixtime”

And the file specified by the SelectSqlFile property contains the following SQL command:

“SELECT (timestampdiff(2, char(lastmodified - timestamp('1970-01-01-00.00.00')))) AS unixtime, user_id FROM user”

The probe will try to match the marker column unixtime with the query result returned. However, DB2 will return the query result in full capital case, that is: UNIXTIME, which is the standard behaviour of DB2.

In this scenario, you should set the MarkerColumnSensitive property to FALSE. This is because the probe will try to match the marker column unixtime with the returned result UNIXTIME, so must do so case-insensitive. In this case, probe able to find the matchable marker column with the query result that enable the partial resync performing correctly.

If the MarkerColumnSensitive property had been set to TRUE. The probe will be unable to match the marker column unixtime with the returned result UNIXTIME. The probe will write a warning message to the probe log and it will perform a full resynchronization (because the probe is unable to find a matchable marker column to use with the query result).

Example configuration 3: Case-sensitive environment, query not matching case of marker column

Suppose the database operates in a case-sensitive environment (for example: Sybase) and the query selects the same name but in a different case.

For example, suppose the MarkerColumn is set to: “a.Identifier”

And the file specified by the SelectSqlFile property contains the following SQL command:

“Select a.Identifier, as.IDENTIFIER, a.AlarmID FROM Alarm a, AlarmStatus as WHERE a.AlarmID = as.AlarmID”

In a case-sensitive environment, the probe must compare the marker column case-sensitive.

In this scenario, you should set the MarkerColumnSensitive property to TRUE This will enable the probe to match the marker column Identifier with the correct case Identifier in the returned result, and will prevent the probe from mistakenly matching it with the wrong case IDENTIFIER.