Thread: [Csvtosql-cvs] csvtosql_jdk50/src/net/sf/csv2sql/writers JdbcBatchWriter.java,1.1,1.2 JdbcWriter.jav

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Update of /cvsroot/csvtosql/csvtosql_jdk50/src/net/sf/csv2sql/writers
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv25222/src/net/sf/csv2sql/writers

Modified Files:
	JdbcBatchWriter.java JdbcWriter.java 
Log Message:
Hi Davide,

we found some bugs in csvtosql package in JdbcWriter.java and 
JdbcBatchWriter.java.

We tried to use csvtosql to import some data directly to Oracle9i 
database, but Exception ORA-00911: invalid character occurs ...

Problem is, that JdbcWriter/JdbcBatchWriter gets SQL statements from 
storage and puts them directly into stmt.executeUpdate method. Every SQL 
statement (String) contains as last character semicolon (as delimiter of 
SQL statements), however stmt.executeUpdate() method expect this SQL 
statement without any delimiter. Quick and dirty patch is to remove last 
character of every statement, for example ...

Here is diff between 
csvtosql_jdk50\src\net\sf\csv2sql\writers\JdbcWriter.java
and patched file

95c95,96
<                 stmt.executeUpdate(getStorage().get(i));
---
 >                 String line = (String) getStorage().get(i);
 >                 stmt.executeUpdate(line.substring(0,line.length()-1));

Well, it should be better to test, if last characted is semicolon, if 
yes, remove it, if no, no action is necessary ...

The same problem is in csvtosql package for jdk 1.4.

Hope this help, could you please fix this in some next release?

Thanks, best regards,

Daniel Marek

Index: JdbcWriter.java
===================================================================
RCS file: /cvsroot/csvtosql/csvtosql_jdk50/src/net/sf/csv2sql/writers/JdbcWriter.java,v
retrieving revision 1.1
retrieving revision 1.2
diff -C2 -d -r1.1 -r1.2
*** JdbcWriter.java	25 Apr 2005 10:53:23 -0000	1.1
--- JdbcWriter.java	6 Apr 2006 14:46:16 -0000	1.2
***************
*** 1,15 ****
  /*
      Copyright (C) 2004  Davide Consonni <dco...@en...>
!  
      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
--- 1,15 ----
  /*
      Copyright (C) 2004  Davide Consonni <dco...@en...>
! 
      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
***************
*** 24,27 ****
--- 24,28 ----
  import java.sql.Statement;
  import java.util.HashMap;
+ 
  import net.sf.csv2sql.storage.exceptions.StorageException;
  import net.sf.csv2sql.utils.DBUtils;
***************
*** 35,39 ****
   */
  public class JdbcWriter extends AbstractWriter {
!     
      //mandatory parameters
      String driver;
--- 36,40 ----
   */
  public class JdbcWriter extends AbstractWriter {
! 
      //mandatory parameters
      String driver;
***************
*** 41,49 ****
      String username;
      String password;
!     
      //optional parameters
      File jdbcJar = null;
      boolean commit;
!     
      private void init()
      throws InvalidParameterValueException {
--- 42,50 ----
      String username;
      String password;
! 
      //optional parameters
      File jdbcJar = null;
      boolean commit;
! 
      private void init()
      throws InvalidParameterValueException {
***************
*** 52,60 ****
          username =  getWriterProperties().getProperty("username");
          password =  getWriterProperties().getProperty("password");
!         
!         
          //optional set autocommit
          commit = "true".equalsIgnoreCase(getWriterProperties().getProperty("commit"));
!         
          //optional
          String strJdbcJar = getWriterProperties().getProperty("jdbcjar");
--- 53,61 ----
          username =  getWriterProperties().getProperty("username");
          password =  getWriterProperties().getProperty("password");
! 
! 
          //optional set autocommit
          commit = "true".equalsIgnoreCase(getWriterProperties().getProperty("commit"));
! 
          //optional
          String strJdbcJar = getWriterProperties().getProperty("jdbcjar");
***************
*** 63,71 ****
              if (!jdbcJar.exists()) {
                  throw new InvalidParameterValueException("jdbcjar",
!                 getWriterProperties().getProperty("jdbcjar"));
              }
          }
      }
!     
      /**
       * @see AbstractWriter#write
--- 64,72 ----
              if (!jdbcJar.exists()) {
                  throw new InvalidParameterValueException("jdbcjar",
!                         getWriterProperties().getProperty("jdbcjar"));
              }
          }
      }
! 
      /**
       * @see AbstractWriter#write
***************
*** 73,84 ****
      public void write()
      throws WriterException {
!         
          Connection connection = null;
          Statement stmt = null;
!         
          try {
              //get configuration options
              init();
!             
              if (jdbcJar!=null) {
                  connection = DBUtils.openConnection(jdbcJar, driver, url, username, password);
--- 74,85 ----
      public void write()
      throws WriterException {
! 
          Connection connection = null;
          Statement stmt = null;
! 
          try {
              //get configuration options
              init();
! 
              if (jdbcJar!=null) {
                  connection = DBUtils.openConnection(jdbcJar, driver, url, username, password);
***************
*** 86,104 ****
                  connection = DBUtils.openConnection(driver, url, username, password);
              }
!             
              stmt = connection.createStatement();
!             
              //start transaction
              if (commit) {connection.setAutoCommit(false);}
!             
              for (int i=0; i<getStorage().size(); i++) {
!                 stmt.executeUpdate(getStorage().get(i));
              }
!             
              //stop transaction
              if (commit) {connection.commit();}
!             
          } catch (StorageException e) {
!              throw new WriterException("cannot read data from temporary storage", e);
          } catch (SQLException e) {
              throw new WriterException("cannot write statement to database", e);
--- 87,107 ----
                  connection = DBUtils.openConnection(driver, url, username, password);
              }
! 
              stmt = connection.createStatement();
! 
              //start transaction
              if (commit) {connection.setAutoCommit(false);}
! 
              for (int i=0; i<getStorage().size(); i++) {
!                 //stmt.executeUpdate(getStorage().get(i));
!                 String line = (String) getStorage().get(i);
!                 stmt.executeUpdate(line.substring(0,line.length()-1));
              }
! 
              //stop transaction
              if (commit) {connection.commit();}
! 
          } catch (StorageException e) {
!             throw new WriterException("cannot read data from temporary storage", e);
          } catch (SQLException e) {
              throw new WriterException("cannot write statement to database", e);
***************
*** 111,115 ****
          }
      }
!     
      protected HashMap<String, String> requiredParameterList() {
          HashMap<String, String> hm = new HashMap<String, String>();
--- 114,118 ----
          }
      }
! 
      protected HashMap<String, String> requiredParameterList() {
          HashMap<String, String> hm = new HashMap<String, String>();
***************
*** 119,123 ****
          return hm;
      }
!     
      protected HashMap<String, String> optionalParameterList() {
          HashMap<String, String> hm = new HashMap<String, String>();
--- 122,126 ----
          return hm;
      }
! 
      protected HashMap<String, String> optionalParameterList() {
          HashMap<String, String> hm = new HashMap<String, String>();
***************
*** 127,130 ****
          return hm;
      }
!     
  }
--- 130,133 ----
          return hm;
      }
! 
  }

Index: JdbcBatchWriter.java
===================================================================
RCS file: /cvsroot/csvtosql/csvtosql_jdk50/src/net/sf/csv2sql/writers/JdbcBatchWriter.java,v
retrieving revision 1.1
retrieving revision 1.2
diff -C2 -d -r1.1 -r1.2
*** JdbcBatchWriter.java	25 Apr 2005 10:53:23 -0000	1.1
--- JdbcBatchWriter.java	6 Apr 2006 14:46:16 -0000	1.2
***************
*** 24,27 ****
--- 24,28 ----
  import java.sql.Statement;
  import java.util.HashMap;
+ 
  import net.sf.csv2sql.storage.exceptions.StorageException;
  import net.sf.csv2sql.utils.DBUtils;

Thread: [Csvtosql-cvs] csvtosql_jdk50/src/net/sf/csv2sql/writers JdbcBatchWriter.java,1.1,1.2 JdbcWriter.jav

csvtosql-cvs