• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • Liutauras Vilda
  • Jeanne Boyarsky
  • paul wheaton
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Henry Wong
Saloon Keepers:
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Tim Moores
  • Mikalai Zaikin
Bartenders:
  • Frits Walraven

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

 
Bartender
Posts: 1973
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Bartender
Posts: 1381
39
IBM DB2 Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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..
 
Sheriff
Posts: 28326
96
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Bartender
Posts: 1973
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Bartender
Posts: 1381
39
IBM DB2 Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Bartender
Posts: 1973
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Bartender
Posts: 1973
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Bartender
Posts: 1381
39
IBM DB2 Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Bartender
Posts: 1381
39
IBM DB2 Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Would it be a problem posting the fetch - and - insert cycle code snippet?
 
Mike London
Bartender
Posts: 1973
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Bartender
Posts: 1973
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Bartender
Posts: 1381
39
IBM DB2 Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
OK, sorry for asking that in this case. Let us know, the whole thing is intriguing
 
Mike London
Bartender
Posts: 1973
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
 
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Bartender
Posts: 1973
17
  • Likes 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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:



It's all done now.

Thanks for all the terrific replies!!!

- mike
 
Claude Moore
Bartender
Posts: 1381
39
IBM DB2 Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Glad you solved your issue... and thanks for letting us know.
 
Roel De Nijs
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Bartender
Posts: 1973
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Bartender
Posts: 1381
39
IBM DB2 Netbeans IDE Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Bartender
Posts: 1973
17
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Bartender
Posts: 1973
17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
 
Bartender
Posts: 598
26
Oracle Notepad Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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:



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).
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
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
 
Thanks tiny ad, for helping me escape the terrible comfort of this chair.
Gift giving made easy with the permaculture playing cards
https://coderanch.com/t/777758/Gift-giving-easy-permaculture-playing
reply
    Bookmark Topic Watch Topic
  • New Topic