Mrelation does not exist

[William Carpin]

Okay I originally posted the following in the rails forum, which was the
wrong place to post it as I'm not using Rails (that thread just happens
to be what came up in Google). Perhaps one of you could help me

C:\Documents and Settings\Will\My
Documents\src\mm\web\cgi-bin>processBounces.rb

Connection Test Successful - Connected To The Database
Parsing Logfile Finished
Processing Bounces...
select id, bounce from sql_languages where
email='(e-mail address removed)';
c:/ruby/lib/ruby/site_ruby/1.8/DBD/Pg/Pg.rb:640:in `execute': ERROR
C42P01
Mrelation "sql_languages" does not exist Fnamespace.c L201
RRangeVa
rGetRelid (DBI:rogrammingError)
from c:/ruby/lib/ruby/site_ruby/1.8/dbi.rb:617:in `execute'
from C:/Documents and Settings/Will/My
Documents/src/mm/web/cgi-bin/proc
essBounces.rb:84:in `updateBounceCount'
from C:/Documents and Settings/Will/My
Documents/src/mm/web/cgi-bin/proc
essBounces.rb:79:in `updateBounceCount'
from C:/Documents and Settings/Will/My
Documents/src/mm/web/cgi-bin/proc
essBounces.rb:144
from C:/Documents and Settings/Will/My
Documents/src/mm/web/cgi-bin/proc
essBounces.rb:142



Roughly the same issue, but it doesn't seem to be an issue with the
serial.... any ideas?

The code is as follows:

data_sources.each do |table_name|
email_address = email_address.downcase
selectQry = "select id, bounce from "+table_name+" where
email=\'"+email_address+"\'\;"

sth = dbh.prepare(selectQry)
sth.execute

while rows = sth.fetch do
printf rows[0], rows[1],"\n"
end
end

 

[William Carpin]

I should also note that it's a Postgres-based database that I'm
connecting to. I've scoured the internet, but everything has lead to
dead ends.



William Carpin wrote:
\src\mm\web\cgi-bin>processBounces.rb

 

[William Crawford]

select id, bounce from sql_languages where
email='(e-mail address removed)';
c:/ruby/lib/ruby/site_ruby/1.8/DBD/Pg/Pg.rb:640:in `execute': ERROR
C42P01
Mrelation "sql_languages" does not exist Fnamespace.c L201
RRangeVa

And of course, the table 'sql_languages' DOES exist, right?

 

[William Carpin]

And of course, the table 'sql_languages' DOES exist, right?

Yes, the table 'sql_languages' does exist. You don't have to try to be
a smart allack about it. I'm teaching myself this language and just
wanted some help.

It's Postgres. If you aren't familiar with it, each database contains
various schemas. sql_languages is in the "information_schema" schema.

 

[William Crawford]

Yes, the table 'sql_languages' does exist. You don't have to try to be
a smart allack about it. I'm teaching myself this language and just
wanted some help.

It's Postgres. If you aren't familiar with it, each database contains
various schemas. sql_languages is in the "information_schema" schema.

I'm not being a smart aleck about it. You'd be amazed how often it's
something simple that was just overlooked.

 

[William Carpin]

I'm not being a smart aleck about it. You'd be amazed how often it's
something simple that was just overlooked.

Oh, okay. Sorry about that, i misjudged your tone!

But yeah, essentially I am using Ruby's DBI module as such
(simplified)...

dbh = DBI.connect("DBIg:mavmail:192.168.1.19", "mavmail", "")
data_sources = dbh.tables

...the above should return an array of table names, which it indeed
does. It's including the table names for not only the public tables,
but the ones stored in the information schema as well. Then I do the
following:

data_sources.each{ do |table_name|
sth = dbh.prepare("SELECT something from "+table_name)
sth.execute
end

...and then outputs the error noted above. I believe it's crapping out
because it's running the query against non-public tables, so it can't
'see' them. Some weird quirk with Postgres as far as I can tell. If I
could return only the public tables then it'd make my life easier...
it's back to the drawing board I guess.

 

[William Crawford]

dbh = DBI.connect("DBIg:mavmail:192.168.1.19", "mavmail", "")
data_sources = dbh.tables

...the above should return an array of table names, which it indeed
does. It's including the table names for not only the public tables,
but the ones stored in the information schema as well. Then I do the
following:

data_sources.each{ do |table_name|
sth = dbh.prepare("SELECT something from "+table_name)
sth.execute
end

...and then outputs the error noted above. I believe it's crapping out
because it's running the query against non-public tables, so it can't
'see' them. Some weird quirk with Postgres as far as I can tell. If I
could return only the public tables then it'd make my life easier...
it's back to the drawing board I guess.

Hm, yeah, that's not very helpful. It doesn't seem to provide a way to
tell what schema it's in or anything. If you could, you could simply do
schema.table_name and it would pull it like you want.

It looks like it just pulls the list of tables by doing:
select tablename from pg_catalog.pg_tables;

If you pulled the information yourself, you could choose only the schema
'public' and get what you want.