May 24, 2022, 03:08:30 AM

News:

Own IWBasic 2.x ? -----> Get your free upgrade to 3.x now.........


help with databases

Started by chris, July 21, 2008, 04:57:47 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

chris

I hope this is the right place for this, anyway I was wondering if anyone has an example for a simple database just putting data in and then retrieving it? I tried to put the help file's examples together but they don’t go together any help would be welcome.

Thanks in advance
Chris

LarryMc

Besides the complete addressbook example that comes with ebasic a quick search of the forum for "database" yeilded this among many others:

http://www.ionicwind.com/forums/index.php/topic,2557.msg21737.html#msg21737

Larry
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

chris

thanks for the link but i can't seem to find the code that deals with the database its self, where is it in the addres book example? or do you know of any console window examples?

thanks again
chris

Allan


http://www.ionicwind.com/forums/index.php/topic,2557.msg21737.html#msg21737


The Database in the Address Book project is a Binary File; NOT an actual DATABASE like SQLite or ACCESS.

Look in the Book.inc file

' variables
DEF MyRecord:AddressType

' MyFPtr is a file pointer
DEF MyFPtr:BFile

' AddressFile holds our file name
DEF AddressFile:STRING


Allan



LarryMc

July 21, 2008, 08:05:59 PM #4 Last Edit: July 21, 2008, 11:25:25 PM by Larry McCaughn
Quote from: Allan on July 21, 2008, 07:52:49 PM

The Database in the Address Book project is a Binary File; NOT an actual DATABASE like SQLite or ACCESS.


Sorry about that.

Larry
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

chris

i'm still not getting it and i bet I’m way off but this is what i have so far i'm sure there is something really simple  wrong but i don't know what one again any help would help
OPENCONSOLE
DEF val, test : string
DEF pdb : uint
dbCreateMDB("c:\\test.mdb")
pdb = dbConnect("Microsoft Access Driver (*.mdb)", "C:\\test.mdb","")
hstmt = dbExecSQL(pdb,"CREATE TABLE  account (lname CHAR(15),fname CHAR(10)")
PRINT "IT WORKED"
INPUT "enter name ",val
hstmt = dbExecSQL(pdb,"UPDATE INTO accounts VALUES(" + val + ")" )
dbGetData(hstmt,1,test)
PRINT test
DO : UNTIL INKEY$<>""
CLOSECONSOLE


Ionic Wind Support Team

If you have 1.62 or earlier then the example programs are included in the compilers installation directory:

C:\program files\ebdev\projects\database

For 1.63 and later they are in your My Documents directory.

My Documents\Emergence Basic\projects\database

There are a couple of console applications there, and the complete addressbook example I wrote years ago that uses an Access DB, not the same as the addressbook program posted here recently.

As for your code, I believe it is "insert into" and not "update into".  INSERT INTO requires the table name, and column name/values.  Also as written you can't use that code in Vista, just a FYI as Vista doesn't allow creating files on the root of the drive.

Paul.

Ionic Wind Support Team

chris

allright thanks for all of the help i still can't get it to work and now it wont even make the database i think i'm going to stick to file for a bit longer thanks for all your help tho

LarryMc

Chris
You've got problems in two major areas(same as me when I first tried using the database).
first area is in the sequence of events (and the EBasic Database commands that accomplish them).

There is enough info in the help files and the examples to get a pretty good handle on them in a relatively short period of time.

The 2nd area is that of the SQL statements that are needed to do things with the database.
SQL tends to be picky in its syntax and the EBasic help files don't really tell you all you need to know.

When I was first working with SQL I spent a lot of time searching for SQL tutorials on the internet.
It gave me fits. And I still have trouble at times but I manage to get what I want done.

It's really powerful what you can do with the tool Paul provides once you get the hang of it so I would suggest you don't give up.

So read the help files, read everything  in the forums, try all the sample files and then come back with questions.

Larry
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

LarryMc

Chris
I took your code from above and "fixed" it .
the only thing you MAY have to change is the contents of dbname$

OPENCONSOLE
string fvalue, lvalue,  error, dbname$
istring test1[15], test2[10]
pointer pdb
int hstmt

fvalue="":lvalue="":test1="":test2="":error=""
pdb=null
hstmt=0
dbname$=getstartpath+"test.mdb"
if dbCreateMDB(dbname$)
print "database was created"
else
print "database already exist"
endif

pdb = dbConnect("Microsoft Access Driver (*.mdb)", dbname$,"")
IF pdb = NULL
print "failed to connect"
goto bailout
else
print "connected to database"
hstmt = dbExecSQL(pdb,"CREATE TABLE  account (lname CHAR(15),fname CHAR(10))")
error = dbGetErrorCode(hstmt)
IF LEN(error)
PRINT:PRINT "Error Code: ", error
PRINT "Error Text: ", dbGetErrorText(hstmt)
PRINT
else
dbFreeSQL(hstmt)
PRINT "IT WORKED"
ENDIF

INPUT "enter first name ",fvalue
INPUT "enter last name ",lvalue
hstmt = dbExecSQL(pdb,"INSERT INTO account (lname, fname) VALUES ('" + fvalue +"', '"+ lvalue+"')" )
error = dbGetErrorCode(hstmt)
IF LEN(error)
print "error inserting"
PRINT:PRINT "Error Code: ", error
PRINT "Error Text: ", dbGetErrorText(hstmt)
PRINT
else
dbFreeSQL(hstmt)
print "entry was added to database"

hstmt = dbExecSQL(pdb,"SELECT * FROM account")
error = dbGetErrorCode(hstmt)
IF LEN(error)
PRINT:PRINT "Error Code: ", error
PRINT "Error Text: ", dbGetErrorText(hstmt)
PRINT
else
WHILE dbGet(hstmt)
dbGetData(hstmt,1,test1,15)
dbGetData(hstmt,2,test2,10)
PRINT test1," ",test2
endwhile
dbFreeSQL(hstmt)
endif
endif
dbDisconnect(pdb)
endif
label bailout

print:print "press any key to continue"
DO : UNTIL INKEY$<>""
CLOSECONSOLE


Larry
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

Rock Ridge Farm (Larry)

There was a bookstore example posted some time back.
I used it as the basis of another database program.
It will create a database if one does not exist.
http://www.ionicwind.com/forums/index.php/topic,1535.0.html

billhsln

To Larry McCaughn, I took what you wrote and changed it just a little more.  My problem is that the second time the program is run, where the database file already exists, it does not seem to INSERT another row of info and does not print any thing out.  I know I must have done something wrong, but I just do not see it.  Any help will be appreciated.

Thanks,
Bill

OPENCONSOLE
string fvalue, lvalue,  error, dbname$
istring test1[15], test2[10]
pointer pdb
uint hstmt
int cntr

fvalue="":lvalue="":test1="":test2="":error=""
pdb = null
hstmt = 0
dbname$ = getstartpath + "!test.mdb"
if dbCreateMDB(dbname$)
print "database was created"
pdb = dbConnect("Microsoft Access Driver (*.mdb)", dbname$,"")
IF pdb = NULL
print "failed to connect"
goto bailout
else
print "connected to database"
hstmt = dbExecSQL(pdb,"CREATE TABLE  account (lname CHAR(15),fname CHAR(10))")
error = dbGetErrorCode(hstmt)
IF LEN(error)
PRINT:PRINT "Error Code: ", error
PRINT "Error Text: ", dbGetErrorText(hstmt)
PRINT
goto bailout
else
dbFreeSQL(hstmt)
PRINT "Create Table worked"
endif
endif
else
print "database already exists"
endif

INPUT "enter first name ",fvalue
INPUT "enter last name ",lvalue
hstmt = dbExecSQL(pdb,"INSERT INTO account (lname, fname) VALUES ('" + fvalue +"', '"+ lvalue+"')" )
error = dbGetErrorCode(hstmt)
IF LEN(error)
print "error inserting"
PRINT:PRINT "Error Code: ", error
PRINT "Error Text: ", dbGetErrorText(hstmt)
PRINT
else
dbFreeSQL(hstmt)
print "entry was added to database"
hstmt = dbExecSQL(pdb,"SELECT * FROM account")
error = dbGetErrorCode(hstmt)
IF LEN(error)
PRINT:PRINT "Error Code: ", error
PRINT "Error Text: ", dbGetErrorText(hstmt)
PRINT
goto bailout
else
PRINT " SELECT * FROM account"
cntr = 0
WHILE dbGet(hstmt)
cntr++
dbGetData(hstmt,1,test1,15)
dbGetData(hstmt,2,test2,10)
PRINT test1," ",test2," ",cntr
endwhile
dbFreeSQL(hstmt)
endif
endif
dbDisconnect(pdb)

label bailout

print:print "press any key to continue"
DO : UNTIL INKEY$<>""
CLOSECONSOLE
END
When all else fails, get a bigger hammer.

LarryMc

Bill,

The only time dbconnect is being executed is when the db is created (which is only the first time the program is run).

Add the following code just before your "INPUT" statements.

if pdb =NULL
pdb = dbConnect("Microsoft Access Driver (*.mdb)", dbname$,"")
IF pdb = NULL
print "failed to connect"
goto bailout
else
print "connected to database"
endif
endif


Larry
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

billhsln

Thank you, Larry.  The program runs perfectly as expected now.

It is always the little things that make such a large difference in how things work.

Bill
When all else fails, get a bigger hammer.

chris

thank you once again it works just fine and i've got it to do what i need :)
now to just type everything up

thanks again
chris