Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Mon Oct 14, 2019 3:19 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 7 posts ] 
Author Message
 Post subject: MySQL Examples
PostPosted: Wed Aug 25, 2010 4:11 am 
Offline
User avatar

Joined: Wed Aug 25, 2010 4:04 am
Posts: 91
Location: South Africa
Good day

I'm new to Real Studio.

I want to know if there are any step by step tutorials explaining/showing how to create an application that connects to an Internet Web based MySQL server. I went through the motions om adding the MySQL server to my project adding the server details ie. username, password etc. I do seem to be able to make a connection but don't seem to be able to query the database and retrieve a recordset. Unfortunately I killed the project because it was the first thing I tried after purchasing my license.


Thanks for any responses.

_________________
Rudolph Thomas
TIGME.COM


Top
 Profile  
Reply with quote  
 Post subject: Re: MySQL Examples
PostPosted: Wed Aug 25, 2010 5:58 am 
Offline
User avatar

Joined: Wed Aug 25, 2010 4:04 am
Posts: 91
Location: South Africa
I found an example by miklos on this board and modified it till it made more sense. Hope it helps someone else. The manual is a bit frustrating. Would be nice to have a manual like PHP.net:

'Place a Listbox control on your window. It is called Listbox1 by default

Dim db as MySQLCommunityServer
db = New MySQLCommunityServer
'db.host="must be an ip address ie. 10.0.0.2"
db.host="ipaddr"
db.port=3306
db.DatabaseName="yourdbname"[/color]
db.UserName="yourusername"
db.Password="yourpassword"
if db.Connect then
Dim i as Integer
Dim rs as RecordSet

'Possible ways write your Query
'rs = db.SQLSelect("SELECT * FROM tablename ORDER BY fieldname")
'rs = db.SQLSelect ("SELECT * FROM list_days_of_week ORDER BY option_name")
rs = db.SQLSelect ("SELECT * FROM list_geo_countries")

Listbox1.DeleteAllRows

while not rs.eof
Listbox1.AddRow ""
for i = 1 to rs.FieldCount
'Possible ways to populate the List Box
'Listbox1.Cell( Listbox1.LastIndex, i-1 ) = rs.Field("option_name").Value
'Listbox1.Cell( Listbox1.LastIndex, i-1 ) = rs.Field("option_value").StringValue
Listbox1.Cell( Listbox1.LastIndex, i-1 ) = rs.IdxField(i).Value
next
rs.MoveNext
wend

rs.Close
else
MsgBox "db failed"
end if

_________________
Rudolph Thomas
TIGME.COM


Top
 Profile  
Reply with quote  
 Post subject: Re: MySQL Examples
PostPosted: Thu Aug 26, 2010 10:28 pm 
Offline

Joined: Sat Nov 24, 2007 3:09 pm
Posts: 159
Location: Denver, CO
Something that I've found really helpful when working with databases is using introspection… I always use a method I call syncLocalProperties:
Sub syncLocalProperties(rs As RecordSet)
dim a As DatabaseField
Dim myAttributes() as Introspection.PropertyInfo = Introspection.GetType(self).GetProperties

for n as Integer = 1 to rs.FieldCount
a = rs.IdxField(n)
For i as Integer = 0 to Ubound(myAttributes)
if myAttributes(i).Name = a.Name then
myAttributes(i).Value(self) = a.Value
end if
Next
next


End Sub


This way I can simply build a class with properties that match fields in the database — so myClass.id is automatically filled in with data from the 'id' field on the database.

so instead of
while not rs.eof
Listbox1.AddRow ""
for i = 1 to rs.FieldCount
'Possible ways to populate the List Box
'Listbox1.Cell( Listbox1.LastIndex, i-1 ) = rs.Field("option_name").Value
'Listbox1.Cell( Listbox1.LastIndex, i-1 ) = rs.Field("option_value").StringValue
Listbox1.Cell( Listbox1.LastIndex, i-1 ) = rs.IdxField(i).Value
next
rs.MoveNext
wend


you would have something like:
dim tempClass As new myClass
do until rs.EOF
tempClass.syncLocalProperties(rs)
Listbox1.AddRow tempClass.foo
Listbox1.Cell(Listbox1.LastIndex,1) = tempClass.bar
rs.MoveNext
loop

_________________
Mojiferous Industries, now 300% more Mojifertastic!


Top
 Profile  
Reply with quote  
 Post subject: Re: MySQL Examples
PostPosted: Fri Aug 27, 2010 1:44 am 
Offline
User avatar

Joined: Wed Aug 25, 2010 4:04 am
Posts: 91
Location: South Africa
Thanks for your reply. I want to ask a noob question:
Where do you place the two code segments:

Sub syncLocalProperties(rs As RecordSet)... and dim tempClass As new myClass...

I assume dim tempClass As new myClass... will go in Listbox1

but where do I place Sub syncLocalProperties(rs As RecordSet)... in the IDE? :roll:

_________________
Rudolph Thomas
TIGME.COM


Top
 Profile  
Reply with quote  
 Post subject: Re: MySQL Examples
PostPosted: Sun Aug 29, 2010 11:12 pm 
Offline

Joined: Sat Nov 24, 2007 3:09 pm
Posts: 159
Location: Denver, CO
syncLocalProperties would be a method on the myClass class — which you would instantiate in the IDE by clicking on the "Add Class" button while browsing your project (the "project" tab) in the IDE, or by going to the Project menu->Add->Class.

You can then set up all the properties of your class, etc and name it something (other than myClass). The properties should be named the same as your fields in the database, so if you have a field named "id" that is an INT of length 11, you would add an integer property to your class named "id". Here's an example from a project I'm working on, I have a class called genre, with three properties:
brief As String
descript As String
id As Integer

And my database has a table called "genres" with three fields with the same names as my class.
CREATE TABLE `genres` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`brief` varchar(10) DEFAULT NULL,
`descript` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
);


So genre also has a method called syncLocalProperties. so my code from above would actually be:
dim tempGenre As new genre
do until rs.EOF
tempGenre.syncLocalProperties(rs)
Listbox1.AddRow tempGenre.brief
Listbox1.Cell(Listbox1.LastIndex,1) = tempGenre.descript
rs.MoveNext
loop

...and I would end up with a listbox with one column corresponding to the "brief" string and the other to the "descript" string.

So to sum up: myClass should be a new class in the IDE, syncLocalProperties should actually be a method on myClass named syncLocalProperties that accepts rs as RecordSet. Hope that helps!

_________________
Mojiferous Industries, now 300% more Mojifertastic!


Top
 Profile  
Reply with quote  
 Post subject: Re: MySQL Examples
PostPosted: Mon Aug 30, 2010 10:28 am 
Offline
User avatar

Joined: Wed Aug 25, 2010 4:04 am
Posts: 91
Location: South Africa
@mojiferous

Thanks, you are a star... I'll try it out...

_________________
Rudolph Thomas
TIGME.COM


Top
 Profile  
Reply with quote  
 Post subject: Re: MySQL Examples
PostPosted: Tue Jan 01, 2013 10:50 pm 
Offline
User avatar

Joined: Sun Feb 20, 2011 1:05 pm
Posts: 6
Location: Boston, MA
This is an interesting example. Very cool. There's two things I don't understand. First, how do you add more fields? I understand about adding the properties with the field names and data type the same of the fields in the database. The second is that I can get it to populate two fields but no matter what it won't work with the ID field.

I have a simple database of ID, Prefix, FirstName, Mid, LastName. I want to populate a listbox with all the values including the ID which is an integer. How do I modify this code to handle more fields?

dim tempClass As new myClass
do until rs.EOF
tempClass.syncLocalProperties(rs)
Listbox1.AddRow tempClass.foo
Listbox1.Cell(Listbox1.LastIndex,1) = tempClass.bar
rs.MoveNext
loop


It seems that the syncLocalProperties is working on an unlimited number of fields. I take it that that is exactly what it is there for.

Sub syncLocalProperties(rs As RecordSet)
dim a As DatabaseField
Dim myAttributes() as Introspection.PropertyInfo = Introspection.GetType(self).GetProperties

for n as Integer = 1 to rs.FieldCount
a = rs.IdxField(n)
For i as Integer = 0 to Ubound(myAttributes)
if myAttributes(i).Name = a.Name then
myAttributes(i).Value(self) = a.Value
end if
Next
next
End Sub


Suggestions anyone?

Thanks.

Duane Mitchell


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 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