|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-03-27 : 07:47:39
|
| Ulhas Nellipunath writes "Thanks for having this forum !!! I am stuck in a unsolvable problem, please help if you can !!!Question :==========I have the following stored procedure (listed below)I am trying to execute this stored proc via a JDBC driver using a java function and display its output to a WEB Page. I have written a java function to capture the output values from the stored proc, which I've copied below.The problem I'm facing heres is: My JDBC java function works fine and brings back the correct results in SQL Server 7, but it doesnot work for SQL Server2000. Can you pls. comment on whats the best way to overcome this problem , why MS SQL Server 2000 works different from SQL Server 7.The error message I get is :Invalid column number(2). Use next() to fetch the data before calling any getxxx method.(I am getting this error message only if I run my code against SQL Server 2000. It works fine in SQL Server 7.)Note : I am using the weblogic driver MSSQLSERVER4 (compatible for SQL Server 2000 & 7).Stored Proc.===========CREATE PROCEDURE vcg.SP_AI_COMPANY_DisplayRecord ( @ShortID varchar (10), @Result bit OUTPUT, @CompanyPK int OUTPUT, @Comp varchar (80) OUTPUT, @Add1 varchar (80) OUTPUT, @Add2 varchar (80) OUTPUT, @Add3 varchar (80) OUTPUT, @City varchar (30) OUTPUT, @St varchar (20) OUTPUT, @Exec varchar (80) OUTPUT, @Office varchar (6) OUTPUT, @Status varchar(50) OUTPUT)ASSELECT @CompanyPK = vcg.T_COMPANY.PRIMARY_KEY, @Comp = vcg.T_COMPANY.COMPANY_NAME, @Add1 = vcg.T_COMPANY.PRIMARY_ADDRESS_LINE_1, @Add2 = vcg.T_COMPANY.PRIMARY_ADDRESS_LINE_2, @Add3 = vcg.T_COMPANY.PRIMARY_ADDRESS_LINE_3, @City = vcg.T_COMPANY.PRIMARY_CITY, @ST = vcg.T_COMPANY.PRIMARY_STATE_KEY, @Exec = vcg.T_COMPANY.USER_LIST, @Office = vcg.T_COMPANY.OFFICE_PRACTICE_KEY, @Status=vcg.T_COMPANY_STATUS_TYPE.[VALUE]FROM vcg.T_COMPANY LEFT OUTER JOIN vcg.T_COMPANY_STATUS_TYPE ON vcg.T_COMPANY.INSTALLATION_KEY = vcg.T_COMPANY_STATUS_TYPE.INSTALLATION_KEY AND vcg.T_COMPANY.COMPANY_STATUS_TYPE_KEY = vcg.T_COMPANY_STATUS_TYPE.PRIMARY_KEYWHERE (vcg.T_COMPANY.COMPANY_SHORT_ID = @ShortID)if @@Rowcount = 1 Select @Result = 1else Select @Result = 0RETURNJAVA Function=============public void LookupCompanyInfo(int thisShortId, CompanyInfo thisCompany){ try { // Get a Connection System.out.println ("Running LookUpCompany Infor" + thisShortId); ResourceObject resource = null; try { resource = getConnectionPool ().checkOut (getAbsoluteName ()); Connection conn = (Connection) resource.getResource (); Statement stmt = conn.prepareCall("{call VCG.SP_AI_COMPANY_DisplayRecord(?,?,?,?,?,?,?,?,?,?,?,?)}"); stmt.setInt(1, thisShortId); stmt.registerOutParameter(2, Types.BIT); stmt.registerOutParameter(3, Types.INTEGER); stmt.registerOutParameter(4, Types.VARCHAR); stmt.registerOutParameter(5, Types.VARCHAR); stmt.registerOutParameter(6, Types.VARCHAR); stmt.registerOutParameter(7, Types.VARCHAR); stmt.registerOutParameter(8, Types.VARCHAR); stmt.registerOutParameter(9, Types.VARCHAR); stmt.registerOutParameter(10, Types.VARCHAR); stmt.registerOutParameter(11, Types.VARCHAR); stmt.registerOutParameter(12, Types.VARCHAR); stmt.execute(); // System.out.println("Output:: " + stmt.getString(4) + stmt.getString(5) + stmt.getString(6) + stmt.getString(7) + stmt.getString(8) + stmt.getString(9)); CompanyInfo temp = new CompanyInfo(); thisCompany.setReturnBit (stmt.getBoolean(2)); thisCompany.setPrimaryKey (stmt.getInt(3)); thisCompany.setCompanyName (stmt.getString(4)); thisCompany.setAddr1 (stmt.getString(5)); |
|
Stored procedure - SQLServer 2000 
My money is that you a running multiple instances of SQL server, and you have not identified the corrent instance in sql server. Sprocs pretty much behave the same BETWEEN '7.0' AND '2000'. And what you have here is not a complicated Sproc.There are no MiraclesAlso can you get anything esle to connect?Brett8-) 