SQL Help

[FarmerMaggot]

Is the primary key that important?

Joe

[Eonwë]

If I recall my use of it in Access, I'd say the name says it all :} The primary key is the one field that cannot be a duplicate, and therefore must be unique and each record must have one. I think in some cases you can have multiple primary keys (just guessing, but I seem to remember that ability in Access) to further guarantee uniqueness of the records.

Hope that helps :}
Eonwë

[FarmerMaggot]

Hm, darn... I can't get it to create a table when I specify a primary key . I'll post the command I'm using when I get home.

FarmerMaggot

[arathorn]

Ahhhh... so now my 20 years in computers does not completely go to waste ;D:

You're not far off, Eonwe. In most (if not all) databases you HAVE to have a Primary key, and as such, only ONE though. What you CAN have in the same table is a SECONDARY key. This 2nd key allows you to link with another table's Primary key to extract like data (and so these two keys HAVE to be exactly the same type, size etc). I could go into more detail and draw several diagrams explaining the significance (hmm... maybe even STICK diagrams?...) but that would most likely just confuse at this point. Hmm... maybe I should teach a course in an intro to databases...and then some!

Now, Farmer, did you get my Email? I sent you the syntax you should be using for a table create .. seems yours was slightly different than the manual for Realbasic that you are using? I haven't heard back from you so I don't know if you tried it or not.

Let me know.

[arathorn]

Joe,

Not sure if you're getting my email so posting here,too:

BTW, regarding the second email message here, I've since read that I need the MySQL database plugin to use the MySQLDatabase class (which I imagine allows you to create the database, tables, etc.). Is there a demo version of that I can try? Do I need something else?

===

FIRST MESSAGE:
---
Take a look at this syntax I found in the Realbasic manual I downloaded:


Dim db as Database
.
.
db.SQLExecute("create table authors(au_id integer not null,
au_fname varchar, au_lname varchar, primary key (au_id)")


I think you either forgot the dbl quotes (although they MAY be optional)
*OR*
your primary key name was NOT in parentheses as well...
think it's the extra parentheses you need myself.
---

===

SECOND MESSAGE:
---
I downloaded the RB demo and created an app using the quickstart 'manual' to get a feel for RB. It's quite similar to VB but I have to say I like it's functionality. I like the fact that it auto-creates many of the menu apps, lines up objects with guidelines and the completion of syntax is also helpful.

I've attached a zip in the email with two Mac versions of the simple app to see if you can run a compiled version on your machine. Curious. Also created a Linux version to see if it runs there. ;D

Also, I'm wondering, HOW did you try to create a table in RB? Can I do/test/try it with the demo, or are you using another program/add-on to RB for database creation? I'm looking into seeing how, if I can, but figured I'd ask you as well.
---

[Shelob]

hmmm... databases.... its been long since I have been wanting to create a database, but I never got any further than the basics of the basics, and even that I could not grasp. Care to give me a crash course once I get MS Access?

[arathorn]

I'm quite well versed in many databases not just SQL/relational ones so any knowledge I can relay, mp!

Unfortunately, as I mentioned in another thread, my main PC crashed this evening and not sure how bad the damage may end up being. <sigh>

[arathorn]

Well, luckily even though the shop couldn't back up my stuff, I took a chance and ran the 'FIXMBR' command when I got the PC back home and it actually fixed my boot master. NOW at least I have WinXP back, but Linux is unavailable as yet. I'll wait 'til next month to buy a backup hard drive to copy everything to, before I try restoring Linux. Life never ceases to annoy

[arathorn]

Well I figured it out. The documentation is a tad out of date AND apparently the primary key is built in now i.e. gets created automatically and isn't specified anymore (odd since you might not necessarily want an auto-numeric). ALSO, the REALdatabase NO longer allows the NOT NULL constraint. SO, that's why we kept getting a syntax error, because it kept interpreting that "primary key" in "primary key (id)" was TWO words instead of the 'implied' ONE compound word. In any event here's the article I read:

*** start ***
Unfortunately that section of the online reference is out of date and needs to be updated. The new REAL Database doesn't support column constraints, such as "not null", and it has one primary key built in for you already. To create a table, you can say:

db.SQLExecute "create table classes( id integer )"

*** end ***

---
---

SO, to create a table you have to open the db then create the table. Here's code that I tested and works. (Btw, I figured out how to create a database from the FILE menu options):

** code start **
Dim dbFile as FolderItem
Dim realdb as REALdatabase
realdb=New REALdatabase
dbFile = GetFolderItem("realDB")
realdb.DatabaseFile=dbFile
If realdb.Connect() then
//proceed with database operations here..
realdb.SQLExecute("create table invoices(id integer, Cust_ID integer, Amount double, Date date)")
MsgBox "Create table error was: " + realdb.ErrorMessage
else
Beep
MsgBox "The database couldn't be opened."
end if
** code end **