select foreign table got error "Column 'BILL_ID' not found in any table"

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

select foreign table got error "Column 'BILL_ID' not found in any table"

lynn_19840516
Hi,

Firstly, I'd like to thank LucidDB's developers! The performance is
mind-blowing, especially coming from a conventional RDBMS.

Here's my problem: when I select foreign table then I got "Column 'BILL_ID' not found in any table";

This is more of a minor annoyance than a real issue, but I'd like to
know whether my create server/create table commands were at fault.
Here's what I entered initially:

create or replace server psg_vitalink_server
foreign data wrapper sys_jdbc
options(
    driver_class 'org.postgresql.Driver',
    url 'jdbc:postgresql://127.0.0.1:5432/vitalink',
    user_name 'TEST',
    password '123',
    validation_query 'select 1');
   
create foreign table bills
server psg_vitalink_server
options(schema_name 'test', table_name 'bills');    

0: jdbc:luciddb:http://localhost> select * from bills;
+----------+-------+--------+--------------+------------+---------+------------+

| bill_id  | type  | state  | from_entity  | to_entity  | holder  | approver   |

+----------+-------+--------+--------------+------------+---------+------------+

| 1247     | CODC  | 0      | 643          | 1          | 643     |            |

| 1290     | CODC  | 20     | 666          | 1          | 9       | 3          |

+----------+-------+--------+--------------+------------+---------+------------+

45 rows selected (0.578 seconds)
0: jdbc:luciddb:http://localhost> SELECT * from bills where bill_id =1;
Error: From line 1, column 27 to line 1, column 33: Column 'BILL_ID' not found i
n any table (state=,code=0)
0: jdbc:luciddb:http://localhost>


I'm using LucidDB 0.9.3, POSTGRESQL 8.4, and the JDBC driver version is postgresql-8.4-701.jdbc3.jar
Ceiwa Zhang
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: select foreign table got error "Column 'BILL_ID' not found in any table"

ngoodman

On Jan 5, 2011, at 2:44 AM, lynn_19840516 wrote:

> Firstly, I'd like to thank LucidDB's developers! The performance is
> mind-blowing, especially coming from a conventional RDBMS.
We're glad you like it!  When you get to the end of your initial project would you be willing to share some of these "mind-blowing" stats compared to your original RDBMS?  We're trying to collect this information so potential users can see what kind of "real world" improvements.

> 0: jdbc:luciddb:http://localhost> select * from bills;
> +----------+-------+--------+--------------+------------+---------+------------+
>
> | bill_id  | type  | state  | from_entity  | to_entity  | holder  | approver  
> 0: jdbc:luciddb:http://localhost> SELECT * from bills where bill_id =1;
> Error: From line 1, column 27 to line 1, column 33: Column 'BILL_ID' not
> found i
> n any table (state=,code=0)

ANSI standard dictates that any unquoted identifier (ie, bills in your case) is UPPERCASED and then evaluated.  Postgres, which respects case, has the column defined "lower" case (ie, bills).  You can change your query to:

> select * from bills where "bill_id" = 1;

Which will prevent LucidDB from uppercasing the identifier.

I'd recommend any tables you create in LucidDB you should create with uppercase identifiers.  Foreign data sources, such as Postgres, the case will come from the remote database so you'll just have to deal with whatever case the database had originally.

Good luck, and let us know how you get on!

Nick
PS - You can also use this FAQ for some other common issues you may (or may not) encounter:
http://pub.eigenbase.org/wiki/LucidDbUserFaq#Missing_Columns
------------------------------------------------------------------------------
Learn how Oracle Real Application Clusters (RAC) One Node allows customers
to consolidate database storage, standardize their database environment, and,
should the need arise, upgrade to a full multi-node Oracle RAC database
without downtime or disruption
http://p.sf.net/sfu/oracle-sfdevnl
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: select foreign table got error "Column 'BILL_ID' not found in any table"

lynn_19840516
Thanks Nick for your detailed explaination. Problem solved.
Ceiwa Zhang
Loading...