I have done it!!!
Thanks to randym... he has helped me with the queries...
What I have learned, I want to share with others at Javaranch...
I am using msaccess 98 and jdk1.3
I have used UNION ALL to do the job...
I have used CallableStatement to access required stored query...
CallableStatement cstmt = con.prepareCall(sql);
I was in for a surprise when calling {call SROrderUSale Query(?, ?, ?, ?)}
I had two queries in database:
1) SROrderUSale
2) SROrderUSale Query
While they were two different queries in m$access database but
Java was calling SROrderUSale for
word "SROrderUSale Query"
maybe it is accepting word "Query" as relevant to database because:
if we code:
DatabaseMetaData dbmd=connection.getMetaData();
System.out.println("All Procedures Are "
+(dbmd.allProceduresAreCallable()?"Callable":"Not Callable"));
System.out.println("Procedures are Called "+dbmd.getProcedureTerm());
We see that Stored Procedures are called "Query" in access. So I
changed the second query to SROrderUSaleQuery in database and my Java code.
Now it is working as expected.
MY first Stored Procedure namely SROrderUSale was:
--------------------------------------------------
SELECT orderDate as [date],sum([Order].Advance) as ordamount,
0 as salamount, ('Order') as TableName from [Order]
WHERE orderDate Between BegDate And EndDate
GROUP By orderDate
UNION ALL
SELECT saleDate as [date], 0 as ordamount,sum(Sales.Advance) as salamount,
('Sales') as TableName from Sales
WHERE saleDate Between BegDate And EndDate
GROUP By saleDate
ORDER BY [date];
----------------------------------------------------
Though I have a field TableName but I did not use it further down the line.
It is useful to get info where is the data is coming from.
MY second Stored Procedure namely SROrderUSaleQuery that was calling
SROrderUSale was :
----------------------------------------------------
SELECT date, Sum(ordamount) AS orderamount, Sum(salamount) AS salesamount,
Sum([ordamount])+Sum([salamount]) AS Total
FROM SROrderUSale
GROUP BY date;
----------------------------------------------------
BegDate (beginning date) and EndDate (ending date) are parameters that
are taken in at runtime. If you run query in database you will be asked
once for each date. You will have to set it twice from the code as they
are used as many times in the first query.
setString(1, "12-Mar-06"); //BegDate
setString(2, "14-Mar-06"); //EndDate
setString(3, "12-Mar-06"); //BegDate
setString(4, "14-Mar-06"); //EndDate
Now some discussion of errors:
Setting prepareCall(sql) worked for me only.
I could not do anyting else with ResultSet or Concurrency.
The following are the settings and exceptions:
con.prepareCall(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE
);
java.sql.SQLException:
[Microsoft][ODBC Microsoft Access Driver]Optional feature not
implemented
con.prepareCall(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY
);
java.sql.SQLException: Unable to obtain result set row count.
From {CALL SRORDERUSALEQUERY(?, ?, ?, ?)}
con.prepareCall(sql,
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE
);
java.sql.SQLException: Unable to obtain result set row count.
From {CALL SRORDERUSALEQUERY(?, ?, ?, ?)}
con.prepareCall(sql,
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY
);
java.sql.SQLException: Unable to obtain result set row count.
From {CALL SRORDERUSALEQUERY(?, ?, ?, ?)}
The
Test Class is ....
[ September 28, 2005: Message edited by: Maki Jav ]