Syntax error in prepared statement that runs in SQL

Question

I'm getting a syntax error in my prepared statement even though my query runs in SQL Management Studio. I am using GlassFish 4.1.1. What am I doing wrong?

I've tried switching the syntax around a bit but I always get an error.

Here is my connection pool code:

try {
    InitialContext ic = new InitialContext();
    dataSource = (DataSource) ic.lookup("java:comp/env/" + database);
} catch (Exception ex) {
    ex.printStackTrace();
}

Here is my query code:

ConnectionPool pool = new ConnectionPool("BoxPointHAMBURGO");
Connection connection = pool.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;

try {
    String query = "SELECT Tabla FROM BoxPointHAMBURGO.dbo.NombresTablas";

    ps = connection.prepareStatement(query);
    rs = ps.executeQuery();
} catch (Exception ex) {
    System.err.println("Error: " + ex.getMessage());
}

The error that I get is:

Syntax error: Encountered "." at line 1 column 39.

It's like puting .dbo.NombresTablas in this case, that one also gives me an error in my Java code but not in SQL Management Studio — Wynn
– Wynn, Commented Jun 3, 2019 at 17:40
What is MAINDB value? It that a non-empty constant? Can you please add the whole query variable value? — Karol Dowbecki
– Karol Dowbecki, Commented Jun 3, 2019 at 17:53
@KarolDowbecki yes, MAINDB is BoxPointHAMBURGO, I will edit my question — Wynn
– Wynn, Commented Jun 3, 2019 at 17:55

Karol Dowbecki · Accepted Answer · 2019-06-03 19:56:14Z

1

As per this answer the double dot .. operator results in default schema for the current database user being used for the query. However you shouldn't expect that SQL Management Studio query syntax will work when using JDBC. These are two completely different driver interfaces with different limitations, JDBC most likely being more restrictive.

You probably should select the BoxPointHAMBURGO database when you establish the JDBC connection. You would have to modify the JDBC URL as per Building the Connection URL the syntax is:

jdbc:sqlserver://localhost;databaseName=BoxPointHAMBURGO

and then remove the database name from the SQL query:

SELECT Tabla FROM dbo.NombresTablas

Do note that tables under dbo schema can only be accessed by the user that is the database owner.

edited Jun 3, 2019 at 19:56

answered Jun 3, 2019 at 18:01

Karol Dowbecki

45.3k9 gold badges83 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Wynn Over a year ago

I have that URL in my context.xml. I removed the database from the query but that just gave me a new error that says Schema 'DBO' does not exist even though I'm sure it does exist.

Karol Dowbecki Over a year ago

As per linked answer the user used to open database connection is not the database owner so it can't see dbo. objects.

Gord Thompson Over a year ago

re: "the double dot .. operator results in default dbo schema being used for your query" - No, it results in the effective schema being the one that is specified as the default schema for the current database user. Very often that is dbo, but not always.

Collectives™ on Stack Overflow

Syntax error in prepared statement that runs in SQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related