pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSqldf - error message
9+ messages / 6 participants
[nested] [flat]
* Sqldf - error message
@ 2009-11-20 14:18 Marvelde, Luc te <L.teMarvelde@nioo.knaw.nl>
2009-11-20 14:37 ` Re: Sqldf - error message Pavel Stehule <pavel.stehule@gmail.com>
2009-11-20 14:39 ` Re: Sqldf - error message Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 9+ messages in thread
From: Marvelde, Luc te @ 2009-11-20 14:18 UTC (permalink / raw)
To: pgsql-sql; +Cc: 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)
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 :)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sqldf - error message
2009-11-20 14:18 Sqldf - error message Marvelde, Luc te <L.teMarvelde@nioo.knaw.nl>
@ 2009-11-20 14:37 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-11-20 14:42 ` Re: Sqldf - error message Pavel Stehule <pavel.stehule@gmail.com>
2009-11-20 14:50 ` Re: Sqldf - error message Marvelde, Luc te <L.teMarvelde@nioo.knaw.nl>
1 sibling, 2 replies; 9+ messages in thread
From: Pavel Stehule @ 2009-11-20 14:37 UTC (permalink / raw)
To: Marvelde, Luc te <L.teMarvelde@nioo.knaw.nl>; +Cc: pgsql-sql
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 case sensitive. 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 :)
>
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sqldf - error message
2009-11-20 14:18 Sqldf - error message Marvelde, Luc te <L.teMarvelde@nioo.knaw.nl>
2009-11-20 14:37 ` Re: Sqldf - error message Pavel Stehule <pavel.stehule@gmail.com>
@ 2009-11-20 14:42 ` Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Pavel Stehule @ 2009-11-20 14:42 UTC (permalink / raw)
To: Marvelde, Luc te <L.teMarvelde@nioo.knaw.nl>; +Cc: pgsql-sql
2009/11/20 Pavel Stehule <pavel.stehule@gmail.com>:
> 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'));")
>>
p.s. Your style is strange. Don't use useless parentheses
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'
Query is same and little bit more readable.
>> 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 case sensitive. 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 :)
>>
>>
>>
>>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sqldf - error message
2009-11-20 14:18 Sqldf - error message Marvelde, Luc te <L.teMarvelde@nioo.knaw.nl>
2009-11-20 14:37 ` Re: Sqldf - error message Pavel Stehule <pavel.stehule@gmail.com>
@ 2009-11-20 14:50 ` Marvelde, Luc te <L.teMarvelde@nioo.knaw.nl>
2009-11-20 15:36 ` Re: Sqldf - error message Rob Sargent <robjsargent@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Marvelde, Luc te @ 2009-11-20 14:50 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: pgsql-sql
Nothing is wrong with the data... Everything is there in the dataframe. All the names are case sensitive, but the way it is 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 case sensitive. 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 :)
>
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sqldf - error message
2009-11-20 14:18 Sqldf - error message Marvelde, Luc te <L.teMarvelde@nioo.knaw.nl>
2009-11-20 14:37 ` Re: Sqldf - error message Pavel Stehule <pavel.stehule@gmail.com>
2009-11-20 14:50 ` Re: Sqldf - error message Marvelde, Luc te <L.teMarvelde@nioo.knaw.nl>
@ 2009-11-20 15:36 ` Rob Sargent <robjsargent@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Rob Sargent @ 2009-11-20 15:36 UTC (permalink / raw)
To: Marvelde, Luc te <L.teMarvelde@nioo.knaw.nl>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; 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 it is 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 case sensitive. 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 :)
>>
>>
>>
>>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sqldf - error message
2009-11-20 14:18 Sqldf - error message Marvelde, Luc te <L.teMarvelde@nioo.knaw.nl>
@ 2009-11-20 14:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-11-20 14:43 ` Re: Sqldf - error message Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Tom Lane @ 2009-11-20 14:39 UTC (permalink / raw)
To: Marvelde, Luc te <L.teMarvelde@nioo.knaw.nl>; +Cc: pgsql-sql
"Marvelde, Luc te" <L.teMarvelde@nioo.knaw.nl> writes:
> 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)
It looks to me like sqldf is unaware of the rules about identifier
case-folding in Postgres. That column would come back named
"broedselid", but it's probably looking for "BroedselID".
Or possibly it's expecting the qualifier "dbo_tbl_Broedsels."
to be included in the returned column name. Either way, you
need to bug sqldf's authors to fix it.
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sqldf - error message
2009-11-20 14:18 Sqldf - error message Marvelde, Luc te <L.teMarvelde@nioo.knaw.nl>
2009-11-20 14:39 ` Re: Sqldf - error message Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-11-20 14:43 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-11-20 16:23 ` Re: Sqldf - error message Adrian Klaver <aklaver@comcast.net>
0 siblings, 1 reply; 9+ messages in thread
From: Pavel Stehule @ 2009-11-20 14:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Marvelde, Luc te <L.teMarvelde@nioo.knaw.nl>; pgsql-sql
2009/11/20 Tom Lane <tgl@sss.pgh.pa.us>:
> "Marvelde, Luc te" <L.teMarvelde@nioo.knaw.nl> writes:
>> 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)
>
> It looks to me like sqldf is unaware of the rules about identifier
> case-folding in Postgres. That column would come back named
> "broedselid", but it's probably looking for "BroedselID".
> Or possibly it's expecting the qualifier "dbo_tbl_Broedsels."
> to be included in the returned column name. Either way, you
> need to bug sqldf's authors to fix it.
>
is it Postgres? I see "Error in sqliteExecStatement"
regards
Pavel Stehule
> regards, tom lane
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sqldf - error message
2009-11-20 14:18 Sqldf - error message Marvelde, Luc te <L.teMarvelde@nioo.knaw.nl>
2009-11-20 14:39 ` Re: Sqldf - error message Tom Lane <tgl@sss.pgh.pa.us>
2009-11-20 14:43 ` Re: Sqldf - error message Pavel Stehule <pavel.stehule@gmail.com>
@ 2009-11-20 16:23 ` Adrian Klaver <aklaver@comcast.net>
2009-11-20 17:11 ` Re: Sqldf - error message Whit Armstrong <armstrong.whit@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Adrian Klaver @ 2009-11-20 16:23 UTC (permalink / raw)
To: pgsql-sql; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Marvelde, Luc te <L.teMarvelde@nioo.knaw.nl>
On Friday 20 November 2009 6:43:53 am Pavel Stehule wrote:
> 2009/11/20 Tom Lane <tgl@sss.pgh.pa.us>:
> > "Marvelde, Luc te" <L.teMarvelde@nioo.knaw.nl> writes:
> >> 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)
> >
> > It looks to me like sqldf is unaware of the rules about identifier
> > case-folding in Postgres. That column would come back named
> > "broedselid", but it's probably looking for "BroedselID".
> > Or possibly it's expecting the qualifier "dbo_tbl_Broedsels."
> > to be included in the returned column name. Either way, you
> > need to bug sqldf's authors to fix it.
>
> is it Postgres? I see "Error in sqliteExecStatement"
It is more complicated than that see:
http://code.google.com/p/sqldf/
It is a framework sqldf over a framework rsqlite over sqlite. The data frames
the OP is talking about are variables that are coerced to being 'tables'. I can
see no mailing list for sqldf itself. The closet I could come is
https://stat.ethz.ch/mailman/listinfo/r-help
which seems to have quite a few discussions on sqldf and would probably be the
better place to ask this question.
>
> regards
> Pavel Stehule
>
> > regards, tom lane
> >
> > --
> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql
--
Adrian Klaver
aklaver@comcast.net
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Sqldf - error message
2009-11-20 14:18 Sqldf - error message Marvelde, Luc te <L.teMarvelde@nioo.knaw.nl>
2009-11-20 14:39 ` Re: Sqldf - error message Tom Lane <tgl@sss.pgh.pa.us>
2009-11-20 14:43 ` Re: Sqldf - error message Pavel Stehule <pavel.stehule@gmail.com>
2009-11-20 16:23 ` Re: Sqldf - error message Adrian Klaver <aklaver@comcast.net>
@ 2009-11-20 17:11 ` Whit Armstrong <armstrong.whit@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Whit Armstrong @ 2009-11-20 17:11 UTC (permalink / raw)
To: Marvelde, Luc te <L.teMarvelde@nioo.knaw.nl>; +Cc: Gabor Grothendieck <ggrothendieck@gmail.com>; pgsql-sql@postgresql.org, Pavel Stehule <pavel.stehule@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; Adrian Klaver <aklaver@comcast.net>
Luc,
You need to contact Gabor Grothendieck (cc'd) about this issue. He is
the package author. I'm sure he will be happy to help.
-Whit
On Fri, Nov 20, 2009 at 11:23 AM, Adrian Klaver <aklaver@comcast.net> wrote:
> On Friday 20 November 2009 6:43:53 am Pavel Stehule wrote:
>> 2009/11/20 Tom Lane <tgl@sss.pgh.pa.us>:
>> > "Marvelde, Luc te" <L.teMarvelde@nioo.knaw.nl> writes:
>> >> 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)
>> >
>> > It looks to me like sqldf is unaware of the rules about identifier
>> > case-folding in Postgres. That column would come back named
>> > "broedselid", but it's probably looking for "BroedselID".
>> > Or possibly it's expecting the qualifier "dbo_tbl_Broedsels."
>> > to be included in the returned column name. Either way, you
>> > need to bug sqldf's authors to fix it.
>>
>> is it Postgres? I see "Error in sqliteExecStatement"
>
> It is more complicated than that see:
> http://code.google.com/p/sqldf/
>
> It is a framework sqldf over a framework rsqlite over sqlite. The data frames
> the OP is talking about are variables that are coerced to being 'tables'. I can
> see no mailing list for sqldf itself. The closet I could come is
> https://stat.ethz.ch/mailman/listinfo/r-help
> which seems to have quite a few discussions on sqldf and would probably be the
> better place to ask this question.
>
>>
>> regards
>> Pavel Stehule
>>
>> > regards, tom lane
>> >
>> > --
>> > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> > To make changes to your subscription:
>> > http://www.postgresql.org/mailpref/pgsql-sql
>
>
>
> --
> Adrian Klaver
> aklaver@comcast.net
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2009-11-20 17:11 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-11-20 14:18 Sqldf - error message Marvelde, Luc te <L.teMarvelde@nioo.knaw.nl>
2009-11-20 14:37 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-11-20 14:42 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-11-20 14:50 ` Marvelde, Luc te <L.teMarvelde@nioo.knaw.nl>
2009-11-20 15:36 ` Rob Sargent <robjsargent@gmail.com>
2009-11-20 14:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-11-20 14:43 ` Pavel Stehule <pavel.stehule@gmail.com>
2009-11-20 16:23 ` Adrian Klaver <aklaver@comcast.net>
2009-11-20 17:11 ` Whit Armstrong <armstrong.whit@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox