Re: Sqldf - error message - Mailing list pgsql-sql

I think you (or I) might misunderstand TL's comment.  The sql is
probably being sent by some late-in-the-client-side-game driver with
names quoted with double-quote marks, but the database does not have the
column names etc  in mixed case.  Can you connect directly to the
postgres server (psql -h dbservername -U dbusername) to see the
definition of the tables in question (\d dbtablename)?

Marvelde, Luc te wrote:
> Nothing is wrong with the data... Everything is there in the dataframe. All the names are case sensitive, but the way
itis spelled is correct.... So that cant be the problem
 
> 
> Luc 
> 
> -----Original Message-----
> From: Pavel Stehule [mailto:pavel.stehule@gmail.com] 
> Sent: vrijdag 20 november 2009 15:38
> To: Marvelde, Luc te
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Sqldf - error message
> 
> Hello
> 
> 2009/11/20 Marvelde, Luc te <L.teMarvelde@nioo.knaw.nl>:
>> Hi All!
>>
>> I just discovered sqldf and im very enthousiastic, as I am a big fan 
>> of R and I often get frustrated using Access for datamanagement.
>>
>> I tried running some queries from Access in R, and it works very well.
>> However, I have problems with some queries.
>>
>> If I run this SQL query:
>>
>>> sqldf("SELECT
>> + dbo_tbl_Terrein.RingCentraleNaam,
>> + dbo_tbl_Broedsels.BroedselID
>> + FROM ((dbo_tbl_BroedselLocatie
>> + INNER JOIN dbo_tbl_Broedsels ON dbo_tbl_BroedselLocatie.BroedselID =
>> dbo_tbl_Broedsels.BroedselID)
>> + INNER JOIN dbo_tbl_Nestkasten ON dbo_tbl_BroedselLocatie.NestkastID 
>> + =
>> dbo_tbl_Nestkasten.NestkastID)
>> + INNER JOIN dbo_tbl_Terrein ON dbo_tbl_Nestkasten.TerreinNummer =
>> dbo_tbl_Terrein.TerreinNummer
>> + WHERE (((dbo_tbl_Terrein.RingCentraleNaam)='Oosterhout a/d 
>> + Waal'));")
>>
>> I get the following message:
>>
>> Error in sqliteExecStatement(con, statement, bind.data) :
>>   RS-DBI driver: (error in statement: no such column:
>> dbo_tbl_Broedsels.BroedselID)
>>
>> ( I also tried removing the 'dbo_tbl_'  part of the dataframe names, 
>> but still the same problem occurred)
> 
> I thing, you have a problem with data. Probably some creating script was broken or some similar. The message is
clean.Your database has not column BroedselID. Recheck your query, please. Im not sure - maybe SQLite identifiers are
casesensitive. Maybe are defined as case sensitive.
 
> 
> Regards
> Pavel Stehule
> 
>> Now ofcoarse I looked on google, because I was sure someone would have 
>> had the same problem.. And yes, I found this website...
>>
>> http://code.google.com/p/sqldf/
>>
>> They say:
>> This happens when you try to use variable with a dot in it (as dots 
>> have special meaning to SQL) or if you try to use SQL92 reserved keywords.
>> SQLite/RSQLite replaces dots with underscores and changes keywords 
>> words so that they can be used. The keywords in question can be found 
>> by entering the following at the R command line:
>>
>> .SQL92Keywords
>>
>> In this list of keywords, i cannot find any word that is close to the 
>> error... So, i was wondering if someone knows how to solve it. 
>> preferably i do not want to change all variable names, as these names 
>> come from the 'big main database' of the institute.
>>
>> The site also says that SQLite/RSQLite can fix the problem... but then 
>> when i look in their help, i cannot see how i can easily use those 
>> commands...
>>
>> Anyone?
>>
>> Kind regards,
>>
>> Luc te Marvelde
>> New member :)
>>
>>
>>
>>
>