Help! Table not found

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

Help! Table not found

Derek Wu
Hi All,

I'm new to LucidDB, so my question here might be pretty dumb.

I'm trying to build a small cube in mondrian on top of luciddb 0.9.2 to analyze jtrac work items. Assume we had:
set catalog 'LocalDB';
set schema 'jtrac';

create table fact_work_item_log (
    row_id int not null primary key,
    item_id int not null,
    ticket varchar(10) not null
);

When I tried execute the following query in SQuirreL SQL Client, I got an error saying Table 'fact_work_item_log' not found.
select * from "fact_work_item_log";

But it works if I remove quotation marks in above SQL. Looks like problem solved, right? Actually, no... In mondrian, I got the following automatically generated SQL:
DEBUG (SqlStatement.java:193) Segment.load: done executing sql [select
    max("fact_work_item_log"."budget") as "m0"
from
    "fact_work_item_log" as "fact_work_item_log"
], exec+fetch 31 ms, 0 rows
DEBUG (SqlStatement.java:144) 5: , failed (java.sql.SQLException: From line 4, column 5 to line 4, column 28: Table 'fact_work_item_log' not found)

Any suggestions? I'm really confused about the usage of quotation mark in LucidDB. It seems for local table, we should always avoid to use it, but for remote table it's a must.

Thanks,
Derek Wu

------------------------------------------------------------------------------
Throughout its 18-year history, RSA Conference consistently attracts the
world's best and brightest in the field, creating opportunities for Conference
attendees to learn about information security's most important issues through
interactions with peers, luminaries and emerging and established companies.
http://p.sf.net/sfu/rsaconf-dev2dev
_______________________________________________
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: Help! Table not found

Tom Barber
I'll take punt that if you remove " from the select statement it will work, the quotes make it case sensitive but your create table statement wasn't.

Tom



On Sat 16/01/10 8:43 AM , Derrek Wu [hidden email] sent:
Hi All,

I'm new to LucidDB, so my question here might be pretty dumb.

I'm trying to build a small cube in mondrian on top of luciddb 0.9.2 to analyze jtrac work items. Assume we had:
set catalog 'LocalDB';
set schema 'jtrac';

create table fact_work_item_log (
    row_id int not null primary key,
    item_id int not null,
    ticket varchar(10) not null
);

When I tried execute the following query in SQuirreL SQL Client, I got an error saying Table 'fact_work_item_log' not found.
select * from "fact_work_item_log";

But it works if I remove quotation marks in above SQL. Looks like problem solved, right? Actually, no... In mondrian, I got the following automatically generated SQL:
DEBUG (SqlStatement.java:193) Segment.load: done executing sql [select
    max("fact_work_item_log"."budget") as "m0"
from
    "fact_work_item_log" as "fact_work_item_log"
], exec+fetch 31 ms, 0 rows
DEBUG (SqlStatement.java:144) 5: , failed (java.sql.SQLException: From line 4, column 5 to line 4, column 28: Table 'fact_work_item_log' not found)

Any suggestions? I'm really confused about the usage of quotation mark in LucidDB. It seems for local table, we should always avoid to use it, but for remote table it's a must.

Thanks,
Derek Wu


------------------------------------------------------------------------------
Throughout its 18-year history, RSA Conference consistently attracts the
world's best and brightest in the field, creating opportunities for Conference
attendees to learn about information security's most important issues through
interactions with peers, luminaries and emerging and established companies.
http://p.sf.net/sfu/rsaconf-dev2dev
_______________________________________________
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: Help! Table not found

Derek Wu
In reply to this post by Derek Wu
Thank you very much Tom! Problem solved. I've got to say this is
really an unexpected difference with nowadays DBMS. I was about to
patch mondrian to generate "proper" query :p

Thanks,
Derek Wu

> I'll take punt that if you remove " from the select statement it will work, the quotes make it case sensitive but your create table statement wasn't.
>
> Tom
>
> On Sat, Jan 16, 2010 at 4:43 PM, Derrek Wu <[hidden email]> wrote:
> > Hi All,
> >
> > I'm new to LucidDB, so my question here might be pretty dumb.
> >
> > I'm trying to build a small cube in mondrian on top of luciddb 0.9.2 to
> > analyze jtrac work items. Assume we had:
> > set catalog 'LocalDB';
> > set schema 'jtrac';
> >
> > create table fact_work_item_log (
> >     row_id int not null primary key,
> >     item_id int not null,
> >     ticket varchar(10) not null
> > );
> >
> > When I tried execute the following query in SQuirreL SQL Client, I got an
> > error saying Table 'fact_work_item_log' not found.
> > select * from "fact_work_item_log";
> >
> > But it works if I remove quotation marks in above SQL. Looks like problem
> > solved, right? Actually, no... In mondrian, I got the following
> > automatically generated SQL:
> > DEBUG (SqlStatement.java:193) Segment.load: done executing sql [select
> >     max("fact_work_item_log"."budget") as "m0"
> > from
> >     "fact_work_item_log" as "fact_work_item_log"
> > ], exec+fetch 31 ms, 0 rows
> > DEBUG (SqlStatement.java:144) 5: , failed (java.sql.SQLException: From line
> > 4, column 5 to line 4, column 28: Table 'fact_work_item_log' not found)
> >
> > Any suggestions? I'm really confused about the usage of quotation mark in
> > LucidDB. It seems for local table, we should always avoid to use it, but for
> > remote table it's a must.
> >
> > Thanks,
> > Derek Wu

------------------------------------------------------------------------------
Throughout its 18-year history, RSA Conference consistently attracts the
world's best and brightest in the field, creating opportunities for Conference
attendees to learn about information security's most important issues through
interactions with peers, luminaries and emerging and established companies.
http://p.sf.net/sfu/rsaconf-dev2dev
_______________________________________________
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: Help! Table not found

ngoodman
Derek,

If you leave quotes off, LucidDB will UPPERCASE identifiers.

select mycol from mytable = select MYCOL from MYTABLE

Mondrian will always send queries with quoted identifiers, so whatever you put in the Mondrian definition will be exactly what is requested, case sensitivity and all.

I'd suggest you just take great care with table/column names when creating tables, including case:  create table "MyTableName" ( "ColumnOneName" int).  In Mondrian, you can use that exact case ("MyTableName"/"ColumnName").

Nick
On Jan 16, 2010, at 5:56 AM, Derrek Wu wrote:

> Thank you very much Tom! Problem solved. I've got to say this is
> really an unexpected difference with nowadays DBMS. I was about to
> patch mondrian to generate "proper" query :p
>
> Thanks,
> Derek Wu
>
>> I'll take punt that if you remove " from the select statement it will work, the quotes make it case sensitive but your create table statement wasn't.
>>
>> Tom
>>
>> On Sat, Jan 16, 2010 at 4:43 PM, Derrek Wu <[hidden email]> wrote:
>>> Hi All,
>>>
>>> I'm new to LucidDB, so my question here might be pretty dumb.
>>>
>>> I'm trying to build a small cube in mondrian on top of luciddb 0.9.2 to
>>> analyze jtrac work items. Assume we had:
>>> set catalog 'LocalDB';
>>> set schema 'jtrac';
>>>
>>> create table fact_work_item_log (
>>>     row_id int not null primary key,
>>>     item_id int not null,
>>>     ticket varchar(10) not null
>>> );
>>>
>>> When I tried execute the following query in SQuirreL SQL Client, I got an
>>> error saying Table 'fact_work_item_log' not found.
>>> select * from "fact_work_item_log";
>>>
>>> But it works if I remove quotation marks in above SQL. Looks like problem
>>> solved, right? Actually, no... In mondrian, I got the following
>>> automatically generated SQL:
>>> DEBUG (SqlStatement.java:193) Segment.load: done executing sql [select
>>>     max("fact_work_item_log"."budget") as "m0"
>>> from
>>>     "fact_work_item_log" as "fact_work_item_log"
>>> ], exec+fetch 31 ms, 0 rows
>>> DEBUG (SqlStatement.java:144) 5: , failed (java.sql.SQLException: From line
>>> 4, column 5 to line 4, column 28: Table 'fact_work_item_log' not found)
>>>
>>> Any suggestions? I'm really confused about the usage of quotation mark in
>>> LucidDB. It seems for local table, we should always avoid to use it, but for
>>> remote table it's a must.
>>>
>>> Thanks,
>>> Derek Wu
>
> ------------------------------------------------------------------------------
> Throughout its 18-year history, RSA Conference consistently attracts the
> world's best and brightest in the field, creating opportunities for Conference
> attendees to learn about information security's most important issues through
> interactions with peers, luminaries and emerging and established companies.
> http://p.sf.net/sfu/rsaconf-dev2dev
> _______________________________________________
> luciddb-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/luciddb-users


------------------------------------------------------------------------------
Throughout its 18-year history, RSA Conference consistently attracts the
world's best and brightest in the field, creating opportunities for Conference
attendees to learn about information security's most important issues through
interactions with peers, luminaries and emerging and established companies.
http://p.sf.net/sfu/rsaconf-dev2dev
_______________________________________________
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: Help! Table not found

Derek Wu
Thanks Nick for your detailed explaination. I changed DDL based on
Tom's hint. So the table I mentioned earlier becomes:
---
create table "fact_work_item_log" (
   "row_id" int not null primary key,
   "item_id" int not null,
   "ticket" varchar(10) not null
);
---
As you may know, that did take me some time to add quotes in all my
DDL and DML. Never mind, I'll get used to it :p

Anyway, I like LucidDB, especially the ability to link varies datasources.

Thanks,
Derek

On Sun, Jan 17, 2010 at 2:58 AM, Nicholas Goodman <[hidden email]> wrote:

> Derek,
>
> If you leave quotes off, LucidDB will UPPERCASE identifiers.
>
> select mycol from mytable = select MYCOL from MYTABLE
>
> Mondrian will always send queries with quoted identifiers, so whatever you put in the Mondrian definition will be exactly what is requested, case sensitivity and all.
>
> I'd suggest you just take great care with table/column names when creating tables, including case:  create table "MyTableName" ( "ColumnOneName" int).  In Mondrian, you can use that exact case ("MyTableName"/"ColumnName").
>
> Nick
> On Jan 16, 2010, at 5:56 AM, Derrek Wu wrote:
>
>> Thank you very much Tom! Problem solved. I've got to say this is
>> really an unexpected difference with nowadays DBMS. I was about to
>> patch mondrian to generate "proper" query :p
>>
>> Thanks,
>> Derek Wu
>>
>>> I'll take punt that if you remove " from the select statement it will work, the quotes make it case sensitive but your create table statement wasn't.
>>>
>>> Tom
>>>
>>> On Sat, Jan 16, 2010 at 4:43 PM, Derrek Wu <[hidden email]> wrote:
>>>> Hi All,
>>>>
>>>> I'm new to LucidDB, so my question here might be pretty dumb.
>>>>
>>>> I'm trying to build a small cube in mondrian on top of luciddb 0.9.2 to
>>>> analyze jtrac work items. Assume we had:
>>>> set catalog 'LocalDB';
>>>> set schema 'jtrac';
>>>>
>>>> create table fact_work_item_log (
>>>>     row_id int not null primary key,
>>>>     item_id int not null,
>>>>     ticket varchar(10) not null
>>>> );
>>>>
>>>> When I tried execute the following query in SQuirreL SQL Client, I got an
>>>> error saying Table 'fact_work_item_log' not found.
>>>> select * from "fact_work_item_log";
>>>>
>>>> But it works if I remove quotation marks in above SQL. Looks like problem
>>>> solved, right? Actually, no... In mondrian, I got the following
>>>> automatically generated SQL:
>>>> DEBUG (SqlStatement.java:193) Segment.load: done executing sql [select
>>>>     max("fact_work_item_log"."budget") as "m0"
>>>> from
>>>>     "fact_work_item_log" as "fact_work_item_log"
>>>> ], exec+fetch 31 ms, 0 rows
>>>> DEBUG (SqlStatement.java:144) 5: , failed (java.sql.SQLException: From line
>>>> 4, column 5 to line 4, column 28: Table 'fact_work_item_log' not found)
>>>>
>>>> Any suggestions? I'm really confused about the usage of quotation mark in
>>>> LucidDB. It seems for local table, we should always avoid to use it, but for
>>>> remote table it's a must.
>>>>
>>>> Thanks,
>>>> Derek Wu
>>
>> ------------------------------------------------------------------------------
>> Throughout its 18-year history, RSA Conference consistently attracts the
>> world's best and brightest in the field, creating opportunities for Conference
>> attendees to learn about information security's most important issues through
>> interactions with peers, luminaries and emerging and established companies.
>> http://p.sf.net/sfu/rsaconf-dev2dev
>> _______________________________________________
>> luciddb-users mailing list
>> [hidden email]
>> https://lists.sourceforge.net/lists/listinfo/luciddb-users
>
>
> ------------------------------------------------------------------------------
> Throughout its 18-year history, RSA Conference consistently attracts the
> world's best and brightest in the field, creating opportunities for Conference
> attendees to learn about information security's most important issues through
> interactions with peers, luminaries and emerging and established companies.
> http://p.sf.net/sfu/rsaconf-dev2dev
> _______________________________________________
> luciddb-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/luciddb-users
>

------------------------------------------------------------------------------
Throughout its 18-year history, RSA Conference consistently attracts the
world's best and brightest in the field, creating opportunities for Conference
attendees to learn about information security's most important issues through
interactions with peers, luminaries and emerging and established companies.
http://p.sf.net/sfu/rsaconf-dev2dev
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Loading...