[Biojava-dev] DeleteStyle in BioSQL using mysql

[Simon Foote]

Hi Len,

The "using" syntax is valid as it pertains to cascading deletes. 
The deletes function correctly with MySQL version 4.0.14 using InnoDb 
tables in my test cases.
The code needs the following added to check the MySQL version.

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 if ((dstyle ==  DBHelper.DELETE_MYSQL4) {
    delete_locs = conn.prepareStatement("delete from location " +
					" using location, seqfeature " + 
					" where location.seqfeature_id =
seqfeature.seqfeature_id and " +
					"       seqfeature.bioentry_id =
?");

} else {
    delete_locs = conn.prepareStatement("delete from location " +
					" where location.seqfeature_id =
seqfeature.seqfeature_id and " +
					"       seqfeature.bioentry_id =
?");
}

Cheers,
Simon

-- 
Bioinformatics Programmer
Institute for Biological Sciences
National Research Council of Canada
[T] 613-990-0561  [F] 613-952-9092
simon.foote at nrc-cnrc.gc.ca


Len Trigg wrote:

>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(BioSQLSequenc
>eDB.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.
>_______________________________________________
>biojava-dev mailing list
>biojava-dev at biojava.org
>http://biojava.org/mailman/listinfo/biojava-dev
>  
>

-- 
Bioinformatics Specialist
Institute for Biological Sciences
National Research Council of Canada
[T] 613-990-0561  [F] 613-952-9092
simon.foote at nrc-cnrc.gc.ca