Strange JDBC Error: The number of values in a VALUES row value constructor does not match the number

[Mike London]

I have a PreparedStatement INSERT statement built up programmatically with a "?" for each field.

There are 83 '?' marks in the INSERT statement below.

INSERT INTO <TABLE_NAME> Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

Additionally, from the ResultSet meta data ---> rs.getMetaData().getColumnCount() ---> also returns 83.

I can open the code in the debugger and look at the result set to verify 83 fields (0-82) for each rs row.

--

Yet, when preparing the statement above:

connection.prepareStatement(stringBuilder.toString());

(stringBuilder is just an object with the 83 "?" marks)

I get this:

The number of values in a VALUES row value constructor does not match the number of values in the target.

I'm not sure what this means since I haven't yet created fields in the target DB. That's part of the job of this code - which worked fine on two other tables already. (confusing)

Would appreciate some advise on what might be wrong and what I should look at.

Thanks,

mike

[Claude Moore]

May you post an excerpt of you code, with more details? Generally speaking the error you encountered means that a parameter in the statement hasn't been set. If you have X '?' placeholders, you need to set X values as well. What sounds strange to me is that you are talking about a ResultSet:where did you obtained this from? by executing the statement? I don't think so, because if the statement is not properly prepared -I.e you missed to set any params - you should not be able to get any ResultSet..

[Paul Clapham]

Mike London wrote:I'm not sure what this means since I haven't yet created fields in the target DB. That's part of the job of this code - which worked fine on two other tables already. (confusing)

I found that confusing too. If I read it straight it suggests that you expect that preparing an INSERT statement will cause a CREATE TABLE or ALTER TABLE statement to be run, thereby creating columns in the table. But that doesn't make sense to me because the INSERT statement doesn't provide column names and column types. And like Claude I was confused by the reference to ResultSet, especially if it was supposed to be getting data from a table which didn't have columns declared yet.

But maybe "this code" refers to a larger piece of code, from which you only posted one line. And maybe something else in that larger piece of code was where the table and its columns were being declared. And maybe it didn't do what you expected it to do.

Or maybe "I haven't yet created fields" just means "I haven't added any rows" and I'm following a red herring?

[Mike London]

Claude Moore wrote:May you post an excerpt of you code, with more details? Generally speaking the error you encountered means that a parameter in the statement hasn't been set. If you have X '?' placeholders, you need to set X values as well. What sounds strange to me is that you are talking about a ResultSet:where did you obtained this from? by executing the statement? I don't think so, because if the statement is not properly prepared -I.e you missed to set any params - you should not be able to get any ResultSet..

Hi Claude,

Thanks for your reply.

The error happens on the actual prepareStatement() call.

Maybe I need to count the "?" by hand. I used TextWrangler and did a "Find All" for "?".

Thanks,

mike

[Claude Moore]

How many columns has your table? Try with INSERT INTO TABLENAME (COLUMN1, COLUMN2,...,COLUMNx) VALUES (?,?,...,?) syntax. If I am not wrong, the number of columns inserted must match values specified... Not sure of this, though.

[Mike London]

Claude Moore wrote:How many columns has your table? Try with INSERT INTO TABLENAME (COLUMN1, COLUMN2,...,COLUMNx) VALUES (?,?,...,?) syntax. If I am not wrong, the number of columns inserted must match values specified... Not sure of this, though.

Nope, in a SQL INSERT, you don't have to list all the field names. Just the VALUES(....) is fine as long as the rest of the syntax is OK.

- mike

[Mike London]

Paul Clapham wrote:

Mike London wrote:I'm not sure what this means since I haven't yet created fields in the target DB. That's part of the job of this code - which worked fine on two other tables already. (confusing)

I found that confusing too. If I read it straight it suggests that you expect that preparing an INSERT statement will cause a CREATE TABLE or ALTER TABLE statement to be run, thereby creating columns in the table. But that doesn't make sense to me because the INSERT statement doesn't provide column names and column types. And like Claude I was confused by the reference to ResultSet, especially if it was supposed to be getting data from a table which didn't have columns declared yet.

But maybe "this code" refers to a larger piece of code, from which you only posted one line. And maybe something else in that larger piece of code was where the table and its columns were being declared. And maybe it didn't do what you expected it to do.

Or maybe "I haven't yet created fields" just means "I haven't added any rows" and I'm following a red herring?

Hey Paul,

The error happens on the prepareStatment() line of code.

All the other stuff I included were ancillary evidence that 83 was the right field count.

And, yes, I omitted a key piece of information (sorry about that), that what I'm doing is:

Transfer table data FROM: DB1 --- TO: DB2

I get all the data from a table in DB1 into a ResultSet (that's where I see that I have 83 fields in each row). Then from the column count from DB1 (source), I create a PreparedStatement for DB2 (dest) that I can then loop through that DB1 result set, set values in the prepared statement, and insert the data into DB2 using the connection for DB2.

I successfully used the same data transfer method from two tables from DB1 (the source table) already so this is confusing that another table isn't giving me the column count correctly or something.

Hopefully this isn't a bug in the JDBC driver itself.

Sorry for the lack of clarity in my initial posting.

--mike

[Claude Moore]

Are you sure that the source and destination tables are equivalent, with the very same columns, column types and so on? About INSERT INTO TABLE, I checked the syntax and you have to specify columns names if you are inserting only a subset of columns. For example, if you have a table with 3 columns you cannot run a INSERT INTO MYTABLE VALUES (?,?), so I would check that second table has the very same column number.

[Claude Moore]

Would it be a problem posting the fetch - and - insert cycle code snippet?

[Mike London]

Claude Moore wrote:Are you sure that the source and destination tables are equivalent, with the very same columns, column types and so on? About INSERT INTO TABLE, I checked the syntax and you have to specify columns names if you are inserting only a subset of columns. For example, if you have a table with 3 columns you cannot run a INSERT INTO MYTABLE VALUES (?,?), so I would check that second table has the very same column number.

Actually the code creates the fields in the destination via ALTER TABLE commands.

The same code has already worked with two tables and I'm getting the column count from the resultset metadata from the source so I know how many fields (83) I'll need to create in the destination.

It's probably not a bad idea, though, to specify the field names as well as the "?".

I'll try that too and post back.

Thanks for that idea.

- mike

[Mike London]

Claude Moore wrote:Would it be a problem posting the fetch - and - insert cycle code snippet?

Yes, proprietary but I'm going to try to specify the field names and see if that helps.

Thanks,

-mike

[Claude Moore]

OK, sorry for asking that in this case. Let us know, the whole thing is intriguing

[Mike London]

Claude Moore wrote:OK, sorry for asking that in this case. Let us know, the whole thing is intriguing

No worries. I'll modify the code that creates the INSERT statement tomorrow when I'm fresh and post back.

Appreciate your idea on this.

-- mike

[Roel De Nijs]

Mike London wrote:I'll modify the code that creates the INSERT statement tomorrow when I'm fresh and post back.

If for some reason the ALTER TABLE statement for one of the columns failed, you'll probably get another runtime exception when trying to create the prepared statement: "unknown column name" (or something similar).

[Mike London]

Roel De Nijs wrote:

Mike London wrote:I'll modify the code that creates the INSERT statement tomorrow when I'm fresh and post back.

If for some reason the ALTER TABLE statement for one of the columns failed, you'll probably get another runtime exception when trying to create the prepared statement: "unknown column name" (or something similar).

I forgot to uncomment the line of code that calls the method to create fields! Argh!!!

That, combined with a minor issue with JDBC field mapping was the extent of my issues.

The destination database on alter table insert coluumn forces TimeStamps to be Always Validated. Since that destination DB doesn't accept standard dates (2016-11-01 11:11:05), I had to write a quick data formatter:

format = DateTimeFormatter.ofPattern("MM/dd/yyyy H:mm:ss");

It's all done now.

Thanks for all the terrific replies!!!

- mike

[Claude Moore]

Glad you solved your issue... and thanks for letting us know.

[Roel De Nijs]

Mike London wrote:It's all done now.

Glad to hear you solved your issue! And thanks for coming back and sharing your solution. That might be helpful for other ranchers as well. Have a cow!

[Mike London]

Roel De Nijs wrote:

Mike London wrote:It's all done now.

Glad to hear you solved your issue! And thanks for coming back and sharing your solution. That might be helpful for other ranchers as well. Have a cow!

That's cool. Thanks. Moo.

BTW, what do the cows mean?

If I get 10 cows is the 11'th one free? ;)

Thanks again for all the great support on JR!

- mike

[Claude Moore]

More or less, a cow is a very very nice way to tell you that you posted something useful or interesting here at the ranch. Only Ranchers and higher rank members may award cows. For more information, have a look at the FAQ or wiki section.
By the way, this cow is IMHO fully deserved. I am in doubt that your case may be usefull to others -maybe too much specific case - but it is really a nice behavior to give a feedback after having asked for help !

[Mike London]

Claude Moore wrote:More or less, a cow is a very very nice way to tell you that you posted something useful or interesting here at the ranch. Only Ranchers and higher rank members may award cows. For more information, have a look at the FAQ or wiki section.
By the way, this cow is IMHO fully deserved. I am in doubt that your case may be usefull to others -maybe too much specific case - but it is really a nice behavior to give a feedback after having asked for help !

Interesting. I've been on the ranch since 2002 and only have three cows? I must be getting better!

Yes, I always come back and comment on the resolution since I'm thinking of some programmer out there in some office at "O-Dark-Thirty" trying to figure something out which my final resolution might offer him.

Oh, BTW, ALTER TABLE wasn't the best approach after all. It turns out that the destination database I was using allows you to create a table independently of the fields-very unusual.

In any case, I switched to a CREATE TABLE and that works better and is more cross-platform.

Thanks again for all your replies.

-- mike

[Roel De Nijs]

Mike London wrote:BTW, what do the cows mean?

Have a look at the Ranch Cows wiki to find out more about our cows.

Mike London wrote:If I get 10 cows is the 11'th one free? ;)

No, but if you find a nice grass land for them, you'll can enjoy free milk and meat

[Roel De Nijs]

Mike London wrote:Interesting. I've been on the ranch since 2002 and only have three cows? I must be getting better!

Don't be too hard for yourself! The majority of your time here at CodeRanch was "cowless", the cows were only introduced 3 years ago.

Mike London wrote:Oh, BTW, ALTER TABLE wasn't the best approach after all. It turns out that the destination database I was using allows you to create a table independently of the fields-very unusual.

In any case, I switched to a CREATE TABLE and that works better and is more cross-platform.

Executing one CREATE TABLE statement instead of executing +80 ALTER TABLE statements makes indeed more sense and is a nice improvement Just curious: which (source and) destination database are you using?

[Mike London]

Roel De Nijs wrote:

Mike London wrote:Interesting. I've been on the ranch since 2002 and only have three cows? I must be getting better!

Don't be too hard for yourself! The majority of your time here at CodeRanch was "cowless", the cows were only introduced 3 years ago.

Mike London wrote:Oh, BTW, ALTER TABLE wasn't the best approach after all. It turns out that the destination database I was using allows you to create a table independently of the fields-very unusual.

In any case, I switched to a CREATE TABLE and that works better and is more cross-platform.

Executing one CREATE TABLE statement instead of executing +80 ALTER TABLE statements makes indeed more sense and is a nice improvement Just curious: which (source and) destination database are you using?

Source: Visual FoxPro

Destination: FileMaker Pro.

Not two of your more common databases, but Visual FoxPro is discontinued and no longer supported so getting data off that into something the client can actually use and is still supported is key.

-- mike

[Brian Tkatch]

Mike London wrote:Nope, in a SQL INSERT, you don't have to list all the field names. Just the VALUES(....) is fine as long as the rest of the syntax is OK.

That is true, but it's still a Bad Idea(tm). Delineating the columns protects your code from added or removed columns, and the occasional reordering. And, better yet, it's self-documenting. You should always list the columns.

Mike London wrote:The destination database on alter table insert coluumn forces TimeStamps to be Always Validated. Since that destination DB doesn't accept standard dates (2016-11-01 11:11:05), I had to write a quick data formatter:

format = DateTimeFormatter.ofPattern("MM/dd/yyyy H:mm:ss");

Please do not do that, you may not know it, but it's also a Bad Idea(tm).

Firstly, that isn't a "standard date," since it includes the time. A date is just a date, and is defined by the ANSI standard as a four-digit year, followed by a two-digit month, followed by a two-digit day. The following should work just about everywhere (the space is optional).DATE '2016-02-19'
Secondly, dates, times, and date times, are not stored literally in a database. That would be terribly inefficient. Instead, they use an internal format, not necessarily explained to the user, and a function is applied when setting or retrieving it. If the statement does not provide a function, it is applied implicitly. Think of it like a an object with getters and setters. You should always use a function when setting a date in SQL (or the ANSI format.)

The used function changes by database. In Oracle and DB2, it's TO_DATE() or TO_TIMESTAMP(). In SQL Server it's CONVERT(). In mysql its STR_TO_DATE(). Visual Fox Pro, assuming i got it right, uses TTOD(). FilmMaker Pro it's (i think) GetAsDate() Please use the appropriate function. It will make your code more efficient, and be protected against the unsuspected bug. Further, if there is a problem with the format, it will give you a clear error message.

--
Updated FoxPro and FilmMaker Pro functions. I am guessing after a bit of searching. In Visual Fox Pro, you certainly used to need to parse it yourself, based on old posts.

[Roel De Nijs]

Mike London wrote:Source: Visual FoxPro

Destination: FileMaker Pro.

Not two of your more common databases

That's indeed true! I have never heard about them