0


I used a simple sql statement for testing purposes if it is possible to create a table function thru java.
However, it takes a very long time for the creation of function to finish and causes other db users to time-out.
Any thoughts?
I'm using sqlserver 2008 express

   try {
        Statement statement = conn.createStatement();
        CallableStatement cs ;
        String  retValue ;
        cs = conn.prepareCall("{? = call dbo.isTableFunctionExists(?)}");
        cs.registerOutParameter(1, Types.INTEGER);
        lcString = "PAY_UDTF_"+this.textField1.getValue().toString() ;
        cs.setString(2, lcString);
        cs.execute();
        retValue = cs.getString(1);
        if (retValue.equals("1")) {
            System.out.println("EXISTS");
            lcSql = " ALTER ";
        }else{
            System.out.println("NOT FOUND");
            lcSql = " CREATE ";
        }
        lcSql = lcSql + " FUNCTION [dbo].[" ;
        lcSql = lcSql + lcString +"] (@pDate date)";
        lcSql = lcSql + " RETURNS TABLE AS RETURN (";
        lcSql = lcSql + " SELECT * FROM dbo.HR_EMPLOYMENT hre ";
        lcSql = lcSql + " Where @pDate between hre.effective_start_date and hre.effective_end_date) ";
        //statement.execute(lcSql);
        statement.executeUpdate(lcSql);
        statement.close();
        System.out.println("COMPLETED");
    } catch (Exception e) {
        System.out.println("EXCEPTION"+e);
    }

    return null;

Any help is highly appreciated.

Thanks,

Elmer

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Elmer
  • 262
  • 2
  • 15
  • Which database driver are you using? Have you tried changing it? Do you know where the bottleneck is? Is it in SQL Server - try running wireshark to see for sure. – javamonkey79 Nov 24 '10 at 05:46
  • I'm using Microsoft SQL Server JDBC Driver 2.0. I tried creating the function in sql server management studio and it worked fine. I'm not familiar with wireshark though. – Elmer Nov 24 '10 at 06:17

1 Answers1

0

There's a line that sets the conn.setAutoCommit(false) ;
At the end of the creation of function, I simply issue conn.commit();

Big thanks anyway....

Elmer

Elmer
  • 262
  • 2
  • 15