Java.sql.SQlException - JDBC JAVA

Clive_ wrote:
> I am trying to get an SQL statement to check that the users ID and the
> ID row in a table match. There maybe up to a hundred users using the
> database.
>
> I have two tables in SQl Server express - Users and Property1 both
> with a
> SurveyID column ie Users.SurveyID and Prop1.SurveyID
>
> The SQL statement is:
> String querySQL=("Select * from Prop1_WallFinish where SurveyID =
> \"Users.SurveyID\"" );
>
> I am getting a java.sql.SQlException: Invalid column name.
>
> Could anyone point out what is wrong with the statement???

It looks to me like you have two problems.

First, your select statement is trying to select from a table named
"Prop1_WallFinish" rather than from either of the tables you mention,
Users and Property1. Perhaps you actually mean

select * from Property1 ....

Second, your "where" clause looks fishy. Assuming that you really mean
to select rows from the table named Property1, then your query appears to be

select * from Property1 where SurveyID = "Users.SurveyID"

which is testing an ID column, which is presumably a number, against a
literal string "Users.SurveyID".

Are you trying to match rows in the Property1 table which have the same
SurveyID column as rows in the Users table? That's called a join. It's
a very common type of query, but the syntax is different than what
you're trying to use.

May I suggest that you read the documentation for your database system
and pay close attention to the chapter which explains joins. It will
tell you how to query across two tables. Use your database system's
command-line query tool to talk directly to your database server and try
out various different queries before you take the leap to programming
them in Java. One hurdle at a time ... :-)

David Harper
Cambridge, England

On 31 Oct, 11:27, David Harper <devn...@obliquity.u-net.com> wrote:
> Clive_ wrote:
> > I am trying to get an SQL statement to check that the users ID and the
> > ID row in a table match. There maybe up to a hundred users using the
> > database.
>
> > I have two tables in SQl Server express - Users and Property1 both
> > with a
> > SurveyID column ie Users.SurveyID and Prop1.SurveyID
>
> > The SQL statement is:
> > String querySQL=("Select * from Prop1_WallFinish where SurveyID =
> > \"Users.SurveyID\"" );
>
> > I am getting a java.sql.SQlException: Invalid column name.
>
> > Could anyone point out what is wrong with the statement???
>
> It looks to me like you have two problems.
>
> First, your select statement is trying to select from a table named
> "Prop1_WallFinish" rather than from either of the tables you mention,
> Users and Property1. Perhaps you actually mean
>
> select * from Property1 ....
>
> Second, your "where" clause looks fishy. Assuming that you really mean
> to select rows from the table named Property1, then your query appears to be
>
> select * from Property1 where SurveyID = "Users.SurveyID"
>
> which is testing an ID column, which is presumably a number, against a
> literal string "Users.SurveyID".
>
> Are you trying to match rows in the Property1 table which have the same
> SurveyID column as rows in the Users table? That's called a join. It's
> a very common type of query, but the syntax is different than what
> you're trying to use.
>
> May I suggest that you read the documentation for your database system
> and pay close attention to the chapter which explains joins. It will
> tell you how to query across two tables. Use your database system's
> command-line query tool to talk directly to your database server and try
> out various different queries before you take the leap to programming
> them in Java. One hurdle at a time ... :-)
>
> David Harper
> Cambridge, England- Hide quoted text -
>
> - Show quoted text -

Hi David,

Thanks for email.

I do not want to create a inner or outer join. I want to make sure
that the UserID match in both tables. To ensure the user enters data
into the correct row.

clive

David Harper, 31.10.2007 12:27:
> select * from Property1 where SurveyID = "Users.SurveyID"
>
> which is testing an ID column, which is presumably a number, against a
> literal string "Users.SurveyID".

Unless SQL Server is completely ignoring the SQL standard
"Users.SurveyID" identifies a column name (because of the double
quotes). Character literals have to be enclosed in single quotes

Thomas

Clive_ wrote:
> I do not want to create a inner or outer join. I want to make sure
> that the UserID match in both tables. To ensure the user enters data
> into the correct row.

In SQL, "Users.SurveyID" is not the same as "Users"."SurveyID". The latter
means the "SurveyID" column of the "Users" table where case matters. Most
RDBMSes prefer a case (lower for Postgres), and only enforce case when you
enclose the identifier in quotes. However, you must enclose /each/ identifier
separately in quotes, not the whole expression.

You didn't answer David's comment about using a table name in the query that
was not part of your explanation:

> I have two tables in SQl Server express - Users and Property1 both
> with a
> SurveyID column ie Users.SurveyID and Prop1.SurveyID
....
> String querySQL=("Select * from Prop1_WallFinish where SurveyID =
> \"Users.SurveyID\"" );

To go by just your description, you want something like:
SELECT * FROM Prop1 WHERE Prop1.SurveyID = ?
and use Users.SurveyID to fill the parameter.

--
Lew

On 31 Oct, 12:40, Lew <l...@lewscanon.com> wrote:
> Clive_ wrote:
> > I do not want to create a inner or outer join. I want to make sure
> > that the UserID match in both tables. To ensure the user enters data
> > into the correct row.
>
> In SQL, "Users.SurveyID" is not the same as "Users"."SurveyID". The latter
> means the "SurveyID" column of the "Users" table where case matters. Most
> RDBMSes prefer a case (lower for Postgres), and only enforce case when you
> enclose the identifier in quotes. However, you must enclose /each/ identifier
> separately in quotes, not the whole expression.
>
> You didn't answer David's comment about using a table name in the query that
> was not part of your explanation:
>
>
>
> > I have two tables in SQl Server express - Users and Property1 both
> > with a
> > SurveyID column ie Users.SurveyID and Prop1.SurveyID
> ...
> > String querySQL=("Select * from Prop1_WallFinish where SurveyID =
> > \"Users.SurveyID\"" );
>
> To go by just your description, you want something like:
> SELECT * FROM Prop1 WHERE Prop1.SurveyID = ?
> and use Users.SurveyID to fill the parameter.
>
> --
> Lew

Hi Lew,

Will try this.

Thanks for advice.

Cheers

Clive