Acquiring data from case-insensitive and case-sensitive databases
The probe supports database that operate in either a case-sensitive environment or in a case-insensitive environment.
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
.