important problem with mysql and a service java

Thread view:

CiMaBuE - 28 Nov 2006 11:06 GMT

hi,

I have a problem with an application I'm developing. The issue is I
have a table with the users and one of the fields is the name of a
personal table. In the program I create an object for each user, and
there is a string variable table.

Later in the program I try to read from that table, the second one, in
the database and here I have the problem. Basically, the system is
telling me, my sql grammar is not ok. I have put that sentence in mysql
and it works properly. I think the problem, maybe, is the conversion
from different kind of strings or something like that.

[...]
private static final String BOTE_PARCIAL= "select sum(premio) from ?
";
[...]
con = ds.getConnection();

PreparedStatement st = con.prepareStatement(BOTE_PARCIAL);
st.setString(1, tablaApuestas);
ResultSet rs = st.executeQuery();
// here it fails

And the error is the next:

tabla es: 0607apuestascharlen
error en getBote
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error
in your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near ''0607apuestascharlen''
at line 1
at
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3176)

Please help me!!!!!!! I need to solve this problem

Reply to this Message

Jeff - 28 Nov 2006 11:55 GMT

> hi,
>
[quoted text clipped - 36 lines]
>
> Please help me!!!!!!! I need to solve this problem

I suspect that you cannot use the table as a parameter. I looked at
the MySQL documentation and could not find an example of prepared
statements doing that. You may be better off constructing the statement
string and using a regular Statement rather than using the prepared
statement. Alternative possibilities include that the string you are
feeding the query as a parameter does not exactly match the table name.

Another technique, rather than having "private" tables on your server,
is to have the data go into a single table, but tag it by
user/location. You can get the user and location data (me@localhost) by
con.getMetaData().getUserName() and post that info with the private
data. Then, include a where clause in the select statements to only get
data for that user/location, and delete the data when exiting the
program.

Reply to this Message

David Harper - 28 Nov 2006 21:31 GMT

> hi,
>
[quoted text clipped - 36 lines]
>
> Please help me!!!!!!! I need to solve this problem

First, you cannot specify the name of a table as a "?" parameter in a
prepared statement. MySQL does not allow this, and I think other
database systems also do not allow it.

Second, you should think again about the design of your application.
Having a separate table for every user is a *bad* idea. There will be
performance issues when your application creates thousands of tables.

The correct solution is to give each user a unique ID number using a
column which is declared with the "auto_increment" qualifier, then store
the strings in another table, linked to the user through his ID number.

Your table USER will have columns

userid integer auto_increment primary key
surname varchar(64)
forename varchar(32)
...

and your table STRINGS will have columns

userid integer
string varchar(2048) // or the maximum length you expect
...
index (userid)

and to find all strings which belong to me,

select string from USER left join STRINGS using(userid) where
surname='Harper' and forename='David'

David Harper
Cambridge, England

Reply to this Message

important problem with mysql and a service java

Free Magazines