/ - Diff - Metacat - Ecoinformatics Redmine

« Previous | Next »

Revision 757

Added by bojilova over 23 years ago

- new db adapter for MS SQL Server
- changed the getUniqueID(conn, tableName) to return the last generated by the db unique ID
instead of generating unique ID first by the appl and then supplied to the insert,
because SQL Server doesn't have capabilities
unique ID to be generated on the client and then supplied to the insert.

       /**
        * Unique ID generator
+       *
        * @param conn db connection in which to generate the unique id
        * @param tableName the table which unique id to generate
        * @param conn db connection in which the unique id was generated
        * @param tableName the table which unique id was generate
        * @exception SQLException <br/> any SQLException that can be thrown
        *            during the db operation
        * @return return the generated unique id as a long type
        */
       public abstract long getUniqueID(Connection conn, String tableName)
                                                       throws SQLException;
       /**
        * The function name that gets the current date and time
        * from the database server

        * The name of the sequence used to generate the unique id
        * is made from the name of the table that uses the id by
        * appending "_id_seq" to it.
        * When record is inserted in the table and before insert trigger
        * gets a nextval from that sequence, select currval of that
        * sequence can return the generated key in the same db connection.
+       *
        * @param conn db connection from which to generate the unique id
        * @param tableName the name of table which unique id to generate
        * @param conn db connection in which the unique id was generated
        * @param tableName the name of table which unique id was generate
        * @exception SQLException any SQLException that can be thrown
        *            during the db operation
        * @return return the generated unique id as a long type
-...
                                              throws SQLException {
         long uniqueid = 0;
         Statement stmt = conn.createStatement();
         stmt.execute("SELECT nextval('" + tableName + "_id_seq')");
         stmt.execute("SELECT currval('" + tableName + "_id_seq')");
         ResultSet rs = stmt.getResultSet();
         if ( rs.next() )
+    		{
-...
        * The PostgreSQL function name that gets the current date
        * and time from the database server
+       *
        * @return return the current date and time function name: "now()::date"
        * @return return the current date and time function name: "now()"
        */
       public String getDateTimeFunction() {
         //System.out.println("The date and time function: " + "now()::date");
     		//to return just the date use now()::date and for the time
     		//use now()::time
         //System.out.println("The date and time function: " + "now()");
     		//to return just the date use now()::date
     		//and for the time use now()::time
         return "now()";
+      }

        * The name of the sequence used to generate the unique id
        * is made from the name of the table that uses the id by
        * appending "_id_seq" to it.
        * When record is inserted in the table and before insert trigger
        * gets a nextval from that sequence, select currval of that
        * sequence can return the generated key in the same db connection.
+       *
        * @param conn db connection in which to generate the unique id
        * @param tableName the name of table which unique id to generate
        * @param conn db connection in which the unique id was generated
        * @param tableName the name of table which unique id was generate
        * @exception SQLException <br/> any SQLException that can be thrown
        *            during the db operation
        * @return return the generated unique id as a long type
-...
                                              throws SQLException {
         long uniqueid = 0;
         Statement stmt = conn.createStatement();
         stmt.execute("SELECT " + tableName + "_id_seq.nextval FROM dual");
         stmt.execute("SELECT " + tableName + "_id_seq.currval FROM dual");
         ResultSet rs = stmt.getResultSet();
         if ( rs.next() ) {
           uniqueid = rs.getLong(1);

     /**
      *  '$RCSfile$'
      *    Purpose: A db adapter class for MS SQL Server RDBMS.
      *  Copyright: 2000 Regents of the University of California and the
      *             National Center for Ecological Analysis and Synthesis
      *    Authors: Jivka Bojilova
      *    Release: @release@
+     *
      *   '$Author$'
      *     '$Date$'
      * '$Revision$'
+     *
      * This program is free software; you can redistribute it and/or modify
      * it under the terms of the GNU General Public License as published by
      * the Free Software Foundation; either version 2 of the License, or
      * (at your option) any later version.
+     *
      * This program is distributed in the hope that it will be useful,
      * but WITHOUT ANY WARRANTY; without even the implied warranty of
      * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
      * GNU General Public License for more details.
+     *
      * You should have received a copy of the GNU General Public License
      * along with this program; if not, write to the Free Software
      * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
      */
     package edu.ucsb.nceas.dbadapter;
     import java.sql.*;
     /**
      * The MS SQL Server db adapter implementation.
      */
     public class DBSqlServer extends DBAdapter {
       /**
        * The SQL Server unique ID generator through use of IDENTITY key
        * The IDENTITY key is a column in the table. When record is inserted
        * in the table, SELECT @@IDENTITY can return the key generated in
        * that IDENTITY column in the same db connection.
        * This is the only way to get unique id: let the SQL Server assign
        * a value in IDENTITY column and get it afterwards for use in the
        * application.
+       *
        * @param conn db connection in which to generate the unique id
        * @param tableName the name of table which unique id to generate
        * @exception SQLException <br/> any SQLException that can be thrown
        *            during the db operation
        * @return return the generated unique id as a long type
        */
       public long getUniqueID(Connection conn, String tableName)
                                              throws SQLException {
         long uniqueid = 0;
         Statement stmt = conn.createStatement();
         stmt.execute("SELECT @@IDENTITY");
         ResultSet rs = stmt.getResultSet();
         if ( rs.next() ) {
           uniqueid = rs.getLong(1);
+        }
         stmt.close();
     System.out.println("Unique ID: " + uniqueid);
         return uniqueid;
+      }
       /**
        * The SQL Server's function name that gets the current date and time
        * from the database server: "getdate()"
+       *
        * @return return the current date and time function name: "getdate()"
        */
       public String getDateTimeFunction() {
         //System.out.println("The date and time function: " + "getdate()");
         return "getdate()";
+      }
       /**
        * The SQL Server's string delimiter character: single quote (')
+       *
        * @return return the string delimiter: single quote (')
        */
       public String getStringDelimiter() {
         return "'";
+      }
+    }

Also available in: Unified diff

Project

General

Profile

Metacat

Revision 757

Added by bojilova over 23 years ago