Hello,
I am using a database connection (conn1) in my JSP include page which is connecting to the first (SQL 7) database with a stored procedure. The stored procedure is performing a query which utilizes tables in another database on the same server. I have also tried to use a second database connection, and an inline query, but still get an error. Can someone please tell me what I am doing wrong? Is it even possible to do this with JSP? I've been able to use two databases in ASP, so I wasn't sure about making this work in JSP. Below is the JSP page and stored procedure.
TIA,
Bruce Wexler
Programmer/Analyst
<%@ page language="java"%>
<%@ page import="java.util.*"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.sql.DriverManager.*" %>
<%@ page import="java.sql.SQLException.*" %>
<%@ page import="java.sql.Connection.*" %>
<%@ page import="java.sql.SQLWarning.*" %>
<%@ page import="java.io.*" %>
<%
Connection conn=null;
try{
DriverManager.registerDriver(new weblogic.jdbc.mssqlserver4.Driver());
conn1=DriverManager.getConnection("jdbc:weblogic:mssqlserver4:[EMAIL PROTECTED]:1433?user=JavaMan&password=chucky");
String query = "EXEC getAuditDate"
Statement stat=conn.createStatement();
ResultSet rs = stat.executeQuery(query);
}
catch(SQLException se){
out.print(se.toString());
}
%>
*************************************************************************************************************************************************************
CREATE PROCEDURE [dbo].[getAuditDate] AS
SELECT tblAuditors.AUDITORNBR, VIEWZCHAIN.[ID#], VIEWZCHAIN.STR_NAME, WalMart.Store.D1, VIEWZCHAIN.[STR#], VIEWZCHAIN.Address,
VIEWZCHAIN.CITY, VIEWZCHAIN.ST, WalMart.Store.[DemoID#], Progstats.[Program#], Progstats.ProgramName, Progstats.HOURS
FROM (tblAuditors INNER JOIN ((tblZipCode INNER JOIN VIEWZCHAIN ON tblZipCode.ZIPCODE = VIEWZCHAIN.Zip)
INNER JOIN WalMart.Store ON VIEWZCHAIN.[ID#] = WalMart.Store.[TD#])
ON tblAuditors.AUDITORNBR = tblZipCode.AUDITORNBR)
INNER JOIN Progstats ON Store.[Program#] = Progstats.[Program#]
WHERE (((tblAuditors.AUDITORNBR)="LA-334")
AND ((WalMart.Store.D1) Between '8/13/2001' And '8/19/2001')
AND ((Progstats.StandardQuestionsAudit)<2))
ORDER BY WalMart.Store.D1, VIEWZCHAIN.[STR#];
GO
