AnsweredAssumed Answered

JDBC queries when columns have spaces in them

Question asked by nkilleen on May 19, 2013
Latest reply on May 21, 2013 by philmodjunk

Title

JDBC queries when columns have spaces in them

Post

     Hi 

     I am working with a legacy data-base. It's hosted on FMP 12 Advanced servers.  Some column names

     contain spaces.

     I am attempting to query it via the JDBC layer but the spaces are getting me in trouble.

      

     I'd like to do a query like this

      

      

     SELECT IDENTIFIER FROM PATIENTS WHERE 'AIBL Number'=147
      
     so the problematic element is 'AIBL Number'  which is of type 'Number' (so no quote on the '147')
     This results in
      
          Exception in thread "main" com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC] FQL0018/(1:51): An expression contains incompatible data types.
           
           
          Other variations:
           
               SELECT IDENTIFIER FROM PATIENTS WHERE 'AIBL Number'='147'
                
               just results in an empty result
                
               also
                
                    SELECT IDENTIFIER FROM PATIENTS WHERE [AIBL Number]='147'
                     
                    gets
                     
                         Exception in thread "main" com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC] FQL0001/(1:39): There is an error in the syntax of the query.
                          
                
                
           
          Can someone advise if there is a syntax that will work ?   The ODBC/JDBC FMP guide does not
          go into this much detail.
           
           
          thanks
          Neil
           
      
      

Outcomes