3 Replies Latest reply on May 21, 2013 8:53 AM by philmodjunk

    JDBC queries when columns have spaces in them

    nkilleen

      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