Store Procedure Execution
Hi guys:
Can anybody help me I am calling a store proc into my JAva program, first my store proc got parameters that I am passing to the String Tokenizer class. The problem I am having is that only one record gets added to the database but the rest of them don't. Also my log file is coming out empty.
here is my store procedure
CREATE PROCEDURE dbo.skill_cat(
@cat_desc char(40),
@subcat_desc char(40),
@skill_desc char(40))
AS
declare @cat_id int, @subcat_id int, @skill_id int
BEGIN
--Process Category
begin tran cat
if not exists (select category_id from CATEGORY where description = @cat_desc)
begin
exec skill_getnextkey 'CATEGORY', 1 ,@cat_id out
insert CATEGORY (category_id, description)
values (@cat_id, @cat_desc)
end
else
select @cat_id = category_id from CATEGORY where description = @cat_desc
commit tran cat
--Process Subcategory
begin tran cat
if not exists (select subcategory_id from SUBCATEGORY where description = @subcat_desc)
begin
exec skill_getnextkey 'SUBCATEGORY', 1 ,@subcat_id out
insert SUBCATEGORY (subcategory_id, category_id, description)
values (@subcat_id, @cat_id, @subcat_desc)
end
else
select @subcat_id = subcategory_id from SUBCATEGORY where description = @subcat_desc
commit tran cat
--Process Skill
begin tran cat
if not exists (select skill_id from SKILL where description = @skill_desc)
begin
exec skill_getnextkey 'SKILL', 1 ,@skill_id out
insert SKILL (skill_id, subcategory_id, description)
values (@skill_id, @subcat_id, @skill_desc)
end
else
select @skill_id = skill_id from SKILL where description = @skill_desc
commit tran cat
END
Java Program:
import java.io.*;
import java.util.*;
import java.sql.*;
import com.sybase.jdbc2.jdbc.SybDriver;
public class dbUpdate
{
public Properties getProperties(String propertyFile) throws IOException
{
Properties p = new Properties();
try
{
FileInputStream is = new FileInputStream(propertyFile);
p.load(is);
}
catch (IOException e)
{
System.out.println("File not found: " + e.getMessage());
}
//returns the object p
return p;
}
public static void main(String[] args) throws SQLException
{
dbUpdate mydbUpdate = new dbUpdate();
Properties p = null;
try
{
//calling the function getProperties() and using command line parameter passing i.e,args[0]
p = mydbUpdate.getProperties(args[0]);
}
catch (IOException e)
{
System.err.println("IOException caught: " + e.getMessage());
}
//Variable Declaration and Initialization
Connection conn= null;
CallableStatement cstmt = null;
ResultSet rs= null;
String DatabaseName= p.getProperty("DatabaseName");
String DatabaseServer = p.getProperty("DatabaseServer");
String PortName= p.getProperty("PortName");
String RDBMS = p.getProperty("RDBMS");
String StoreProcedure= p.getProperty("StoreProcedure");
String DriverName= p.getProperty("DriverName");
String UserName= p.getProperty("UserName");
String UserPassword= p.getProperty("UserPassword");
String Protocol= p.getProperty("Protocol");
String DatabaseConnectivity = p.getProperty("DatabaseConnectivity");
String DataFile= p.getProperty("DataFile");
String LogFile= p.getProperty("LogFile");
try
{
//Loading the Sybase Driver
Class.forName(DriverName);
//Getting a connection to the Database
conn = DriverManager.getConnection(DatabaseConnectivity+":"+RDBMS+":"+Protocol+":"+DatabaseServer+":"+PortName+"/"+DatabaseName,
UserName, UserPassword);
**********PROBLEM STARTS HERE*********************
//Creating a statement Object and calling the Store Procedure
cstmt = conn.prepareCall("{ call skill_cat(?,?,?) }" );
//Reading the Comma Delimited File
File inFile = new File(DataFile);
File outFile = new File(LogFile);
BufferedReader bf = new BufferedReader(new FileReader(inFile));
FileWriter out = new FileWriter(outFile);
String str;
while((str = bf.readLine()) != null)
{
StringTokenizer st = new StringTokenizer(str, ";");
String tokener;
int i=1;
while(st.hasMoreTokens())
{
tokener = st.nextToken();
System.out.println(tokener);
cstmt.setString(i, tokener);
i++;
}
out.write(str);
}
//Execute the Store Procedure
rs= cstmt.executeQuery();
rs.next();
System.out.println("1 - " + rs.getString(1));
rs.next();
System.out.println("2 - " + rs.getString(2));
rs.next();
System.out.println("3 - " + rs.getString(3));
rs.next();
System.out.println("4 - " + rs.getString(4));
**************PROBLEM ENDS HERE***********
/*
//Retrieve and print values in result set
while (rs.next())
{
System.out.println();
}
*/
}
catch (ClassNotFoundException e)
{
System.err.println("Couldn't load database driver: " + e.getMessage());
}
catch (IOException e)
{
System.err.println("IOException caught: " + e.getMessage());
}
catch (SQLException e)
{
System.err.println("SQLException caught: " + e.getMessage());
}
finally
{
try
{
//closing the connection
if (conn != null)
conn.close();
}
catch (SQLException ignored)
{
}
}
}
}
