Board index » jbuilder » Re: Using a data variable in a where clause

Re: Using a data variable in a where clause


2003-09-07 03:52:56 PM
jbuilder7
hi,
On Sat, 06 Sep 2003 23:41:18 +0100, Rudy wrote:
Quote
I need to run a select query which contains the following e.g

String query = SELECT APP_DATE, APP_TIME, APP_TYPE, a.CUST_NO FROM
APPOINTMENTS a, PATIENT_DETAILS p WHERE APP_DATE = " + appDate + " and
p.CUST_NO = a.CUST_NO

appData is a java.sql.Date format and contain the date i want to find.
The error i get is "Operator = doesn't apply to the types <DATE>and
<INT>" I have also read some that this would return the date at midnight
and therefore would not match, how can i fix this. I am using JDataStore
for development.
Try to use PreparedStatement:
PreparedStatement statement = conn.prapareStatement("SELECT APP_DATE,
APP_TIME, APP_TYPE, a.CUST_NO FROM APPOINTMENTS a, PATIENT_DETAILS p WHERE
APP_DATE = ? and p.CUST_NO = a.CUST_NO");
statement.setDate(1, appDate);
ResultSet resultSet = statement.executeQuery();
good luck
marcin
 
 

Re:Re: Using a data variable in a where clause

Hi there,
the problem i have with using a prepared statement is that i have not got a
database connection as i am accessing the data remotely, and therefore do
not have a database connection. Any other ideas???
Thanks
Rudy
"marcin brzoza" < XXXX@XXXXX.COM >wrote in message
Quote
hi,

On Sat, 06 Sep 2003 23:41:18 +0100, Rudy wrote:

>I need to run a select query which contains the following e.g
>
>String query = SELECT APP_DATE, APP_TIME, APP_TYPE, a.CUST_NO FROM
>APPOINTMENTS a, PATIENT_DETAILS p WHERE APP_DATE = " + appDate + " and
>p.CUST_NO = a.CUST_NO
>
>appData is a java.sql.Date format and contain the date i want to find.
>The error i get is "Operator = doesn't apply to the types <DATE>and
><INT>" I have also read some that this would return the date at midnight
>and therefore would not match, how can i fix this. I am using
JDataStore
>for development.
Try to use PreparedStatement:
PreparedStatement statement = conn.prapareStatement("SELECT APP_DATE,
APP_TIME, APP_TYPE, a.CUST_NO FROM APPOINTMENTS a, PATIENT_DETAILS p WHERE
APP_DATE = ? and p.CUST_NO = a.CUST_NO");
statement.setDate(1, appDate);
ResultSet resultSet = statement.executeQuery();

good luck
marcin
 

Re:Re: Using a data variable in a where clause

"Rudy" < XXXX@XXXXX.COM >schrieb im Newsbeitrag
Quote
Hi,

I need to run a select query which contains the following
e.g

String query = SELECT APP_DATE, APP_TIME, APP_TYPE, a.CUST_NO
FROM APPOINTMENTS a, PATIENT_DETAILS p
WHERE APP_DATE = " + appDate + " and p.CUST_NO = a.CUST_NO

appData is a java.sql.Date format and contain the date i want to find.
The
error i get is "Operator = doesn't apply to the types <DATE>and <INT>"
I have also read some that this would return the date at midnight and
therefore would not match, how can i fix this. I am using JDataStore for
development.

Thanks
Rudy


HI,
it is in some databases default to return the date at midnight if there is
nothing else specified. If you use the the sysdate() function in combination
you'll really have a problem, because sysdate() contains the time too. In
this case trunc(sysdate()) helps. My SQL reference says sysdate() is a mySQL
function but it also is used in oracle 8.i.
If JdataStore supports it, you can try
to_date(datevalue_as_string,formatstring) to enter your date-variable.
Ralph
 

{smallsort}

Re:Re: Using a data variable in a where clause

hi,
On Sun, 07 Sep 2003 12:30:06 +0100, Rudy wrote:
Quote
the problem i have with using a prepared statement is that i have not got
a database connection as i am accessing the data remotely, and therefore
do not have a database connection. Any other ideas???
Do you building SQL statements on client, post as string to server and
execute on server?
greetings
marcin
 

Re:Re: Using a data variable in a where clause

Hi,
I am really at a loss, i converted the date to a string and tried to compare
that, it failed, have tried to compare java.sql.date and that failed. I
though it would be really simple to compare a date, without the use of a
prepared statement. I had written my app as a single user app with
querydatasets, preparedStatements, parameterRows but i am now changing it to
a client-server module, which using the example that comes with jbuilder,
they use tabledatasets to store the data. However, i have found it
increasingly difficult to make the switch when i needed parameterized rows
etc. Is the a way of using queryDataSets with the need of a database
connection??
Thanks, sorry about the rant
Rudy
"Ralph Steins" < XXXX@XXXXX.COM >wrote in message
Quote

"Rudy" < XXXX@XXXXX.COM >schrieb im Newsbeitrag
news: XXXX@XXXXX.COM ...
>Hi,
>
>I need to run a select query which contains the following
>e.g
>
>String query = SELECT APP_DATE, APP_TIME, APP_TYPE, a.CUST_NO
>FROM APPOINTMENTS a, PATIENT_DETAILS p
>WHERE APP_DATE = " + appDate + " and p.CUST_NO = a.CUST_NO
>
>appData is a java.sql.Date format and contain the date i want to find.
The
>error i get is "Operator = doesn't apply to the types <DATE>and <INT>"
>I have also read some that this would return the date at midnight and
>therefore would not match, how can i fix this. I am using JDataStore
for
>development.
>
>Thanks
>Rudy
>
>
HI,

it is in some databases default to return the date at midnight if there is
nothing else specified. If you use the the sysdate() function in
combination
you'll really have a problem, because sysdate() contains the time too. In
this case trunc(sysdate()) helps. My SQL reference says sysdate() is a
mySQL
function but it also is used in oracle 8.i.
If JdataStore supports it, you can try
to_date(datevalue_as_string,formatstring) to enter your date-variable.

Ralph


 

Re:Re: Using a data variable in a where clause

Basically yes, i pass the query to the server
Thanks
Rudy
"marcin brzoza" < XXXX@XXXXX.COM >wrote in message
Quote
hi,

On Sun, 07 Sep 2003 12:30:06 +0100, Rudy wrote:

>the problem i have with using a prepared statement is that i have not
got
>a database connection as i am accessing the data remotely, and therefore
>do not have a database connection. Any other ideas???
Do you building SQL statements on client, post as string to server and
execute on server?

greetings
marcin
 

Re:Re: Using a data variable in a where clause

hi,
On Sun, 07 Sep 2003 15:15:03 +0100, Rudy wrote:
Quote
Basically yes, i pass the query to the server Thanks
So build the statement with PreparatedStatement syntax on client and post
to server, as next post to server the meta information about parameters in
statement (like: "1,S" = the first parameter is String). So you can post
the parameter as strings (server knows the meta data and can convert data).
Or you can use Serialization (or Externalizable interface) to send
statement and parameters.
Or you can create the connection to db on client ;)
good luck
marcin
 

Re:Re: Using a data variable in a where clause

On Sun, 07 Sep 2003 16:30:34 +0100, Rudy wrote:
Quote
Thanks,

Should i make a database connection, as in Database database1..., on the
server and create a prepared statement from the statement created on the
client??? Seem logical, i will give it a try. Also how is multi-user
access handled accross a network, with regard to opening the same table
and viewing the same record??? If u know.
What do you want to do? Manage the server only database access or have
additional logic? Work your software with static structure of database?
Can clients have directly access to database?
i ask, because i don't know, what do you want to have.
greetings
marcin
 

Re:Re: Using a data variable in a where clause

hi,
On Sun, 07 Sep 2003 17:26:43 +0100, Rudy wrote:
Quote
ok, ideally, i would like to have the server having access only to the
database, and this provides the data needed by the client through
different queries. What do u mean when u say static structure of the
database?? I would like the database to have consistent data at all times.
This is, as u can tell, the first distributed database system i have tried
to produce, am i going the right way about it??? Thanks for your advice
"static structure of db" = the structure (not the data) of your database
is not dynamic (the software works with defined structure of db, not with
any db, like sql client for generic db).
ok, back to your problem. are you writing a distributed application with
rmi (i saw your postings in b.p.j.corba-rmi)? You implement on server a
class with logic (in this case database access). Methods of this class
represents queries and needs parameters for queries. Example (without rmi
specific stuff):
on server:
class Foo
{
Connection connection = null;
// ...
public Appointment[] getAppointments(Date _date)
{
Appointment[] result = null;
// we are on server =>we know connection to database
PreparedStatement statement = connection.prapareStatement("SELECT
APP_DATE, APP_TIME, APP_TYPE, a.CUST_NO FROM APPOINTMENTS a,
PATIENT_DETAILS p WHERE APP_DATE = ? and p.CUST_NO = a.CUST_NO");
statement.setDate(1, _date);
ResultSet resultSet = statement.executeQuery(); while
(resultSet.next())
{
// here we can read data from resultset and build result
}
return result;
}
}
on client:
Foo foo = (Foo)Naming.lookup(...); // rmi stuff
Appointment[] appointment = foo.getAppointments(aDate);
Exactly this happens in the sun's example for rmi (calculator):
developer.java.sun.com/developer/onlineTraining/rmi/RMI.html
Methods of the class CalculatorImpl implements the logic (in your case is
it db access). The client uses this methods (chapter "5. Clients").
good luck
marcin
 

Re:Re: Using a data variable in a where clause

Hey marcin,
thanks for the help...i seem to be cracking on now and have established a
coonection and can now us prepared statments...the transformation from
single user to multi, now seems to be going well, all i worry about is speed
of access and consistent, up-to-date data...oh well :),
thanks for all your time
Rudy
"marcin brzoza" < XXXX@XXXXX.COM >wrote in message
Quote
hi,

On Sun, 07 Sep 2003 17:26:43 +0100, Rudy wrote:
>ok, ideally, i would like to have the server having access only to the
>database, and this provides the data needed by the client through
>different queries. What do u mean when u say static structure of the
>database?? I would like the database to have consistent data at all
times.
>This is, as u can tell, the first distributed database system i have
tried
>to produce, am i going the right way about it??? Thanks for your advice
"static structure of db" = the structure (not the data) of your database
is not dynamic (the software works with defined structure of db, not with
any db, like sql client for generic db).
ok, back to your problem. are you writing a distributed application with
rmi (i saw your postings in b.p.j.corba-rmi)? You implement on server a
class with logic (in this case database access). Methods of this class
represents queries and needs parameters for queries. Example (without rmi
specific stuff):

on server:
class Foo
{
Connection connection = null;
// ...

public Appointment[] getAppointments(Date _date)
{
Appointment[] result = null;
// we are on server =>we know connection to database
PreparedStatement statement = connection.prapareStatement("SELECT
APP_DATE, APP_TIME, APP_TYPE, a.CUST_NO FROM APPOINTMENTS a,
PATIENT_DETAILS p WHERE APP_DATE = ? and p.CUST_NO = a.CUST_NO");
statement.setDate(1, _date);
ResultSet resultSet = statement.executeQuery(); while
(resultSet.next())
{
// here we can read data from resultset and build result
}
return result;
}
}

on client:
Foo foo = (Foo)Naming.lookup(...); // rmi stuff
Appointment[] appointment = foo.getAppointments(aDate);

Exactly this happens in the sun's example for rmi (calculator):

developer.java.sun.com/developer/onlineTraining/rmi/RMI.html
Methods of the class CalculatorImpl implements the logic (in your case is
it db access). The client uses this methods (chapter "5. Clients").

good luck
marcin