Problem with Access concatenation

Problem with Access concatenation

Post by Tom » Thu, 24 Jul 2008 14:17:57

I have a query which concatenates a prefix in Access which works fine
in Access, but not from Java. To explain further, I create a query
programmatically which I catch when debugging, and paste it into
Access to make sure it's correct. It works fine in Access, but I get
the "too few parameters" error when executing the query from Java. By
fiddling around to figure out what it didn't like, it became evident
it was the concatenation. Has anyone run across this?

Problem with Access concatenation

Post by GArlingto » Thu, 24 Jul 2008 18:07:26

In my experience the above error would point to driver/settings
Are you trying to say that "string1" + "string2" is !=
"string1string2" in your result?


Problem with Access concatenation

Post by Roedy Gree » Thu, 24 Jul 2008 18:11:18

On Tue, 22 Jul 2008 22:17:57 -0700 (PDT), Tom < XXXX@XXXXX.COM >
wrote, quoted or indirectly quoted someone who said :

Normally, soon after you start using your database with Java you
convert it to a true SQL engine and drop Access. There are plenty of
free ones. see

Roedy Green Canadian Mind Products
The Java Glossary

Problem with Access concatenation

Post by Sabine Din » Thu, 24 Jul 2008 20:58:13

I think he's saying that string concatenation to create SQL qery strings
is prone to human error.


final String query = "select some" +
"from table "
" where some=" + value + "and forget some spaces inbetween"; [1]


[1] not valid Java nor SQL
Sabine Dinis Blochberger


Problem with Access concatenation

Post by David Sega » Thu, 24 Jul 2008 21:40:33

"Too few parameters" indicates that you have a pre written JDBC query
that contains question marks. It is equivalent to an Access parameter
query. I suspect that you have assembled a complete SQL statement
instead of providing the required individual parameters but it would
be easier to debug if you post the code. An SSCCE
< ; would be ideal.

Problem with Access concatenation

Post by David Sega » Thu, 24 Jul 2008 22:08:07

Nobody, not even Microsoft, advocates using the Jet Database Engine
that comes with Access. On the other hand, Access is a useful tool for
composing and testing simple SQL queries against almost any database
as described by the OP.

Problem with Access concatenation

Post by Tom » Thu, 24 Jul 2008 23:50:10

I receive the Access db from a BA. At some point when we have time we
may convert the data to something else, but for now, this is what I
have to work with. The query of the view looks like this:

SELECT AllBenefitText.TextShortName AS ShortName, AllBenefitText.Text
AS BenefitText, AllBenefitText.PlanType AS PlanType, "BenefitText - "
& ShortName AS BenefitTextName
FROM AllBenefitText;

The concatenation I'm talking about is in Access (last column). The
query I construct in Java is something like "SELECT BenefitTextName
FROM AllBenefitText." This query works fine within Access itself, but
when running it through JDBC I get the ubiquitous "too few parameters"
error message. I was wondering if anyone had run accross this and
knew of a solution (other than "don't use Access").

Problem with Access concatenation

Post by Arne Vaj » Fri, 25 Jul 2008 08:39:47

If you posted the actual SQL, then you may get an explanation.

There can easily be differences, because the SQL inside Access
is executed differently than SQL going through the ODBC driver.