title: Error in JDBC create table and insert in amazon redshift
body: <p>I am using amazon redshift with R. I got the connection working. I wanted to create some database tables and insert some data into them through R. However, I ran into some issues while doing so</p>
  
  <pre><code># Set the Java Environment
  # For 64 bit connections
  Sys.setenv(JAVA_HOME='C:\\Program Files\\Java\\jre1.8.0_121')
  
  # For 32 bit connections
  #Sys.setenv(JAVA_HOME='C:\\Program Files (x86)\\Java\\jre1.8.0_121')
  
  
  # Load libraries if they haven't already
  library(rJava)
  library(RJDBC)
  
  # Get the driver
  jdbcDriver &lt;- JDBC(driverClass="com.amazon.redshift.jdbc41.Driver",   
                     classPath="drivers/RedshiftJDBC41-1.2.1.1001.jar")
  
  # Connect to the database
  jdbcConnection &lt;- dbConnect(jdbcDriver, 
                              "server",
                              "username", "password")
  </code></pre>
  
  <p>The database is connected, without issues. Now I wanted to create a table and insert data into it. I was using <code>sqlCreateTable</code> and <code>sqlAppendTable</code> to create and insert data into the table. </p>
  
  <pre><code># Create a table iris, and insert the data into it
  
  sql_required &lt;- sqlCreateTable(jdbcConnection, "iris_table1", iris)
  
  tbl_create &lt;-  dbGetQuery(jdbcConnection, sql_required) 
  </code></pre>
  
  <p>However, the above command results in the following error.</p>
  
  <pre><code># Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ",  : 
  #                                Unable to retrieve JDBC result set for CREATE TABLE "iris_table1" (
  #                                  "Sepal.Length" DOUBLE PRECISION,
  #                                  "Sepal.Width" DOUBLE PRECISION,
  #                                  "Petal.Length" DOUBLE PRECISION,
  #                                  "Petal.Width" DOUBLE PRECISION,
  #                                  "Species" VARCHAR(255)
  #                                )
  # ([JDBC Driver]com.amazon.dsi.dataengine.impl.DSISimpleRowCountResult 
  #   cannot be cast to com.amazon.dsi.dataengine.interfaces.IResultSet)
  </code></pre>
  
  <p>Nevertheless, the table is created and will be listed by the following command </p>
  
  <pre><code>sql_required &lt;- "SELECT TABLE_NAME
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG=\'demodata\'"
  
  tables_in_reshift &lt;- dbGetQuery(jdbcConnection, sql_required)
  tail(tables_in_reshift, 1)
  
  #   table_name
  # 141 iris_table1
  </code></pre>
  
  <p>I can even insert the data </p>
  
  <pre><code>sql_required &lt;- sqlAppendTable(jdbcConnection, "iris_table1", head(iris))
  
  
  tbl_insert &lt;- dbGetQuery(jdbcConnection, sql_required)
  </code></pre>
  
  <p>However, this will also result in following error.</p>
  
  <pre><code># Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ",  : 
  #                                Unable to retrieve JDBC result set for INSERT INTO "iris_table1"
  #                              ("Sepal.Length", "Sepal.Width", "Petal.Length", "Petal.Width", "Species")
  #                              VALUES
  #                              (5.1, 3.5, 1.4, 0.2, 'setosa'),
  #                              (4.9, 3, 1.4, 0.2, 'setosa'),
  #                              (4.7, 3.2, 1.3, 0.2, 'setosa'),
  #                              (4.6, 3.1, 1.5, 0.2, 'setosa'),
  #                              (5, 3.6, 1.4, 0.2, 'setosa'),
  #                              (5.4, 3.9, 1.7, 0.4, 'setosa')
  # ([JDBC Driver]com.amazon.dsi.dataengine.impl.DSISimpleRowCountResult 
  # cannot be cast to com.amazon.dsi.dataengine.interfaces.IResultSet)
  # 
  </code></pre>
  
  <p>Nevertheless, the data will be inserted, as the following command returns 6 records</p>
  
  <pre><code>sql_required &lt;- "SELECT * from iris_table1"
  
  inserted_dat &lt;- dbGetQuery(jdbcConnection, sql_required)
  
  
  #    sepal.length sepal.width petal.length petal.width species
  # 1          4.9         3.0          1.4         0.2  setosa
  # 2          4.6         3.1          1.5         0.2  setosa
  # 3          5.4         3.9          1.7         0.4  setosa
  # 4          5.1         3.5          1.4         0.2  setosa
  # 5          4.7         3.2          1.3         0.2  setosa
  # 6          5.0         3.6          1.4         0.2  setosa
  </code></pre>
  
  <p>Although the program works, I am concerned with the errors. 
  So, my questions are:</p>
  
  <p>What is the error trying to explain and how do I get rid of it?<br>
  Is there a better way (faster, more efficient) to create a table and insert data into redshift using <code>RJDBC</code>? Thanks!   </p>
  
  <p><strong>UPDATE</strong>
  I was of the initial opinion that running command such as </p>
  
  <pre><code>sqlCreateTable(jdbcConnection, "iris_table5", iris)
  </code></pre>
  
  <p>should be enough to create table but it doesn't create table neither does</p>
  
  <pre><code>sqlAppendTable(jdbcConnection, "iris_table1", head(iris))
  </code></pre>
  
  <p>append data to the database table. </p>