Discussions

General J2EE: Cant use NVL of Nz functions on MSAccess through JDBC ODBC

  1. Dear friends,

    I am connectiong to a MS Access database throgh JDBC ODBC bridge and I

    am running my JSP on Tomcat 4.

    This is the query :
    SELECT Nz(FIRST_NAME,'')+' '+ Nz(MIDDLE_INITIALS,'')+' '+

    Nz(LAST_NAME,'')
    AS full_name
    FROM PASSENGER_DETAILS

    This query runs on MS Access ok without a problem. But Tomcat's

    console gives this error message and it doesnt work.
    SQLException: [Microsoft][ODBC Microsoft Access Driver] Undefined

    function 'Nz' in expression.

    I would be grateful If you can help.

    Thanks and regards,
    Ranjith
    ranjith at dmsswt dot com
  2. Nz is an Office function, not SQL. When you use the SQL-statements within Access, you can also use the complete Office-functionality. From outside (ODBC) you can only use pure SQL-statements(i.e. those that Access recognizes).

    So I'm affraid that you will have to find a work-around.
  3. Dear friends,
    I want to use NVL function of SQL with my query. HOw can I use that function with access through JDBC ODBC driver. NVL returns a specified value if the column value is null. There should be a function to use for this purpose. But I dont know that. If you know it pls write me.

    Thanks and best regards,
    Ranjith
  4. I had the same problem and found this solution (Access 2000, JDK 1.4.2):

    SELECT IIF(ISNULL(FIRST_NAME), '', FIRST_NAME) AS first_name FROM PASSENGER_DETAILS