[R] rjdbc identifier.quote

[Armand Pirvu (gmail)]

Hi Gabor

Thank you for your  reply

I tried it

> dbWriteTable(conn, "table6",mylargedata,identifier.quote ="\"")
Error in .sql.qescape(names(value), TRUE, conn at identifier.quote) : 
  The JDBC connection doesn't support quoted identifiers, but table/column name contains characters that must be quoted (m.1997.09.01)

Alas it still fails.


The strange thing is that it appears to me that " is a valid identifier.quote in the DBMS. As I pointed out  I could run in via JDBC a create table tt ("m.1.2.4" integer) statement

So I am a bit suspicious that maybe I hit a snag in RJDBC ?




Thanks
A

On Feb 9, 2011, at 9:50 PM, Gabor Grothendieck wrote:

> On Wed, Feb 9, 2011 at 9:06 PM, Armand Pirvu (gmail)
> <armand.pirvu at gmail.com> wrote:
>> Dear all
>> 
>> 
>> Backend is Ingres DBMS
>> I use RJDBC with Ingres JDBC driver
>> 
>> I have this csv file
>> 
>> "","Strategy","par1","m.1997.09.01"
>> "1","ALF",2,0.1244
>> 
>> 
>> which I try to load it RJDBC
>> 
>> 
>> require (RJDBC)
>> drv <- JDBC("com.ingres.jdbc.IngresDriver","/home/ingres/ingresv1/ingres/lib/iijdbc.jar")
>> conn <- dbConnect(drv, "jdbc:ingres://192.168.5.21:V17/rtestdb")
>> mylargedata <- read.csv(file="a5.csv.v0",head=TRUE,sep=",")
>>> dbWriteTable(conn, "table5",mylargedata)
>> Error in .sql.qescape(names(value), TRUE, conn at identifier.quote) :
>>  The JDBC connection doesn't support quoted identifiers, but table/column name contains characters that must be quoted (m.1997.09.01)
>> 
>> 
>> So I did another test. I used  Squirel SQL client (JAVA based) and I ran this
>> 
>> create table foo ("m.col1" integer)
>> 
>> 
>> And it succeeded
>> 
>> I checked in the database
>> 
>> * help table foo \g
>> Name:                 foo
>> Column Information:
>>                                                                  Key
>> Column Name                      Type       Length Nulls Defaults Seq
>> "m.col1"                         integer         4  yes    null
>> 
>> 
>> 
>> At this point I am not sure, whether the issue is in Ingres JDBC driver or in RJDBC ? Or maybe is it a specific value for identifier.quote ?
>> 
>> 
>> Any help is appreciated
> 
> In both the RpgSQL and RH2 drivers (which each call RJDBC for the
> respective databases) I had found that this worked:
> 
> JDBC(..., identifier.quote = "\"")
> 
> where above we have double quote, backslash, double quote, double quote.
> 
> -- 
> Statistics & Software Consulting
> GKX Group, GKX Associates Inc.
> tel: 1-877-GKX-GROUP
> email: ggrothendieck at gmail.com