CODEWITHSUNDEEP
javasql-serverjdbcprepared-statement
Wynn
Wynn

Reputation: 11

Syntax error in prepared statement that runs in SQL

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.

Upvotes: 1

Views: 522

Answers (1)

Karol Dowbecki
Karol Dowbecki

Reputation: 44970

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.

Upvotes: 1

Related Questions