Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Sat Oct 20, 2018 11:23 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 12 posts ] 
Author Message
 Post subject: Database
PostPosted: Fri Feb 08, 2013 3:07 pm 
Offline
User avatar

Joined: Thu Jun 28, 2007 9:09 am
Posts: 14
Location: Belfast, N.Ireland
Hey all, im trying to teach myself how to create a "New SqlDatabase" really dont know where to start, i tried Jay Jennings video tutorial but get error after error, also the RealStudio Tutorial Example and tutorial is very daunting, im trying to start simple just having a listbox a few editfields, Buttons to Add, Edit and Delete records... Thats it, could someone point me in the direction of a small example or a decent tutorial so i can get started??

Many many thanks =)

Robin

_________________
C:/Program Run... C:/Program Crash... C:/Programmer Quit!


Top
 Profile  
Reply with quote  
 Post subject: Re: Database
PostPosted: Fri Feb 08, 2013 8:15 pm 
Offline
User avatar

Joined: Wed Sep 20, 2006 5:12 pm
Posts: 680
Location: Puerto Rico
you mean sql engine :?:

_________________
Thanks for any contribution and God blesses all (I start Using RS in 10/12/2010)
Mac Mini with Lion v10.7.5 and Real Studio 2012r1 Pro And Windows 7 RS2012r1 Personal and Linux RS2012r1 Personal Register user
my Hobbies R/C Turbines Jets Models


Top
 Profile  
Reply with quote  
 Post subject: Re: Database
PostPosted: Fri Feb 08, 2013 11:31 pm 
Offline
User avatar

Joined: Thu Jun 28, 2007 9:09 am
Posts: 14
Location: Belfast, N.Ireland
Sorry yes i mean Sql Engine =)

_________________
C:/Program Run... C:/Program Crash... C:/Programmer Quit!


Top
 Profile  
Reply with quote  
 Post subject: Re: Database
PostPosted: Sat Feb 09, 2013 11:19 am 
Offline
User avatar

Joined: Fri Sep 30, 2005 11:48 am
Posts: 3554
Location: Lenexa, KS
I have a ~ten hour series on how to create a database application as part of my Real Studio training subscription videos at http://www.bkeeney.com/RealStudioTraining/realstudiotraining.cgi. In that series I create a simple Journal (think diary) application that uses an SQLite database. There are also a few addendum's to the series where I convert it to use MySQL and in one I use ActiveRecord instead of the standard DatabaseRecord and Recordset methods.

_________________
Bob K.

A blog about being a Real Studio/Xojo developer at http://www.bkeeneybriefs.com


Top
 Profile  
Reply with quote  
 Post subject: Re: Database
PostPosted: Sat Feb 09, 2013 12:18 pm 
Offline

Joined: Wed Sep 15, 2010 1:48 pm
Posts: 84
Start with the code examples found in the help. F1 key. They do work.

search the forums for database starter databasestarter.zip

If your new to real studio, spend time learning everything in the language reference and do the real basic tutorials. Building any non-trivial application takes time. I have a database program I am working on, and it is the culmination of several years of on/and/off again coding. I still have a lot of work to do on it.

Take some time to understand how code is executed and how real studio works. Computer programming can not be learned over nite, unless I am a slow learner.

_________________
RS2012r2 Pro Win


Top
 Profile  
Reply with quote  
 Post subject: Re: Database
PostPosted: Mon Feb 11, 2013 9:00 am 
Offline

Joined: Sat May 19, 2007 6:01 am
Posts: 300
This is bad code but enough to get you started.
The database itself will be stored in the same folder as the app itself. It's not correct, but at least, it's working.
Copy/paste and fiddle on your own!

// Ask if DB exists, if not create. (If debugmode, then insert data.)


Dim dbDatabase As REALSQLDatabase
Dim TargetFile As FolderItem
Dim blnResult as boolean

dbDatabase = New REALSQLDatabase
TargetFile = GetFolderItem("database.rsd")

dbDatabase.DatabaseFile = TargetFile
IF TargetFile <> Nil THEN
main.strDatabasePath =str(TargetFile.absolutepath)
blnResult = dbDatabase.CreateDatabaseFile
if dbDatabase.Error then
msgbox dbDatabase.ErrorMessage
end
IF blnResult = TRUE THEN
// LIVE STATUS UPDATE
// SETTINGS
dbDatabase.SQLExecute ("Create Table [settings] ( intID Integer PRIMARY KEY AUTOINCREMENT, bolUpdate boolean, intUpdate Integer )")

// URL
dbDatabase.SQLExecute("Create Table [URL] (intID Integer PRIMARY KEY AUTOINCREMENT, dteCreated timeStamp, " _
+ "strURLPath varchar, strURL varchar, strName varchar, strNotes varchar, intCounter Integer) ")

// COUNTER
dbDatabase.SQLExecute("Create Table [counter] (intID Integer PRIMARY KEY AUTOINCREMENT, dteCreated timeStamp, intURLID Integer )")


// Insert settings ---> (from another project, names are wrong but you get the idea.)
dim dr1 as new DatabaseRecord
dr1.IntegerColumn("intDelay") = 2
dr1.IntegerColumn("intStartPage") = 1
dr1.IntegerColumn("intStopPage") = 10
dbDatabase.InsertRecord( "DominatorSettings",dr1 )
dr1.IntegerColumn("intDelay") = 2
dr1.IntegerColumn("intStartPage") = 1
dr1.IntegerColumn("intStopPage") = 10
dbDatabase.InsertRecord( "KATSettings",dr1 )

dbDatabase.Commit
DIM testXX as boolean = FALSE

IF DebugBuild AND testXX = TRUE THEN // ---> (from another project, names are wrong but you get the idea.)
dim dr as new DatabaseRecord
dim d as New date
DIM intLastID As Integer
// KEYWORD ACCESS TOOL
dr.Column("strName") = "www.russia.se"
dr.Column("dteCreated") = d.ShortDate
dbDatabase.InsertRecord( "KATURL",dr )
'dbDatabase.Commit
intLastID = dbDatabase.lastRowID

dr.Column("strName") = "ryska ambassaden"
dr.IntegerColumn("intURLID") = intLastID
dr.Column("dteCreated") = d.ShortDate
dbDatabase.InsertRecord( "KATKeyword",dr )
'dbDatabase.Commit
dr.Column("strName") = "visum ryssland"
dr.IntegerColumn ("intURLID") = intLastID
dr.Column("dteCreated") = d.ShortDate
dbDatabase.InsertRecord( "KATKeyword",dr )
'dbDatabase.Commit
dr.Column("strName") = "ryssar i sverige"
dr.IntegerColumn ("intURLID") = intLastID
dr.Column("dteCreated") = d.ShortDate
dbDatabase.InsertRecord( "KATKeyword",dr )
if dbDatabase.error then
msgbox str(dbDatabase.ErrorCode) + " -- " + dbDatabase.ErrorMessage
return
end
// Commit!!
dbDatabase.Commit

// DOMINATOR
dim dr2 as new DatabaseRecord
dr2.Column("strName") = "ryska ambassaden"
dr2.Column("dteCreated") = d.ShortDate
dbDatabase.InsertRecord( "DominatorKW",dr2)
'dbDatabase.Commit
intLastID = dbDatabase.lastRowID

dr2.Column("strName") = "www.russia.se"
dr2.IntegerColumn("intDominatorKWID") = intLastID
dr2.Column("dteCreated") = d.ShortDate
dbDatabase.InsertRecord "DominatorURL",dr2
'dbDatabase.Commit
dr2.Column("strName") = "www.russia.com"
dr2.IntegerColumn("intDominatorKWID") = intLastID
dr2.Column("dteCreated") = d.ShortDate
dbDatabase.InsertRecord "DominatorURL",dr2
'dbDatabase.Commit
dr2.Column("strName") = "www.russia.net"
dr2.IntegerColumn("intDominatorKWID") = intLastID
dr2.Column("dteCreated") = d.ShortDate
dbDatabase.InsertRecord "DominatorURL",dr2
if dbDatabase.error then
msgbox str(dbDatabase.ErrorCode) + " -- " + dbDatabase.ErrorMessage
return
end
dbDatabase.Commit
END IF


dbDatabase.Close

'MsgBox "Database created"
if dbDatabase.Error then
msgbox dbDatabase.ErrorMessage
end
ELSE
MsgBox "Database fail"
END IF
ELSE
// dbDatabase EXISTS, NO NEED TO CREATE NEW DATABASE.
'blnResult = dbDatabase.Connect
END IF


// loadURL = put all URL's in a listbox




' ## CLEAR ALL PREVIOUS DATA
lstOffline.deleteAllRows

' ## GET FROM DATABASE
Dim dbDatabase As REALSQLDatabase
Dim TargetFile As FolderItem
dbDatabase = New REALSQLDatabase
Dim blnResult as boolean

TargetFile = GetFolderItem(main.strDatabasePath)
dbDatabase.DatabaseFile = TargetFile

blnResult = dbDatabase.Connect
IF dbDatabase.Error THEN
MsgBox dbDatabase.ErrorMessage
END IF
IF blnResult = TRUE THEN
' ## READ FROM DB
dim dr as new DatabaseRecord
dim sql as string

' ## QUESTION
sql = "SELECT intID, strName, strURL, strURLPath, dteCreated FROM [URL] ORDER BY intID"

dim rs as RecordSet = dbDatabase.SQLSelect(sql)

' ## FILL DATA
IF rs <> NIL THEN
while not rs.eof
lstOffline.AddRow(rs.Field("strName"). StringValue + " " + rs.Field("dteCreated"). StringValue )
'lstOffline.AddRow(rs.Field("strName"). StringValue )
lstOffline.RowTag(lstOffline.LastIndex) = rs.Field("intID"). StringValue
rs.MoveNext
wend

' ## CLEAR DATA
'txtURL.text = ""
'btnInsertURL.Caption = "Insert"
'btnInsertURL.Enabled = false
'btnDeleteURL.Enabled = false
END IF
END IF

// Insert URL = add post to database




' ## WORK WITH TEXT
DIM arrName(-1) As String
DIM arrURL(-1) As String

DIM strURL, strName as String
' ## ht.tp... --> an URL as I extract the name and the path from. The URL also contain extra information, after the "?", not interesting for me.

arrURL = split( trim(txtURL.text) , "?")
strURL = arrURL(0)
arrName = split(strURL , "/")
strName = arrName( UBound(arrName) )

' ## INSERT INTO DB
Dim dbDatabase As REALSQLDatabase
Dim TargetFile As FolderItem
dbDatabase = New REALSQLDatabase
Dim blnResult AS Boolean

TargetFile = GetFolderItem(main.strDatabasePath)
dbDatabase.DatabaseFile = TargetFile

blnResult = dbDatabase.Connect
IF dbDatabase.Error THEN
MsgBox dbDatabase.ErrorMessage
END IF
IF blnResult = TRUE THEN
// add to database
dim dr as new DatabaseRecord
dim d as New date
dr.Column("strURLPath") = trim(txtURL.text)
dr.Column("strURL") = strURL
dr.Column("strName") = strName
dr.Column("dteCreated") = str(d.ShortDate)
dbDatabase.InsertRecord "URL",dr
dbDatabase.Commit
'MsgBox "Last ID: " + str(dbDatabase.lastRowID)
dbDatabase.Close
END IF

// CLEAR DATA
txtURL.text = ""
btnSave.Enabled = FALSE
btnSave.Refresh

loadURL


***
There are many errors in this code, but at least, it works and it will get you started.
You have SQL experience? You need to INSERT, UPDATE and DELETE data and based on this code you might can think of the rest yourself!! Good luck!!

Edit: No, not errors. But people here will reply that you can't do this and that and I just agree. You can't. But it works and for me, it's a good start!!


Top
 Profile  
Reply with quote  
 Post subject: Re: Database
PostPosted: Tue Feb 12, 2013 1:14 pm 
Offline
User avatar

Joined: Thu Jun 28, 2007 9:09 am
Posts: 14
Location: Belfast, N.Ireland
Thank you for all the info, but i have never touched databases before. its a really dark grey area to me and my knowledge of RealStudio is shaky but learning alot looking through the Documentation and these forums but alas am really new to Databases, been scouring the net for examples but nothing, ive tried using the code you posted Eclipse but my knowledge of RealStudio is limited and not to sure what Controls and methods these are for.

again thank you so much for the replies but am still abit lost.

Robin

_________________
C:/Program Run... C:/Program Crash... C:/Programmer Quit!


Top
 Profile  
Reply with quote  
 Post subject: Re: Database
PostPosted: Tue Feb 12, 2013 3:38 pm 
Offline

Joined: Sat Dec 24, 2011 5:32 am
Posts: 40
Location: Germany
Another good introduction to database usage (SQLite) in Real Studio:
http://www.youtube.com/watch?v=bm2U5OZ6FHY

Greetings,
Christian


Top
 Profile  
Reply with quote  
 Post subject: Re: Database
PostPosted: Tue Feb 12, 2013 5:17 pm 
Offline
User avatar

Joined: Thu Jun 28, 2007 9:09 am
Posts: 14
Location: Belfast, N.Ireland
Will have a go,

Many many thanks Christian :mrgreen:

_________________
C:/Program Run... C:/Program Crash... C:/Programmer Quit!


Top
 Profile  
Reply with quote  
 Post subject: Re: Database
PostPosted: Tue Feb 12, 2013 6:12 pm 
Offline
User avatar

Joined: Thu Jun 28, 2007 9:09 am
Posts: 14
Location: Belfast, N.Ireland
Christian that video was very helpful! Made me understand a bit about SQL,

But what im trying to achieve is to allow the user to enter the data via Editfield/Textfield. could you shed some light on this?

Thanks again

Robin

_________________
C:/Program Run... C:/Program Crash... C:/Programmer Quit!


Top
 Profile  
Reply with quote  
 Post subject: Re: Database
PostPosted: Wed Feb 13, 2013 8:18 am 
Offline

Joined: Sat Dec 24, 2011 5:32 am
Posts: 40
Location: Germany
Hello,
I did not test the following code, so maybe you will have to tweak it a little bit. You should add to the project in the video some additional steps:

Prepare the User Interface
Add a new Button with name "PushButtonAddTFDataToDB" with caption "Add Textfield data"
Add a new TextField with name "TF_Name"
Add a new TextField with name "TF_Coach"
Add a new TextField with name "TF_City"


Write a new Method
Add new Method with name: AddTextFieldData
Write this code into the Method:

If Not IsConnected Then
MsgBox("Create the database and create the table first.")
End if

Dim row As New DataBaseRecord
// ID will be updated automatically
row.Column("Name") = TF_Name.text
row.Column("Coach") = TF_Coach.text
row.Column("City") = TF_City.text

mDB.InsertRecord("Team", row)

If mDB.Error Then
AddDataStatusLabel.Text = "DB Error: " + DB.ErrorMessage
End if



In the Action Event of the PushButton "PushButtonAddTFDataToDB" write "AddTextFieldData" (without the quotes).

Test
Enter some data into the textfields, push the "PushButtonAddTFDataToDB"-PushButton and push the "Show Sample Data" Pushbutton.

Greetings,
Christian


Top
 Profile  
Reply with quote  
 Post subject: Re: Database
PostPosted: Sat Feb 16, 2013 4:36 pm 
Offline

Joined: Sat May 19, 2007 6:01 am
Posts: 300
Excuse me for posting too difficult code for you.

I'd be happy to send you the complete project, and these methods and so will maybe be easier to get a grip of...

Also, I'd like to point out that you can use SQL in RealBasic, but SQL is the same as used elsewhere. It's not specific to RealBasic, so to say.

SELECT * FROM [users] WHERE strSecretName = 'Eclipse';


Sort of...!! The above code is SQL and you can use SQL in RealBasic.


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 12 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group