db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Pannozzi <jimse...@gmail.com>
Subject Re: how to use schema names , it allways return error for me.
Date Sun, 06 Nov 2005 16:40:08 GMT
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 <legolas.w@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.v.lunteren@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
> >

View raw message