jdbc and prepared statement problem

R

Ryan

I am having trouble getting my prepared statements to work.

I have a DAO and a test class to call it.

I think this means i'm passing a null parameter, but I checked with
system.out.println. I dont have any nulls?

SQLException caught!
java.sql.SQLException: Expected to find "an identifier" instead found "?" at
pos
ition 21.
at com.pointbase.net.netJDBCPrimitives.handleResponse(Unknown Source)
at com.pointbase.net.netJDBCPrimitives.handleJDBCObjectResponse(Unknown
Source)
at com.pointbase.net.netJDBCConnection.prepareStatement(Unknown Source)
at contactDAO.setContact(contactDAO.java:81)
at daoTest.main(daoTest.java:14)
Press any key to continue . . .



import java.sql.*;

public class contactDAO {

private static String SELECTQUERY = "select * from contact";
private String SELECTNAME = "SELECT * FROM CONTACT WHERE NAME = ?";
private String INSERTNAME = "INSERT INTO CONTACT (?, ?, ?, ?, ?,?)";
private static String DBCONNECTION =
"jdbc:pointbase:server://localhost:9092/contacts";
private static String USERNAME = "CONTACT";
private static String PASSWORD = "CONTACT";
public contactDAO () {
try {
Class.forName ( "com.pointbase.jdbc.jdbcUniversalDriver" );
} catch (Exception e) {
System.out.println("ERROR: failed to load pointbase JDBC
driver.");
e.printStackTrace();
return;
}
}

public static void main(String[] args) {
try {
Class.forName ( "com.pointbase.jdbc.jdbcUniversalDriver" );
} catch (Exception e) {
System.out.println("ERROR: failed to load pointbase JDBC
driver.");
e.printStackTrace();
return;
}

try {
Connection c =
DriverManager.getConnection(DBCONNECTION,USERNAME,PASSWORD);
Statement query = c.createStatement();
ResultSet result = query.executeQuery(SELECTQUERY);

System.out.println("------- Content of the Contact
Table: ------------\n");
while(result.next()) {

System.out.println("\t" + result.getString("NAME") +
"\t" + result.getString("STREET") +
"\t" + result.getString("CITY"));
}
}
catch(SQLException e) {
System.err.println("SQLException caught!");
e.printStackTrace();
}
}



public ResultSet getContact (String name) {

ResultSet rs = null;

try {
Connection c =
DriverManager.getConnection(DBCONNECTION,USERNAME,PASSWORD);
PreparedStatement ps = c.prepareStatement(SELECTNAME);
ps.setString(1,name);
rs = ps.executeQuery();
} catch(SQLException e) {
System.err.println("SQLException caught!");
e.printStackTrace();
}
return rs;
}

public int setContact (String name, String street, String city, String
state, int zipcode, String phone) {
int rowcount = 0;

try {
Connection c =
DriverManager.getConnection(DBCONNECTION,USERNAME,PASSWORD);
PreparedStatement ps = c.prepareStatement(INSERTNAME);
ps.setString(1,name); ps.setString(2,street);
ps.setString(3,city); ps.setString(4,state);
ps.setInt(5,zipcode); ps.setString(6,phone);rowcount
= ps.executeUpdate();
} catch(SQLException e) {
System.err.println("SQLException caught!");
e.printStackTrace();
}
return rowcount;
}

}

-----------------------------------------------------------------

and now the test script...


import java.sql.*;

public class daoTest {


public static void main(String[] args) {
int rowcount = 0;
ResultSet rs = null;

try {
contactDAO cd = new contactDAO();
cd.setContact("RALPH","123 My
Street","Reston","Virginia",20194,"(666)666-6666");
cd.getContact("RALPH");
/* while(rs.next()) {
System.out.println("\t" + rs.getString("NAME") +
"\t" + rs.getString("STREET") +
"\t" + rs.getString("CITY"));
}*/
} catch (Exception e) {
System.out.println("ERROR: failed to load pointbase JDBC
driver.");
e.printStackTrace();
return;
}

}
}
 
K

Kevin McMurtrie

INSERT INTO CONTACT (?, ?, ?, ?, ?,?)

Most DBs need the VALUES identifier to indicate that you'll be
specifying values in the SQL, since there are multiples ways to provide
the input to an INSERT statement.

It's also very dangerous to insert data without specifying the columns.
You could trash the DB if the tables are altered and the code isn't
modified to match.


Ryan said:
I am having trouble getting my prepared statements to work.

I have a DAO and a test class to call it.

I think this means i'm passing a null parameter, but I checked with
system.out.println. I dont have any nulls?

SQLException caught!
java.sql.SQLException: Expected to find "an identifier" instead found "?" at
pos
ition 21.
at com.pointbase.net.netJDBCPrimitives.handleResponse(Unknown Source)
at com.pointbase.net.netJDBCPrimitives.handleJDBCObjectResponse(Unknown
Source)
at com.pointbase.net.netJDBCConnection.prepareStatement(Unknown Source)
at contactDAO.setContact(contactDAO.java:81)
at daoTest.main(daoTest.java:14)
Press any key to continue . . .



import java.sql.*;

public class contactDAO {

private static String SELECTQUERY = "select * from contact";
private String SELECTNAME = "SELECT * FROM CONTACT WHERE NAME = ?";
private String INSERTNAME = "INSERT INTO CONTACT (?, ?, ?, ?, ?,?)";
private static String DBCONNECTION =
"jdbc:pointbase:server://localhost:9092/contacts";
private static String USERNAME = "CONTACT";
private static String PASSWORD = "CONTACT";
public contactDAO () {
try {
Class.forName ( "com.pointbase.jdbc.jdbcUniversalDriver" );
} catch (Exception e) {
System.out.println("ERROR: failed to load pointbase JDBC
driver.");
e.printStackTrace();
return;
}
}

public static void main(String[] args) {
try {
Class.forName ( "com.pointbase.jdbc.jdbcUniversalDriver" );
} catch (Exception e) {
System.out.println("ERROR: failed to load pointbase JDBC
driver.");
e.printStackTrace();
return;
}

try {
Connection c =
DriverManager.getConnection(DBCONNECTION,USERNAME,PASSWORD);
Statement query = c.createStatement();
ResultSet result = query.executeQuery(SELECTQUERY);

System.out.println("------- Content of the Contact
Table: ------------\n");
while(result.next()) {

System.out.println("\t" + result.getString("NAME") +
"\t" + result.getString("STREET") +
"\t" + result.getString("CITY"));
}
}
catch(SQLException e) {
System.err.println("SQLException caught!");
e.printStackTrace();
}
}



public ResultSet getContact (String name) {

ResultSet rs = null;

try {
Connection c =
DriverManager.getConnection(DBCONNECTION,USERNAME,PASSWORD);
PreparedStatement ps = c.prepareStatement(SELECTNAME);
ps.setString(1,name);
rs = ps.executeQuery();
} catch(SQLException e) {
System.err.println("SQLException caught!");
e.printStackTrace();
}
return rs;
}

public int setContact (String name, String street, String city, String
state, int zipcode, String phone) {
int rowcount = 0;

try {
Connection c =
DriverManager.getConnection(DBCONNECTION,USERNAME,PASSWORD);
PreparedStatement ps = c.prepareStatement(INSERTNAME);
ps.setString(1,name); ps.setString(2,street);
ps.setString(3,city); ps.setString(4,state);
ps.setInt(5,zipcode); ps.setString(6,phone);rowcount
= ps.executeUpdate();
} catch(SQLException e) {
System.err.println("SQLException caught!");
e.printStackTrace();
}
return rowcount;
}

}

-----------------------------------------------------------------

and now the test script...


import java.sql.*;

public class daoTest {


public static void main(String[] args) {
int rowcount = 0;
ResultSet rs = null;

try {
contactDAO cd = new contactDAO();
cd.setContact("RALPH","123 My
Street","Reston","Virginia",20194,"(666)666-6666");
cd.getContact("RALPH");
/* while(rs.next()) {
System.out.println("\t" + rs.getString("NAME") +
"\t" + rs.getString("STREET") +
"\t" + rs.getString("CITY"));
}*/
} catch (Exception e) {
System.out.println("ERROR: failed to load pointbase JDBC
driver.");
e.printStackTrace();
return;
}

}
}
Click to expand...
 
R

Rhino

Ryan said:
I am having trouble getting my prepared statements to work.

I have a DAO and a test class to call it.

I think this means i'm passing a null parameter, but I checked with
system.out.println. I dont have any nulls?

SQLException caught!
java.sql.SQLException: Expected to find "an identifier" instead found "?" at
pos
ition 21.
at com.pointbase.net.netJDBCPrimitives.handleResponse(Unknown Source)
at com.pointbase.net.netJDBCPrimitives.handleJDBCObjectResponse(Unknown
Source)
at com.pointbase.net.netJDBCConnection.prepareStatement(Unknown Source)
at contactDAO.setContact(contactDAO.java:81)
at daoTest.main(daoTest.java:14)
Press any key to continue . . .



import java.sql.*;

public class contactDAO {

private static String SELECTQUERY = "select * from contact";
private String SELECTNAME = "SELECT * FROM CONTACT WHERE NAME = ?";
private String INSERTNAME = "INSERT INTO CONTACT (?, ?, ?, ?, ?,?)";
private static String DBCONNECTION =
"jdbc:pointbase:server://localhost:9092/contacts";
private static String USERNAME = "CONTACT";
private static String PASSWORD = "CONTACT";
public contactDAO () {
try {
Class.forName ( "com.pointbase.jdbc.jdbcUniversalDriver" );
} catch (Exception e) {
System.out.println("ERROR: failed to load pointbase JDBC
driver.");
e.printStackTrace();
return;
}
}

public static void main(String[] args) {
try {
Class.forName ( "com.pointbase.jdbc.jdbcUniversalDriver" );
} catch (Exception e) {
System.out.println("ERROR: failed to load pointbase JDBC
driver.");
e.printStackTrace();
return;
}

try {
Connection c =
DriverManager.getConnection(DBCONNECTION,USERNAME,PASSWORD);
Statement query = c.createStatement();
ResultSet result = query.executeQuery(SELECTQUERY);

System.out.println("------- Content of the Contact
Table: ------------\n");
while(result.next()) {

System.out.println("\t" + result.getString("NAME") +
"\t" + result.getString("STREET") +
"\t" + result.getString("CITY"));
}
}
catch(SQLException e) {
System.err.println("SQLException caught!");
e.printStackTrace();
}
}



public ResultSet getContact (String name) {

ResultSet rs = null;

try {
Connection c =
DriverManager.getConnection(DBCONNECTION,USERNAME,PASSWORD);
PreparedStatement ps = c.prepareStatement(SELECTNAME);
ps.setString(1,name);
rs = ps.executeQuery();
} catch(SQLException e) {
System.err.println("SQLException caught!");
e.printStackTrace();
}
return rs;
}

public int setContact (String name, String street, String city, String
state, int zipcode, String phone) {
int rowcount = 0;

try {
Connection c =
DriverManager.getConnection(DBCONNECTION,USERNAME,PASSWORD);
PreparedStatement ps = c.prepareStatement(INSERTNAME);
ps.setString(1,name); ps.setString(2,street);
ps.setString(3,city); ps.setString(4,state);
ps.setInt(5,zipcode); ps.setString(6,phone);rowcount
= ps.executeUpdate();
} catch(SQLException e) {
System.err.println("SQLException caught!");
e.printStackTrace();
}
return rowcount;
}

}

-----------------------------------------------------------------

and now the test script...


import java.sql.*;

public class daoTest {


public static void main(String[] args) {
int rowcount = 0;
ResultSet rs = null;

try {
contactDAO cd = new contactDAO();
cd.setContact("RALPH","123 My
Street","Reston","Virginia",20194,"(666)666-6666");
cd.getContact("RALPH");
/* while(rs.next()) {
System.out.println("\t" + rs.getString("NAME") +
"\t" + rs.getString("STREET") +
"\t" + rs.getString("CITY"));
}*/
} catch (Exception e) {
System.out.println("ERROR: failed to load pointbase JDBC
driver.");
e.printStackTrace();
return;
}

}
}
Click to expand...

Well, you haven't included any line numbers in your note so I can't be sure
which statement is throwing the exception. But I strongly suspect that it's
your INSERT statement:
private String INSERTNAME = "INSERT INTO CONTACT (?, ?, ?, ?,
Click to expand...
?,?)";

You need to have either a column-name list or the keyword VALUES or both
between the table name and the list of parameter markers (question marks).
For example:

private String INSERTNAME = "INSERT INTO CONTACT (col1, col2, col3,
col4, col5, col6) VALUES (?, ?, ?, ?, ?, ?)";

Naturally, you need to replace 'col1, col2' etc. with the real column names
from your table ;-)

Rhino
 
R

Rhino

Rhino said:
Ryan said:
I am having trouble getting my prepared statements to work.

I have a DAO and a test class to call it.

I think this means i'm passing a null parameter, but I checked with
system.out.println. I dont have any nulls?

SQLException caught!
java.sql.SQLException: Expected to find "an identifier" instead found
Click to expand...
"?"
at
pos
ition 21.
at com.pointbase.net.netJDBCPrimitives.handleResponse(Unknown Source)
at com.pointbase.net.netJDBCPrimitives.handleJDBCObjectResponse(Unknown
Source)
at com.pointbase.net.netJDBCConnection.prepareStatement(Unknown Source)
at contactDAO.setContact(contactDAO.java:81)
at daoTest.main(daoTest.java:14)
Press any key to continue . . .



import java.sql.*;

public class contactDAO {

private static String SELECTQUERY = "select * from contact";
private String SELECTNAME = "SELECT * FROM CONTACT WHERE NAME = ?";
private String INSERTNAME = "INSERT INTO CONTACT (?, ?, ?, ?, ?,?)";
private static String DBCONNECTION =
"jdbc:pointbase:server://localhost:9092/contacts";
private static String USERNAME = "CONTACT";
private static String PASSWORD = "CONTACT";
public contactDAO () {
try {
Class.forName ( "com.pointbase.jdbc.jdbcUniversalDriver" );
} catch (Exception e) {
System.out.println("ERROR: failed to load pointbase JDBC
driver.");
e.printStackTrace();
return;
}
}

public static void main(String[] args) {
try {
Class.forName ( "com.pointbase.jdbc.jdbcUniversalDriver" );
} catch (Exception e) {
System.out.println("ERROR: failed to load pointbase JDBC
driver.");
e.printStackTrace();
return;
}

try {
Connection c =
DriverManager.getConnection(DBCONNECTION,USERNAME,PASSWORD);
Statement query = c.createStatement();
ResultSet result = query.executeQuery(SELECTQUERY);

System.out.println("------- Content of the Contact
Table: ------------\n");
while(result.next()) {

System.out.println("\t" + result.getString("NAME") +
"\t" + result.getString("STREET") +
"\t" + result.getString("CITY"));
}
}
catch(SQLException e) {
System.err.println("SQLException caught!");
e.printStackTrace();
}
}



public ResultSet getContact (String name) {

ResultSet rs = null;

try {
Connection c =
DriverManager.getConnection(DBCONNECTION,USERNAME,PASSWORD);
PreparedStatement ps = c.prepareStatement(SELECTNAME);
ps.setString(1,name);
rs = ps.executeQuery();
} catch(SQLException e) {
System.err.println("SQLException caught!");
e.printStackTrace();
}
return rs;
}

public int setContact (String name, String street, String city, String
state, int zipcode, String phone) {
int rowcount = 0;

try {
Connection c =
DriverManager.getConnection(DBCONNECTION,USERNAME,PASSWORD);
PreparedStatement ps = c.prepareStatement(INSERTNAME);
ps.setString(1,name); ps.setString(2,street);
ps.setString(3,city); ps.setString(4,state);
ps.setInt(5,zipcode); ps.setString(6,phone);rowcount
= ps.executeUpdate();
} catch(SQLException e) {
System.err.println("SQLException caught!");
e.printStackTrace();
}
return rowcount;
}

}

-----------------------------------------------------------------

and now the test script...


import java.sql.*;

public class daoTest {


public static void main(String[] args) {
int rowcount = 0;
ResultSet rs = null;

try {
contactDAO cd = new contactDAO();
cd.setContact("RALPH","123 My
Street","Reston","Virginia",20194,"(666)666-6666");
cd.getContact("RALPH");
/* while(rs.next()) {
System.out.println("\t" + rs.getString("NAME") +
"\t" + rs.getString("STREET") +
"\t" + rs.getString("CITY"));
}*/
} catch (Exception e) {
System.out.println("ERROR: failed to load pointbase JDBC
driver.");
e.printStackTrace();
return;
}

}
}
Click to expand...

Well, you haven't included any line numbers in your note so I can't be sure
which statement is throwing the exception. But I strongly suspect that it's
your INSERT statement:
private String INSERTNAME = "INSERT INTO CONTACT (?, ?, ?, ?,
Click to expand...
?,?)";

You need to have either a column-name list or the keyword VALUES or both
between the table name and the list of parameter markers (question marks).
For example:

private String INSERTNAME = "INSERT INTO CONTACT (col1, col2, col3,
col4, col5, col6) VALUES (?, ?, ?, ?, ?, ?)";

Naturally, you need to replace 'col1, col2' etc. with the real column names
from your table ;-)

Rhino
Click to expand...
 
R

Rhino

Rhino said:
Ryan said:
I am having trouble getting my prepared statements to work.

I have a DAO and a test class to call it.

I think this means i'm passing a null parameter, but I checked with
system.out.println. I dont have any nulls?

SQLException caught!
java.sql.SQLException: Expected to find "an identifier" instead found
Click to expand...
"?"
at
pos
ition 21.
at com.pointbase.net.netJDBCPrimitives.handleResponse(Unknown Source)
at com.pointbase.net.netJDBCPrimitives.handleJDBCObjectResponse(Unknown
Source)
at com.pointbase.net.netJDBCConnection.prepareStatement(Unknown Source)
at contactDAO.setContact(contactDAO.java:81)
at daoTest.main(daoTest.java:14)
Press any key to continue . . .



import java.sql.*;

public class contactDAO {

private static String SELECTQUERY = "select * from contact";
private String SELECTNAME = "SELECT * FROM CONTACT WHERE NAME = ?";
private String INSERTNAME = "INSERT INTO CONTACT (?, ?, ?, ?, ?,?)";
private static String DBCONNECTION =
"jdbc:pointbase:server://localhost:9092/contacts";
private static String USERNAME = "CONTACT";
private static String PASSWORD = "CONTACT";
public contactDAO () {
try {
Class.forName ( "com.pointbase.jdbc.jdbcUniversalDriver" );
} catch (Exception e) {
System.out.println("ERROR: failed to load pointbase JDBC
driver.");
e.printStackTrace();
return;
}
}

public static void main(String[] args) {
try {
Class.forName ( "com.pointbase.jdbc.jdbcUniversalDriver" );
} catch (Exception e) {
System.out.println("ERROR: failed to load pointbase JDBC
driver.");
e.printStackTrace();
return;
}

try {
Connection c =
DriverManager.getConnection(DBCONNECTION,USERNAME,PASSWORD);
Statement query = c.createStatement();
ResultSet result = query.executeQuery(SELECTQUERY);

System.out.println("------- Content of the Contact
Table: ------------\n");
while(result.next()) {

System.out.println("\t" + result.getString("NAME") +
"\t" + result.getString("STREET") +
"\t" + result.getString("CITY"));
}
}
catch(SQLException e) {
System.err.println("SQLException caught!");
e.printStackTrace();
}
}



public ResultSet getContact (String name) {

ResultSet rs = null;

try {
Connection c =
DriverManager.getConnection(DBCONNECTION,USERNAME,PASSWORD);
PreparedStatement ps = c.prepareStatement(SELECTNAME);
ps.setString(1,name);
rs = ps.executeQuery();
} catch(SQLException e) {
System.err.println("SQLException caught!");
e.printStackTrace();
}
return rs;
}

public int setContact (String name, String street, String city, String
state, int zipcode, String phone) {
int rowcount = 0;

try {
Connection c =
DriverManager.getConnection(DBCONNECTION,USERNAME,PASSWORD);
PreparedStatement ps = c.prepareStatement(INSERTNAME);
ps.setString(1,name); ps.setString(2,street);
ps.setString(3,city); ps.setString(4,state);
ps.setInt(5,zipcode); ps.setString(6,phone);rowcount
= ps.executeUpdate();
} catch(SQLException e) {
System.err.println("SQLException caught!");
e.printStackTrace();
}
return rowcount;
}

}

-----------------------------------------------------------------

and now the test script...


import java.sql.*;

public class daoTest {


public static void main(String[] args) {
int rowcount = 0;
ResultSet rs = null;

try {
contactDAO cd = new contactDAO();
cd.setContact("RALPH","123 My
Street","Reston","Virginia",20194,"(666)666-6666");
cd.getContact("RALPH");
/* while(rs.next()) {
System.out.println("\t" + rs.getString("NAME") +
"\t" + rs.getString("STREET") +
"\t" + rs.getString("CITY"));
}*/
} catch (Exception e) {
System.out.println("ERROR: failed to load pointbase JDBC
driver.");
e.printStackTrace();
return;
}

}
}
Click to expand...

Well, you haven't included any line numbers in your note so I can't be sure
which statement is throwing the exception. But I strongly suspect that it's
your INSERT statement:
private String INSERTNAME = "INSERT INTO CONTACT (?, ?, ?, ?,
Click to expand...
?,?)";

You need to have either a column-name list or the keyword VALUES or both
between the table name and the list of parameter markers (question marks).
For example:

private String INSERTNAME = "INSERT INTO CONTACT (col1, col2, col3,
col4, col5, col6) VALUES (?, ?, ?, ?, ?, ?)";

Naturally, you need to replace 'col1, col2' etc. with the real column names
from your table ;-)
Click to expand...
OOPS!!

What I SHOULD have said was: "You need a VALUES keyword and, OPTIONALLY, may
have a column list between the table name and the list of parameter markers.
If provided, the column list needs to precede the VALUES keyword." Contrary
to what I said, you CANNOT have the column list WITHOUT the VALUES keyword,
at least not in any dialect of SQL I've ever seen.

Therefore this would work:

private String INSERTNAME = "INSERT INTO CONTACT (col1, col2, col3,
col4, col5, col6) VALUES (?, ?, ?, ?, ?, ?)";

and so would this:

private String INSERTNAME = "INSERT INTO CONTACT VALUES (?, ?, ?, ?, ?,
?)";

but NOT this:

private String INSERTNAME = "INSERT INTO CONTACT (col1, col2, col3,
col4, col5, col6) (?, ?, ?, ?, ?, ?)";

because the VALUES keyword *must* be there. Only the column name list is
mandatory. But, as Kevin said, it's a darned good idea to have the column
list even if it is optional.

Rhino
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Closed Statement issue 7
Why JavaBean load jdbc driver failed? 3
Need help with JDBC code walk 3
Connected SQLite to my java program but information are not submitted 2
JDBC: Problem with execution of SQL Statement 3
problem in inserting record in ms access. 5
JDBC connection Issues 14
Error with server 3

Members online

Total: 318 (members: 1, guests: 317)
Robots: 298

Forum statistics

Threads
473,968
Messages
2,570,149
Members
46,695
Latest member
StanleyDri

Latest Threads

Top