You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Legolas Woodland <le...@gmail.com> on 2005/11/06 00:04:35 UTC
how to use schema names , it allways return error for me.
hi
Thank you for reading my post.
i tried to execute following statement but it return the exception that the
table does not exists ,
i tried several cobination of " and ' around the schema and table name with
no luck.
"INSERT INTO APP.table2 ( data, data2 ) VALUES ( ?, ? )"
This statement return
Table 'APP.TABLE2' does not exist.
can some one help me ?
is there any way to pass schema name in connection url ? or set the schema
to a default one?
Re: how to use schema names , it allways return error for me.
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Legolas Woodland wrote (2005-11-06 14:15:56):
> Thank you for reply
> I found a part of problem.
> 1-SELECT field1, field2 FROM APP.table2
> the above statement will never works , i tried to execute it and it return
> no table found.
> but the followiing statement works OK .
> 2-select * from "APP"."table2"
> as i test them in netbeans IDE SQL designer , now i do not know how i can
> have something like 2 as an String variable to pass it to sql Statement or
> preparedStatement.
> can some one help me about it ?
SQL is case-insensitive, and everything is converted to upper case,
unless quoted. Thus
app.table2 is converted to APP.TABLE2, but "app"."table2" will remain
as is. In your case, the scheman name is APP (which may be written
app), but the table name is "table2". So, app."table2" will also work.
In Java, you can write e.g.
prepareStatement("select * from app.\"table2\"");
--
Bernt Marius Johnsen, Database Technology Group,
Sun Microsystems, Trondheim, Norway
Re: how to use schema names , it allways return error for me.
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Legolas Woodland wrote (2005-11-06 14:15:56):
> Thank you for reply
> I found a part of problem.
> 1-SELECT field1, field2 FROM APP.table2
> the above statement will never works , i tried to execute it and it return
> no table found.
> but the followiing statement works OK .
> 2-select * from "APP"."table2"
> as i test them in netbeans IDE SQL designer , now i do not know how i can
> have something like 2 as an String variable to pass it to sql Statement or
> preparedStatement.
> can some one help me about it ?
SQL is case-insensitive, and everything is converted to upper case,
unless quoted. Thus
app.table2 is converted to APP.TABLE2, but "app"."table2" will remain
as is. In your case, the scheman name is APP (which may be written
app), but the table name is "table2". So, app."table2" will also work.
In Java, you can write e.g.
prepareStatement("select * from app.\"table2\"");
--
Bernt Marius Johnsen, Database Technology Group,
Sun Microsystems, Trondheim, Norway
Re: how to use schema names , it allways return error for me.
Posted by Legolas Woodland <le...@gmail.com>.
Thank you for reply
I found a part of problem.
1-SELECT field1, field2 FROM APP.table2
the above statement will never works , i tried to execute it and it return
no table found.
but the followiing statement works OK .
2-select * from "APP"."table2"
as i test them in netbeans IDE SQL designer , now i do not know how i can
have something like 2 as an String variable to pass it to sql Statement or
preparedStatement.
can some one help me about it ?
On 11/6/05, Michael Segel <ms...@segel.com> wrote:
>
> On Saturday 05 November 2005 17:04, Legolas Woodland wrote:
> > hi
> > Thank you for reading my post.
> > i tried to execute following statement but it return the exception that
> the
> > table does not exists ,
> > i tried several cobination of " and ' around the schema and table name
> with
> > no luck.
> > "INSERT INTO APP.table2 ( data, data2 ) VALUES ( ?, ? )"
> > This statement return
> > Table 'APP.TABLE2' does not exist.
> > can some one help me ?
> > is there any way to pass schema name in connection url ? or set the
> schema
> > to a default one?
>
> Well, you have SET SCHEMA (see page 38 of Ref Manual)
>
> Of course you may want to query your sysscheamas table to see the schema
> names
> and also check to see that the table does exist in the schema that you
> think
> it exists in...
>
> HTH.
>
> --
> Michael Segel
> Principal
> MSCC
>
Re: how to use schema names , it allways return error for me.
Posted by Michael Segel <ms...@segel.com>.
On Saturday 05 November 2005 17:04, Legolas Woodland wrote:
> hi
> Thank you for reading my post.
> i tried to execute following statement but it return the exception that the
> table does not exists ,
> i tried several cobination of " and ' around the schema and table name with
> no luck.
> "INSERT INTO APP.table2 ( data, data2 ) VALUES ( ?, ? )"
> This statement return
> Table 'APP.TABLE2' does not exist.
> can some one help me ?
> is there any way to pass schema name in connection url ? or set the schema
> to a default one?
Well, you have SET SCHEMA (see page 38 of Ref Manual)
Of course you may want to query your sysscheamas table to see the schema names
and also check to see that the table does exist in the schema that you think
it exists in...
HTH.
--
Michael Segel
Principal
MSCC
Re: how to use schema names , it allways return error for me.
Posted by James Pannozzi <ji...@gmail.com>.
Myrna van Lunteren wrote:
>You can select things from sys.systables joined with sysschemas to see in
which schema your table2 ended up. something like this:
>select t.tablename, s.schemaname from sys.systables t, sys.sysschemas s
where t.schemaid=s.schemaid;
Many thanks for the intel and for this technique which I will use
should I goof up and enter that problem domain
again.
Jim Pannozzi
On 11/5/05, Myrna van Lunteren <m....@gmail.com> wrote:
>
> On 11/5/05, Legolas Woodland <le...@gmail.com> wrote:
> >
> > hi
> > Thank you for reading my post.
> > i tried to execute following statement but it return the exception that
> > the table does not exists ,
> > i tried several cobination of " and ' around the schema and table name
> > with no luck.
> > "INSERT INTO APP.table2 ( data, data2 ) VALUES ( ?, ? )"
> > This statement return
> > Table 'APP.TABLE2' does not exist.
> > can some one help me ?
> > is there any way to pass schema name in connection url ? or set the
> > schema to a default one?
> >
> --
> Your table2 is probably not in the APP schema like you expect.
> If you log on using a username, that username is assumed to be the desired
> schema for things to get created in.
> If you do not want that to be your schema, you need to explicitly switch
> to another schema (and possibly create it first), or explicitly qualify each
> create with the intended schemaname.
> Else, if you create a table (not necessarily other database objects, but I
> think that was a bug), a new schema will automatically be created.
> APP is the default schema, it always exists.
> set schema schemaname is the ij SQL to set/switch to a named schema.
> create schema schemaname would explicitly create a non-existing schema
> You can select things from sys.systables joined with sysschemas to see in
> which schema your table2 ended up. something like this:
> select t.tablename, s.schemaname from sys.systables t, sys.sysschemas s
> where t.schemaid=s.schemaid;
> Myrna
>
Re: how to use schema names , it allways return error for me.
Posted by James Pannozzi <ji...@gmail.com>.
I had exactly the same problem on one of the test
databases that I created, dbtest.
I first created a new database in ij with:
connect 'jdbc:derby:dbtest;create=true;user=usr;password=pwd';
and then created a table with:
create table Races(race varchar(50));
(why the heck is Firefox giving me this tiny
font, I feel like a liliputian, oh well,
to continue...)
and then I populated the table
with a few test entries:
insert into Races values ('Dwarf');
insert into Races values ('Gnome');
insert into Races values ('Human');
insert into Races values ('Night Elf');
insert into Races values ('Orc');
insert into Races values ('Undead');
insert into Races values ('Tauren');
Then I used the sql command from ij
to make sure everything was there:
select * from Races;
and all appeared OK.
A few days later, I tried referencing the database
from PHP.
(I initially had a bug in my PHP configuation
and could not get db2_connect to work
but I eventually found and fixed that problem.)
After I had PHP and the db2_ functions working
OK, I could no longer reference the "Races"
table mentioned above. I tried logging in
as "usr" and using password "pwd"
and it kept saying table Races not found
or undefined table or some such.
But,mysteriously, when I went back to ij, it worked
and showed me the table entries.
I tried dblook by giving the command:
java org.apache.derby.tools.dblook -d jdbc:derby:dbtest;
to see what schema names
were given and the schema name was "USR"
so I tried referencing the table as usr.Races
instead of just Races
in my PHP and it still did not work.
So I rebuilt the database exactly as before but
this time in new database called dbtest1.
After I did this, everything worked OK
and table name was recognized from PHP.
Why it would not work in the first test database
I tried, I don't know. I wonder if somehow it
got corrupted when I was trying to
reference it from PHP when my config
was not right, but, anyway, I had the same
problem as Legolas but it has not happened
again in any of the other test databases
that I built.
Jim Pannozzi
On 11/6/05, Legolas Woodland <le...@gmail.com> wrote:
>
> Hi
> Thank you for reply .
> I checked again and the schema exists and table with same name exist with
> two fields.
> my problem is that , how we should specify table and fields name in derby
> , when we write sql command ,
> can i use Table2 alone to access the table ? or using schema name is
> manedatory ?
>
>
> On 11/6/05, Myrna van Lunteren <m....@gmail.com> wrote:
> >
> > On 11/5/05, Legolas Woodland <legolas.w@gmail.com > wrote:
> > >
> > > hi
> > > Thank you for reading my post.
> > > i tried to execute following statement but it return the exception
> > > that the table does not exists ,
> > > i tried several cobination of " and ' around the schema and table name
> > > with no luck.
> > > "INSERT INTO APP.table2 ( data, data2 ) VALUES ( ?, ? )"
> > > This statement return
> > > Table 'APP.TABLE2' does not exist.
> > > can some one help me ?
> > > is there any way to pass schema name in connection url ? or set the
> > > schema to a default one?
> > >
> > --
> > Your table2 is probably not in the APP schema like you expect.
> > If you log on using a username, that username is assumed to be thedesired schema
> > for things to get created in.
> > If you do not want that to be your schema, you need to explicitly switch
> > to another schema (and possibly create it first), or explicitly qualify
> > each create with the intended schemaname.
> > Else, if you create a table (not necessarily other database objects, but
> > I think that was a bug), a new schema will automatically be created.
> > APP is the default schema, it always exists.
> > set schema schemaname is the ij SQL to set/switch to a named schema.
> > create schema schemaname would explicitly create a non-existing schema
> > You can select things from sys.systables joined with sysschemas to see
> > in which schema your table2 ended up. something like this:
> > select t.tablename, s.schemaname from sys.systables t, sys.sysschemas s
> > where t.schemaid=s.schemaid;
> > Myrna
> >
>
>
Re: how to use schema names , it allways return error for me.
Posted by Legolas Woodland <le...@gmail.com>.
Hi
Thank you for reply .
I checked again and the schema exists and table with same name exist with
two fields.
my problem is that , how we should specify table and fields name in derby ,
when we write sql command ,
can i use Table2 alone to access the table ? or using schema name is
manedatory ?
On 11/6/05, Myrna van Lunteren <m....@gmail.com> wrote:
>
> On 11/5/05, Legolas Woodland <le...@gmail.com> wrote:
> >
> > hi
> > Thank you for reading my post.
> > i tried to execute following statement but it return the exception that
> > the table does not exists ,
> > i tried several cobination of " and ' around the schema and table name
> > with no luck.
> > "INSERT INTO APP.table2 ( data, data2 ) VALUES ( ?, ? )"
> > This statement return
> > Table 'APP.TABLE2' does not exist.
> > can some one help me ?
> > is there any way to pass schema name in connection url ? or set the
> > schema to a default one?
> >
> --
> Your table2 is probably not in the APP schema like you expect.
> If you log on using a username, that username is assumed to be the desired
> schema for things to get created in.
> If you do not want that to be your schema, you need to explicitly switch
> to another schema (and possibly create it first), or explicitly qualify
> each create with the intended schemaname.
> Else, if you create a table (not necessarily other database objects, but I
> think that was a bug), a new schema will automatically be created.
> APP is the default schema, it always exists.
> set schema schemaname is the ij SQL to set/switch to a named schema.
> create schema schemaname would explicitly create a non-existing schema
> You can select things from sys.systables joined with sysschemas to see in
> which schema your table2 ended up. something like this:
> select t.tablename, s.schemaname from sys.systables t, sys.sysschemas s
> where t.schemaid=s.schemaid;
> Myrna
>
Re: how to use schema names , it allways return error for me.
Posted by Myrna van Lunteren <m....@gmail.com>.
On 11/5/05, Legolas Woodland <le...@gmail.com> wrote:
>
> hi
> Thank you for reading my post.
> i tried to execute following statement but it return the exception that
> the table does not exists ,
> i tried several cobination of " and ' around the schema and table name
> with no luck.
> "INSERT INTO APP.table2 ( data, data2 ) VALUES ( ?, ? )"
> This statement return
> Table 'APP.TABLE2' does not exist.
> can some one help me ?
> is there any way to pass schema name in connection url ? or set the
> schema to a default one?
>
--
Your table2 is probably not in the APP schema like you expect.
If you log on using a username, that username is assumed to be the desired
schema for things to get created in.
If you do not want that to be your schema, you need to explicitly switch to
another schema (and possibly create it first), or explicitly qualify each
create with the intended schemaname.
Else, if you create a table (not necessarily other database objects, but I
think that was a bug), a new schema will automatically be created.
APP is the default schema, it always exists.
set schema schemaname is the ij SQL to set/switch to a named schema.
create schema schemaname would explicitly create a non-existing schema
You can select things from sys.systables joined with sysschemas to see in
which schema your table2 ended up. something like this:
select t.tablename, s.schemaname from sys.systables t, sys.sysschemas s
where t.schemaid=s.schemaid;
Myrna