On 5-6-2013 10:49, Huidong TIAN wrote:

> Hi,
>
> I need to write some data.frame from R into Firebird server. Connect,
> Query and dbReadTable worked well. However, when try to write data into
> database, I encountered the following error:
> *
> *
> *> Tab <- dbGetQuery(conn, "select first 1000 * from GROUP_REGISTRATIONS")*
> *> head(Tab)*
> * *ID DATE_CHECKED YEAR_OF_DIAGNOSIS CODING_GROUP
> MALIGN_REGISTERED
> 1 1 2007-02-04 2007 13 - URO/Hud
> 7
> 2 2 2007-02-11 2007 13 - URO/Hud
> 50
> 3 3 2007-02-18 2007 13 - URO/Hud
> 71
> 4 4 2007-02-25 2007 13 - URO/Hud
> 77
> 5 5 2007-03-04 2007 11 - LuNCH/Sarkom/Non-solid
> 1
> 6 6 2007-03-04 2007 12 - GyM *
> 11*
> *> dbWriteTable(conn, "Tab_0", Tab)*
> Error in .verify.JDBC.result(s, "Unable to execute JDBC statement ",
> statement) :
> Unable to execute JDBC statement INSERT INTO Tab_0 VALUES(?,?,?,?,?) (GDS
> Exception. 335544569. Dynamic SQL Error
> SQL error code = -204
> Table unknown
> TAB_0
> At line 1, column 13)
> *> dbReadTable(conn, 'Tab_0')*
> [1] ID DATE_CHECKED YEAR_OF_DIAGNOSIS CODING_GROUP
> MALIGN_REGISTERED
> <0 rows> (or 0-length row.names)
>
>
> An friend suggest that could because the new table has not been 'commit',
> so in the Error message appeared 'Table unknow'.
> I checked the help document for dbWriteTable (it's a R function), and it
> says "any optional arguments that the underlying database driver supports"
> can be passed into dbWriteTable. So, what kind of argument I should give?
> like "autocommit"?

I have been able to reproduce the problem and look at the sourcecode of
RJDBC. The problem is that in dbWriteTable RJDBC disables autocommit
(and enables it again at the end), creates the table and then tries to
insert into it in the same transaction. Firebird does not allow inserts
into a table that is created in the same transaction.

As I see it, there are are two potential solutions:
1) Manually create the table and then use:
dbWriteTable(conn, "Tab_0", Tab, overwrite=FALSE, append=TRUE)

2) First execute dbWriteTable as normal to create the database (+ get
the error) and then append the data
dbWriteTable(conn, "Tab_0", Tab)
dbWriteTable(conn, "Tab_0", Tab, overwrite=FALSE, append=TRUE)

Other than that, I suggest you file a bug with the creators of RJDBC
that they should commit after the create table.

Mark
--
Mark Rotteveel