This tutorial is third and the final part of the JDBC revisited series. In part one, we demonstrated on how you can connect to your database using a JDBC driver. In part 2, we showed you how you easy it is to extract the metadata of the data source through JDBC Driver.
In this tutorial, we will focus on DB Interoperability features offered by DataDirect JDBC drivers, i.e., the capability to write a SQL statement that can be executed against multiple databases, irrespective of the databases implementation. Every major database vendor has proprietary implementations of different functionalities and they impact the interoperability provided by open data access standards. Adoption of these open standards has enabled companies to reduce expenditures by delivering a high degree of interoperability among databases.
One of the key areas of interoperability is SQL up-leveling. DataDirect drivers provides SQL up-leveling across all open data access APIs. This gives developers a common way to implement these types of functionality—using the same code—regardless of the backend DBMS. In this tutorial we will focus on some of these interoperable features.
Every database has some sort of support for the data types like DATE, TIME and TIMESTAMP. These data and time formats may vary from database vendor to another. The escape clauses for DATE data type that can be used across databases is in the format of {d ‘yyyy-mm-dd’}.
Assuming you have same data on two different databases, you can use the following syntax to get the results on both of them.
SELECT * FROM EMPLOYEES WHERE HIRE_DATE > {d '2005-06-17'}
Similarly the escape sequences for TIME data type would be {t ‘hh:mm:ss’} and for TIMESTAMP data type would be {ts ‘yyyy-mm-dd hh:mm:ss’ }. DataDirect drivers have these escape syntaxes implemented in the same way across all data access APIs and thus allowing developers to use same code across different databases.
Similar to Date and Time stamp literals, database vendors have proprietary implementations of various scalar functions and thus having different syntaxes for using them, making the life of a developer harder. DataDirect rescues developers by implementing these scalar functions in a consistent manner so that they can be used against any database.
Numeric functions provide the ability to determine many mathematic values, such as sine, cosine, square root, absolute value and logarithms. Each of the many supported functions is called using the escape syntax, regardless of the database implementation. Here is an example of how to call square root function.
select {fn SQRT(SALARY)} from EMPLOYEES
To learn about all the Numeric functions that we have implemented across DataDirect drivers, visit this page.
String functions enable you to modify strings within the result set. Some of the basic operations that most of us do are changing the case of the strings, calculating substrings etc. Some of the string functions available to developers are UCASE, LCASE, RTRIM, LTRIM, LENGTH, and SUBSTRING. The following statement returns UCASE values for a row that is mixed case in the database. The following statement returns lower case values for a row that is mixed case in the database.
SELECT {fn LCASE(Last_Name)} FROM EMPLOYEES
To learn about all the String functions that we have implemented across DataDirect drivers, visit this page.
The time and data functions are very useful when developers need to manipulate date and time values, obtain days of week, or get the current time to use in a SQL statement. The following example uses the DAYNAME function to retrieve the date and day of the week the employee was hired.
SELECT hire_date, {fn DAYNAME(hire_date)} FROM EMPLOYEES
To learn about all the Date and Time functions that we have implemented across DataDirect drivers, visit this page.
System functions include the ability to retrieve a database name for the current connection, and the ability to check for a NULL value and substitute with another value if the expression is a NULL. The following example uses the USER function in a Select statement to retrieve the logged in username of an Oracle database.
SELECT {fn USER()} FROM dual
To learn about all the System functions that we have implemented across DataDirect drivers, visit this page.
DataDirect provides you a simple escape syntax that you can use to execute Stored procedures without having to worry about remembering database vendor specific syntaxes. The escape sequence for calling a stored procedure is
{[?=]call procedure-name[(parameter[,parameter]...)]}
and a simple example to illustrate is
CALL increment(10);
where increment is the stored procedure name and 10 is the parameter.
You can specify the character to be used to escape wildcard characters (% and _, for example) in LIKE clauses. The escape sequence for escape characters is:
{escape 'escape-character'}
For example, the following SQL statement specifies that an asterisk (*) be used as the escape character in the LIKE clause for the wildcard character %:
SELECT col1 FROM table1 WHERE col1 LIKE '*%%' {escape '*'}
While these are some of the interoperability features, we still have more ways to help you ease your efforts when you are working against different databases. This is one of the unfair advantages that you would be entitled to while using DataDirect drivers over open source or any other drivers available in market.
We are the developers, working for the developers to reduce their efforts and increase productivity in this world of proprietary implementations. All our JDBC drivers are available for trial, feel free to download and experience yourself how we they enhance your experience.