ok, I am confused....

Databases
1

Ok,

I am new to Xojo, yes, but have done some things in visual basic, and q7basic. (fortran in college, long time ago), but I am confused on how to do things in xojo. OK, you don’t have global variables… not sure why, as I use them to store values to be used by other areas of my program, but ok.

Now, I have a database that I am accessing (mysql, hosted on a separate pc). First, I would like to initialize the connection only once (haven’t figured out how, see the thing about global variables), but have been initializing form or method, using the code below. I first want to delete all the records in the recordset, but the code is not working (I don’t know the correct syntax to use here), and later want to input data into the recordset (probably use preparedsql statement, after I learn how it works in xojo).

Any hints/ suggestions / examples on how to do the tasks? I have looked at the database examples that downloaded with xojo, and see how they read the data, but how do you delete data?

----- code below ----------
Dim rs as RecordSet
Dim strSql, ConnectStatusLabel as STRING
'Dim mIsConnected As Boolean

If Not IsConnected Then
Dim buylist as mySQLCommunityServer

buylist = New MySQLCommunityServer

buylist.Host = "xxxxxxxx.biz"
buylist.UserName = "me"
buylist.Password = "not shown"
buylist.DatabaseName = "buylist"
'MsgBox("Now Connected to database.")

End If

Dim SPMktCap As Single
Dim spdata1 as RecordSet

// now I am tyring to delete all the records in spdata1 //

strSql=“Delete * from SPDATA1”
spdata1=buylist.SQLSelect(strSql)

2

Regarding global variables, add a module to the App and add your variables as properties there. Any method/ window will be able to see them.

3

When I try to add to the app, the module is greyed out… I can not add anything above the even handler line, but can add event handler and lines below that…??

4

Adding the module is done with the “insert” cube or menu. I cannot reproduce the greyed out module. Click of app, hit blue cube, choose module - module1 (2,3…) will appear in the navigator with a globe icon.

5

Thanks NG P I was able to add a module, then properties using the blue cube… Is this were I would declare the database connection so that it is accessible from other parts of the program? Would it then be a property also, like the variables?

6

Instead of SQLSelect try using SQLExecute. It’s used to process a command that doesn’t return data.

7

Ok, changed code to:

strSql=“Delete * from SPDATA1”
buylist.SQLExecute(strSql)

The recordset still has data, though… not deleted…

Also, even with SPMKTcap as a property of a module, unless it is dim’d inside of this routine I get a does not exist error… do you have to reference it different when it is in a different module?

Thanks

Thanks

8

Ok couldn’t edit prior post … your suggestion worked, after getting rid of the * in the sql statement (forgot about that, duh). Any ideas on the other items?

9

did you commit the changes to the database?

10

I ran the above code, with the change suggested by Dwane… it does now delete the data… did not run a commit command, but checked the database in a shell, and the table is empty.

11

Create a Module called “Globals”. Create properties in there as “protected”. This means that, anywhere in your app, you can access “Globals.SomeProperty”.

Extensive use of global variables is contrary to an OOP philosophy. I’m not saying, “don’t use them”, I’m saying, “consider carefully when and where to use them.”

12

A great series on database usage in Xojo applications can be found in these stickies from the old forum:

http://forums.realsoftware.com/viewtopic.php?f=3&t=4342