Database example with more than one table

[Brian]

Hi,

Has anyone got an example of a database with at least 2 tables I can have a look at?

I've no problems seeing the data from one table, it's when I try to look at a second
or third table that it all goes to pot!

Many thanks,

Brian

[Rock Ridge Farm (Larry)]

Check this one:
http://www.ionicwind.com/forums/index.php/topic,701.0.html
If it is not there let me know - I have another one that I know does 2 tables.

[LarryMc]

Brian,
post your code and I'll see if I can help you

[Brian]

Larry,

Downloaded your example, thanks. I'll have a look, and if I don't understand it, I'll post my code

Thanks again,

Brian

[acelli]

Brian,

I think it depends on the driver.  I have consistently had problems with Oracle but your post inspired me try again. 

Tonight I discovered that with Microsoft's driver it won't work at all but with Oracle's driver it reports an error but will work.  Two tables TEST1 and TEST2, three fields FNAME, LNAME, ZIP.  TEST1 has one record.  TEST2 has two.  Find the code below.  Both drivers return errors but the Oracle does return the data (see picture). 

Let me know your experience.

Thanks,
Alan

Code Select Expand



OPENCONSOLE

DEF SQL[1000]:ISTRING

DEF pdb as POINTER
DEF hstmt:INT
DEF err:STRING
DEF count:int
DEF last,first as STRING


'pdb = dbConnect("Microsoft ODBC for Oracle","","Driver={Microsoft ODBC for Oracle};Server=XE;Uid=<userid>;Pwd=<password>")

pdb = dbConnect("Oracle in XE","","Driver={Oracle in XE};Server=XE;Uid=<userid>;Pwd=<password>")


IF pdb <> NULL THEN
    PRINT "ODBC Connection Established"
    PRINT

sql = " select a.fname ,b.lname from test1 a,test2 b where a.zip=b.zip "
    PRINT sql
    PRINT

             hstmt = dbExecSQL(pdb,SQL)
             err = dbGetErrorCode(hstmt)                                                       
'            IF LEN(err)                                                                       
'               PRINT                                                                         
              PRINT "Error Code: ", err                                                     
              PRINT "Error Text: ", dbGetErrorText(hstmt)                                   
              PRINT                                                                         
'                   ELSE 
    dbBindVariable(hstmt, 1, first)
    dbBindVariable(hstmt, 2, last)
    count=0
                     WHILE dbGet(hstmt)         
                       count++
    PRINT last, ", ", first
                     WEND 
                PRINT                   
                PRINT "COUNT ",count
'    ENDIF                   


    dbFreeSQL(hstmt)                                                                                                                                               
ELSE
    PRINT "No Connection Established"
ENDIF
dbDisconnect(pdb)


PRINT
PRINT "Press any key to exit"
DO:UNTIL INKEY$ <> ""


CLOSECONSOLE
END




[Brian]

Hi,

Thanks for for your replies - I'm really stuck now!

What I need to do is:
The Details table holds all the names and addresses of clients
The Info table holds all the book titles that a client may purchase
The BookShop table holds information that a user puts in when calling a client

So . . .

When a client's details are displayed, it should get the book titles from the Info table, and
also displays all the call details that the user has typed in

Obviously, the tables need to be linked together somehow, and when a particular client
is displayed, get that client's information from the Info and BookShop tables, but I have
no idea how to go about it

On a further note, when the window is being resized, I'm getting a flickering round the
edges, and the listview with the book title information doesn't get resized properly. Any ideas?

All help very gratefully received

Thanks a lot,

Brian

[Rock Ridge Farm (Larry)]

Create a unique id for each client - use that as the primary key for each table then once you look up  a client
you have the key to find the associated data in each of the other tables.

[peterpuk]

Hi Brian,

I'm not sure if this will help but I will try to explain how to link tables. Database design and maintenance can be a real pain if not carefully thought out from the start. Many, many years ago I used to link tables with Account numbers and Stock codes, and ran into numerous maintenance difficulties. All my problems were resolved by using the 'record ids' that Larry mentioned above.

In general, when creating a database with tables that need to be linked in some way, it is common practise to use Record IDs as the link. These Record IDs are usually the autonumber field that you include in the table, AND you usually only have one defined in the table. I see that you have done this correctly, with the 'id' being the first field in each Table.

So you have a unique id in the INFO Table that refers to each book title, and you have a unique id in the DETAILS Table that refers to each client.

If each record in the BOOKSHOP Table has a link to the 'client', and a link to the 'title', then you should store the client and title ids in the BOOKSHOP record (which you have not done). Your BOOKSHOP Table should look something like

id   AutoNumber
detailsID   long Integer
infoID   long Integer
calldate   Date/Time
contact   Text 64
shoptitles   Text 64
stocklevel   Text 7
ordered   Text 2
quantity   Text 7
comments   text 255

Note that the detailsID and infoID fields are not AutoNumbers. You will simply store the client and title ids in these fields at the time of creating the record.

Now for example, when you want to search the BOOKSHOP Table for all records for a particular client, you would:
first locate the clients record in the DETAILS Table, and move the value of id field into a temporay variable (say clientID).

Then execute the query

SELECT * FROM BookShop WHERE detailsID=clientID

This will result in a recordset that contains all BOOKSHOP records for that client.

As another example, when you want to search the BOOKSHOP Table for a particular client and title, you would:
first locate the clients record in the DETAILS Table, and move the value of id field into a temporay variable (say clientID).
then locate the title record in the INFO Table, and move that id into a temporary variable (say titleID)

Then execute the query

SELECT * FROM BookShop WHERE detailsID=clientID AND infoID=titleID

This will result in a recordset that contains all BOOKSHOP records for that client, for that title.

I know this isn't much information, but I hope it helps a bit. I'm sure there are others that can provide more information.
PS. I know you might have your reasons for saving numeric data in TEXT fields, but I would be defining those fields as numeric (using the appropriate type of course).

[LarryMc]

Brian,
To say what Larry said a little differently:

The Details table holds a unique id for each client. (id counter(1,1))

Add a new field to the Info table and the BookShop table (let's say 'cid')

When you pick a client, store their id in a variable (say client_id)

then, using a 'where' clause (WHERE cid="+client_id) on both the Info and BookShop tables.

Wah-Lah!
 
Revised: Peterpuk beat me to it!

[peterpuk]

Only just beat you to it Larry

But of course, in my attempt to help, I must mention that I have not used the correct syntax for the query. Larry's example is the way to go.

[Brian]

Thanks, guys!

A lot to go on there - I think a pint or three of beer tonight will do, before
I get down to it tomorrow

Thanks again,

Brian

[Brian]

Hello again,

Still stuck. . . . I understand the concepts of ORDER BY and WHERE clauses, but I just can't
figure out how to get them to work in this program

I've managed to get some data displayed from the Info table via a kludge, but I just don't
see how to make it display Info data by finding an entry in the Details table

I really could do with some sample code manipulating two tables, if anyone is up to the task

Any takers?

Brian

[Rock Ridge Farm (Larry)]

Post the code. Hard to guess without seeing some code.