Selecting from FileMaker table via JDBC when the table name contains spaces

Question

I am using JDBC to get data out of a file maker server v12.

For some unknown reason filemaker allows you to have spaces in your table names. I am unable to select these tables because I just get a syntax error.

I have written an application in java to get the data out. Does anyone have any idea how i can select the data from a table with a space in it?

EDIT (from OP's comments):

This is the Java part:

String selectSQL = "SELECT "+this.getImportableColumnsString()+" FROM "+this.getTableName();
PreparedStatement preparedStatement = this.connection.prepareStatement(selectSQL);
ResultSet rs = preparedStatement.executeQuery();

Could you post the relevant part of your code and details about the syntax error you're getting? — PM 77-1, May 14 '13 at 21:02
With coloumn names brackets work afaik. With table names select * from [table name with space]; does not work? (Just asking) — skandigraun, May 14 '13 at 21:05
It is in Java `code`String selectSQL = "SELECT "+this.getImportableColumnsString()+" FROM "+this.getTableName(); PreparedStatement preparedStatement = this.connection.prepareStatement(selectSQL); ResultSet rs = preparedStatement.executeQuery();`code` — Daryl B, May 14 '13 at 21:07
@user723168 - You seem to use `getTableName()` to obtain a table name. Even if this table name has spaces it would not produce a **syntax** error. Are you sure about the type of the error you're getting? Have I missed something? — PM 77-1, May 14 '13 at 21:15
Just found out it requires double quotes on the tables and column names. " the square brackets [] didnt work. — Daryl B, May 14 '13 at 21:16

score 3 · Answer 1 · answered Jan 14 '14 at 18:45

3

As mentioned in a comment to the question, if the FileMaker table name contains spaces then it must be enclosed in double-quotes in the SQL statement, e.g.,

String selectSQL = "SELECT * FROM \"table name\"";

answered Jan 14 '14 at 18:45

Gord Thompson

116,920
32
215
418

score 0 · Answer 2 · answered May 14 '13 at 21:04

My first thought is that you could put the table name within ' characters like: SELECT * FROM 'my table'. Does this not work?

Otherwise I suggest you contact the Filemaker Server support on this page: http://help.filemaker.com/app/ask It is likely that they have had this question before and knows how to build the query.

//Flipbed

score 0 · Answer 3 · answered May 15 '13 at 14:33

I'm pretty sure in the docs for OCDB and JDBC support FileMaker says that tables may have to comply to naming conventions that are stricter than what FileMaker allows. It is easy to change table names in FileMaker if you have admin access to the database you are sourcing why not just replace the spaces in the table names with underscores.

Selecting from FileMaker table via JDBC when the table name contains spaces

3 Answers3