You have
CREATE TABLE [User](
Email varchar(50),
Name varchar(50),
PIN int,
Phone varchar(50),
Address varchar(50),
Mileage int,
MealPref varchar(50),
PRIMARY KEY (Email));
that translates into this web2py model
db.define_table('User',
Field('Email','string',50),
Field('Name','string',50),
Field('PIN','integer'),
Field('Phone','string',50),
Field('Address','string',50),
Field('Mileage','integer'),
Field('MealPref','string',50),
primarykey=['Email'],
migrate=False
)
after which you can use the table in your controllers e.g.
rows=db(db.User.Email=='[email protected]).select()
For more info on keyed tables see
http://groups.google.com/group/web2py/browse_thread/thread/db150376b06d47fc
Denes
On Nov 13, 4:32 pm, Crim <[email protected]> wrote:
> you can down load the sql code herehttp://www.megaupload.com/?d=ARORZKPF
>
> no i cant use it .. so i take it i have to define the table in
> web2py?
>
> thanks a lot for your help ^ ^
>
> On Nov 13, 3:09 pm, DenesL <[email protected]> wrote:
>
> > Hi Crim,
>
> > if you can use db.user.email that means you have defined the table in
> > web2py.
> > As I understand it, as per your previous posts, that table was not
> > created with web2py so it must be a legacy table.
>
> > You can access it depending on two things:
> > 1) If the table has an auto incrementing field similar to web2py's id
> > field then you just define that field as type 'id' (don't forget to
> > set migrate=False as
> > perhttp://web2py.com/book/default/chapter/06#Legacy-Databases-and-Keyed-...).
> > 2) If there is no such field but the table has one or more fields that
> > are keys you can still access it using a primarykey definition (see
> > same section in the book as above).
>
> > There should be no need to use [dbo] or [master].[dbo] anywhere.
>
> > Posting your model would make it easier for others to help you.
>
> > Dens.
>
> > On Nov 13, 3:39 pm, Crim <[email protected]> wrote:
>
> > > thanks for the in-depth reply ^ ^
>
> > > im using existing tables and i master and another one stuff setup to
> > > do the same thing but this is a school project so im not to picky at
> > > the moment xD but thanks for the info for future use.
>
> > > Can i do the db(db.user.email == s).select() if i dont instantiate it
> > > in web2py? do i still have to define it? Considering the tables exist
> > > in the MSSql db?
>
> > > On Nov 13, 2:05 pm, Niphlod <[email protected]> wrote:
>
> > > > uhm .... are you using existing tables or you are using web2py to
> > > > create them ??
>
> > > > dbo shouldn't affect at all the query, but a few hints nonetheless :
>
> > > > master is a really nasty place to create tables .... "master" db
> > > > should be left untouched .... in web2py you define a database when you
> > > > istantiate db object, and all the query done on that db won't be able
> > > > to "see" other databases
>
> > > > that stated, if you have your connection string as
> > > > "mssql://username:passw...@localhost/master"
>
> > > > the query "select * from [master].[dbo].[User] where [master].[dbo].
> > > > [User]=s" should be accomplished doing:
>
> > > > db.define_table('user',
> > > > .....
> > > > Field('email', 'string'),
> > > > .....
> > > > )
>
> > > > and db(db.user.email == s).select()
>
> > > > anyway, I'm not sure (I don't have MSSQL installed to try out, but I
> > > > can reply you on monday at work) that there are a few places where
> > > > this implementation could not work:
>
> > > > - web2py creates tables and fields with lowercase letters, so "User"
> > > > get selectable only if database is set to be case insensitive
> > > > - "dbo" stands for "dbowner" and it represent the schema which the
> > > > object (in this case, a table) belongs. if your user has not the
> > > > "db_owner" role on the db you won't be able to "see" it
>
> > > > A few words also on this. Having objects beloging to different schemas
> > > > under the same database it's definitely an option, but web2py
> > > > (actually the library it uses, and many others like that) can't
> > > > "choose" different schemas... they simply ignore it and the database
> > > > defaults to whatever the default is for the user querying the database
> > > > itself.
> > > > You can have different schemas to separate between permissions on
> > > > different objects in the same database, but to avoid headaches it's
> > > > always better to create a separate user that "owns" a single database
> > > > and put every data inside it.
>
> > > > If the user in the connection string is db_owner of the database,
> > > > "select * from dbo.table" is perfectly equivalent to "select * from
> > > > table"
>
> > > > If you have any other question please ask, I'll be glad to test it out
> > > > on Monday
>
>
