jdbc create table with ms sql drivers

[David Garratt]

My application builds its own database schema by executing sql ddl commands and works fine with Oracle and mySQl databases. However whatever I try it does not seem to work with mssql (2000 or 2005). I have tried jdbc driver versions 1 through 1.2 and even the beta of version 2.

Basically my "create table" commands seem to be ignored and don't even create an exception.

I am connecting via sql authentication and not using the dll.

Has anyone managed to do this and if so could they be kind enough to post a code example.

Many thanks

Dave

[Paul Sturrock]

I run DDL via JDBC against MSSQL 2000 and 2005 all the time without issue. What driver are you using? What DDL are you running?

[David Garratt]

My routine looks like this :-

public boolean executeDDL(LinkedList<JDBDDL> ddl) {
Statement stmtupdate;
boolean result = true;
int sze = ddl.size();
int percent = 0;

if (sze > 0) {
try {
stmtupdate = hst.getConnection(getSessionID()).createStatement();

JWindowProgress progress = new JWindowProgress();
progress.setVisible(true);
for (int l = 0; l < sze; l++) {
try {
percent = l/sze*100;
progress.update(percent, "% Statement "+String.valueOf(l)+" of "+String.valueOf(sze));
stmtupdate.executeUpdate(ddl.get(l).getText());
ddl.get(l).setError("Success");
} catch (Exception ex) {
result = false;
String err = ex.getMessage();
ddl.get(l).setError(err);
}
}
progress.setVisible(false);
progress = null;
} catch (Exception ex) {
setErrorMessage(ex.getMessage());
}
}
return result;
}

A typical DDL string would look like this :-

CREATE TABLE APP_DESPATCH1(
DESPATCH_NO varchar(10) NOT NULL,
DESPATCH_DATE datetime NULL,
LOCATION_ID_FROM varchar(15) NULL,
STATUS varchar(15) NULL,
TOTAL_PALLETS numeric(38, 0) NULL,
LOCATION_ID_TO varchar(15) NULL,
TRAILER varchar(15) NULL,
HAULIER varchar(15) NULL)

My classpath includes sqljdbc4.jar or sqljdbc.jar depending on the version of the Microsoft jdbc driver I'm playing with, but the results are the same. I've tried their versions, 1.1, 1.2 and Preview 2.0.

The executeUpdate executes with no exception, but the table is not created. I've noticed that I cannot refresh the list of tables in SQL Management Studion until I cancel the debug session in Eclipse, it returns a lock timeout message !

I have connected to the database as the same user as the jdbc session is using (SQL authentication) and I can manually create a table fine.

Very odd.

The same code runs against mysql and oracle databases and creates tables ok (albeit with slightly different syntax in the sql ddl statement).

If you have a small snippet of code from your system that works I would love to have a look.

Many thanks in advance

Dave

[Paul Sturrock]

Unfortunately the snippet of code would be Hibernate code, not JDBC code! But what its doing is the same: DDL via JDBC.

Just a thought, is DDL transactional in SQL Server? Its not in other databases, but it does almost sound like your process is blocking for some reason - possible a commit or rollback? If you call execute sp_who2 what state is your JDBC process in?

[David Garratt]

You nailed it !

For some reason I have to perform a commit on a DDL statement in MS SQL - but not for mySQL or Oracle.

Thanks for that

Dave