[Biojava-dev] DeleteStyle in BioSQL using mysql

[Len Trigg]

Hi guys,

I'm attempting to remove a sequence in a biosql database (both mysql
and hsqldb - have yet to try oracle), and I get the following error:

Caused by: java.sql.SQLException: Syntax error or access violation,
message from server: "You have an error in your SQL syntax
near 'using location, seqfeature  where location.seqfeature_id =
seqfeature.seqfeature' at line 1"
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:1874)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1700)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1569)
        at org.biojava.bio.seq.db.biosql.BioSQLSequenceDB._removeSequence(BioSQLSequenceDB.java:650)
        ... 5 more

When I look at the code, the relevant bit is:

PreparedStatement delete_locs;
if (dstyle ==  DBHelper.DELETE_POSTGRESQL) {
    delete_locs = conn.prepareStatement("delete from location " +
					" where location.seqfeature_id = seqfeature.seqfeature_id and " +
					"       seqfeature.bioentry_id = ?");
} else {
    delete_locs = conn.prepareStatement("delete from location " +
					" using location, seqfeature " + 
					" where location.seqfeature_id = seqfeature.seqfeature_id and " +
					"       seqfeature.bioentry_id = ?");
}
delete_locs.setInt(1, bioentry_id);
delete_locs.executeUpdate();
delete_locs.close();


Now, if I comment out the " using location, seqfeature " line (and
other examples of the same), the sequence removal is super green. Is
the "using" clause part of standard SQL? The clause was obviously put
there for a reason, so could someone explain when it should be used?
Or, if it's not actually needed anymore, should I remove the whole
DeleteStyle special casing altogether?


Cheers,
Len.