Skip to main content
gamedev.net
🔒 Locked

SQL query not correct: why?

Started by cignox1 Oct 16, 2005 at 4:42 PM 11 replies 1.3k views
Original Post
cignox1
cignox1
Hi all, I'm trying to add a table in a database using the following query: 

create table `users` ( `ID` BIGINT(20) primary key, `User_Name` VARCHAR(255), `Gender` VARCHAR(255), `Age` INTEGER(11), `Exam` VARCHAR(255), `Level` VARCHAR(255), `Mothertongue` VARCHAR(255), `German_Skills` VARCHAR(255), `Italian_Skills` VARCHAR(255), `Computer_Skills` VARCHAR(255), `Additional Materials` VARCHAR(255), `General_Q1` VARCHAR(255), `General_Q2` VARCHAR(255), `General_Q3` VARCHAR(255), `General_Q4` VARCHAR(255), `General_Q5` VARCHAR(255), `MELDIT_Q1` VARCHAR(255), `MELDIT_Q2` VARCHAR(255), `MELDIT_Q3` VARCHAR(255), `MELDIT_Q4` VARCHAR(255), `MELDIT_Q5` VARCHAR(255), `MELDIT_Q6-Home` VARCHAR(255), `Office` VARCHAR(255), `Road` VARCHAR(255), `Train` VARCHAR(255), `Park` VARCHAR(255), `Other` VARCHAR(255), `Preffered_Place` VARCHAR(255), `Why?` VARCHAR(255), `Supplement` VARCHAR(255), `Why` VARCHAR(255), `Other_Material-Dictionary` VARCHAR(255), `Study_Book` VARCHAR(255), `Paper_Notes` VARCHAR(255), `Other1` VARCHAR(255), `Disadvantage-Screen` VARCHAR(255), `Navigation` VARCHAR(255), `Missing_Data` VARCHAR(255), `Slow` VARCHAR(255), `Other2` VARCHAR(255) )
but it results in the following error: Error while building a new table: No value specified for parameter 41 the fields are only 40 and with other queries it works, so it is a problem of this very one. I'm using a mysql database, java and the latest version of mysql connector drivers. Do some of you understand why it does not work?
Kippesoep
Kippesoep
Weird. I just copied it directly into MySQL and it created the table just fine. What version of MySQL are you using? Have you tried removing a few columns?
Kippesoep
cignox1
cignox1
mmm, you're right. Sorry (here in italy its late night :-(
The error appears when I try to populate the table in the following way:
if(data.first() == false) return false;data.beforeFirst();            Statement stat = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);ResultSet newt = stat.executeQuery("select * from "+name);newt.moveToInsertRow();while(data.next()){                        for(int i = 1; i <= meta.getColumnCount(); i++) newt.updateObject(i, data.getObject(i));    newt.insertRow();  }

I get the data from a table (the var 'data') and after having built a copy of the table in the new database (the var 'newt') I get all the rows of the src table and put them in the new. With this table that doesn't work.
Kippesoep
Kippesoep
It would seem like the table somehow got created with 41 columns instead of 40, which causes the INSERT to fail. Try going into MySQL itself and do a "DESC users;" to see if there's anything odd.
Kippesoep
cignox1
cignox1
I executed desc users in SQLyog, but everything's fine (that is, it reported 40 rows).
Could be the fact that Additional Materials field is composed by 2 separated words? Can I change the name of a field?
Kippesoep
Kippesoep
That was my guess, too. You could use the "ALTER TABLE" command to change it:
ALTER TABLE users CHANGE `Additional Materials` Additional_Materials VARCHAR(255);
Kippesoep
cignox1
cignox1
I've done it but my problem is there. The table is created but no rows are inserted because I 'didn't specify the parameter 41'.
Kippesoep
Kippesoep
Extremely weird. Try adding a 41th parameter and seeing if the insert succeeds (and if it does, where the value ends up).
Kippesoep
Kippesoep
Kippesoep
This is getting stranger by the minute. Are you sure meta.getColumnCount actually return 40? Also: I haven't worked with the MySQL JDBC connector myself, but are you sure updateObject and getObject are 1-based? (i.e. that you shouldn't use this instead:)
for(int i = 0; i < meta.getColumnCount(); i++)


That is a far more conventional idiom.
Kippesoep
cignox1
cignox1
Quote:
Original post by Kippesoep
This is getting stranger by the minute. Are you sure meta.getColumnCount actually return 40?

Yes, i print its value on the screen just before the code I posted.

Also: I haven't worked with the MySQL JDBC connector myself, but are you sure updateObject and getObject are 1-based? (i.e. that you shouldn't use this instead:)
for(int i = 0; i < meta.getColumnCount(); i++)


That is a far more conventional idiom.[/quote]
From the java docs:
Quote:

Updates the designated column with an Object value. The updater methods are used to update column values in the current row or the insert row. The updater methods do not update the underlying database; instead the updateRow or insertRow methods are called to update the database.

Parameters:
columnIndex - the first column is 1, the second is 2, ...
x - the new column value

so yes, I'm sure the index start from 1 (pretty uncommon, at least in C++, I'm not so used to Java.

Anyway, this error appears only with this table. For example, with the following command, it works perfectly:
create table `sessions` ( `Session_ID` BIGINT(20) primary key, `User_ID` BIGINT(20), `Start_Date` DATE, `Start_Time` TIME, `Total_Time_Seconds` BIGINT(20), `Number_Texts_Per_Session` BIGINT(20) )

That make me think to a bug in the driver, or something different between the two tables.
Kippesoep
Kippesoep
It pretty much has to be. I've run out of ideas. Sorry I couldn't help you.
Kippesoep
cignox1
cignox1
Thank you anyway for the help. Perhaps I will be able to fix it somehow in the next days.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.

Sign in to reply to this topic.