Setting up MySQL 5.5 with VB6 for Eclipse

[Captain_Starblazer]

**Lets get this out of the way first.**
This *** IS NOT *** a 'tutorial', it is a starting point. If you know nothing about VB6 or Windows OBDC, leave now and have a nice life..

I am not as nice as Robin is. I am not a teacher. I have no tolerance or patience. I will not nursemaid you in anyway. I will not explain my coding to you (or care about what you think of my coding style, my speeling or me gammer). I learned a long time ago that "No Good Deed goes unpunished" and from what I have read in some of Robin's post he should take that to heart.
But, here I go again trying to 'help'. Mainly because this site has breathed new life into a dead horse (My 33 year old project) that I have loved since Trade Wars 2000 was around. In the old BBS days.
And I am now a Grumpy Old Bastard™

**If you are still here**, and want an idea how to massively expand the Eclipse Engine (and you have the basic skills), read on, this is prolly not the only way to do this. (And Robin, your and Eclipses name will show prominently in the Credits, although it will not look a bit like Eclipse. I'm basically just using the techniques)

**Now, on with it…**

I am in the process of starting up my new project (Galaxy Conquest 2569) yet again after working on it in various incarnations. (Started as single player, written in Quick Basic 4.5 (The days BEFORE Windows and the internet) and was inspired by the fore mentioned BBS Door Game Tradewars 2000\. Then I tried Visual Basic 1.0, then VB3 connect to a DB, Then totally as web CGI under linux and PERL. Now VB6 and Eclipse.

**Ok, starting off** you obviously have to setup MySQL 5.5 for Windows (Plenty of places on the web to tell you how) and you will also need to install the mysql-connector-odbc-5.1.10.
Both you can get for the MySQL Product Site.
I used mysql-5.5.20-win32.zip and set it up manually (Because most msi installers really suck)
and mysql-connector-odbc-5.1.10-win32.msi (no getting away from that)

** THIS IS ONLY FOR THE PURPOSE OF THIS POST **
This code will work with any known database and table that is TEXT..
–------------------------------------------------------
Create your access to the MySQL Server and create
A New Database (and remember the name)
Create a table in the new database called 'test_table'
Create 5 columns named col1,col2,col3,col4,col5 respectfully as VARCHAR 255
insert values in each column col1data,col2data,col3data,col4data,col5data.
----------------------------------------------------------

The following is for Windows XP Professional XP3 (I have no idea if its different in Win7, and I really don't care)

Under Control Panel - Administrative Tools - Data Sources (OBDC)
Set up OBDC Connections
User DSN using MySQL ODBC 5.1 Driver for your database location and ensure it tests ok
System DSN of the same
and a FILE DSN of the same

After all three of these test ok.. we continue on to the coding.

FORWARD: The reason for the coding below it because of how Eclipse works. You * DO NOT * want the remote client directly accessing your MySQL server, that would be a massive security risk. It would be much better for the client to send a command to your eclipse server and then have the secured server do the MySQL query and return the results to the client as a text string that it can then be processed. The following code demonstrates the server side of this..

Create a new VB6 Project (I use Visual Studio 6 VB 6 Enterprise).
Put the following on the form.

The Data Control **** IS NOT **** the normal MS 'Data' Control , it is the 'Remote Data Object Control' (RDO) which is included in VB6\. To put this on the toolbox so you can put it on the form, open the 'Project Menu -> References' and import the control.

Remote Data Object Control

The Form

You should set the data control to 'not visible'.
The command button acts as the 'request', this could also be easy done with a Global Sub call.

In the command button paste this code (Edit Connect and DB table data as needed)
Note this code will only returns ONE row for the DB (that all you will really ever need)

```
Private Sub cmdConnectMySQL_Click()

Dim rs As rdoResultset
Dim cn As New rdoConnection
Dim cl As rdoColumn
Dim SQL As String
Const None As String = ""

cn.Connect = "DSN=;UID=;PWD=;DATABASE=;"

cn.CursorDriver = rdUseOdbc
cn.EstablishConnection rdDriverNoPrompt

' Actual SQL Query Statement

SQL = "SELECT * FROM "

Set rs = cn.OpenResultset(SQL, rdOpenKeyset, _
    rdConcurReadOnly, rdAsyncEnable + rdExecDirect)

    numcol = 0: colnames$ = ""
    For Each cl In rs.rdoColumns
        a$ = cl.Name
        numcols = numcols + 1
        colnames$ = colnames$ + a$ + "*"
    Next
colnames$ = Left$(colnames$, Len(colnames$) - 1)

colvalues$ = ""

' Do Until rs.EOF
For Each cl In rs.rdoColumns
        a$ = cl.Value
        colvalues$ = colvalues$ + a$ + "*"
    numrows = numrows + 1
Next
colvalues$ = Left$(colvalues$, Len(colvalues$) - 1)

Debug.Print "Number Columns: " + Str$(numcols)
Debug.Print "Column Names: " + colnames$
Debug.Print "Column Values: " + colvalues$

End Sub
```
The output is strings with the database data delimited by a '*'
from there it is easy enough to split the data to separate variables.
For the paranoid like me you could encode the string before sending and decode in the client to avoid someone hacking the text string


I hope this might have helped, someone. If not, too bad, I have a grumpy old bastard reputation to look after.

I may or may not continue with this series depending on the reception it gets.

[Robin]

I wouldn't suggest handling remote queries in your main VB6 application. We originally used that for the authentication system used to bridge the forum accounts with the game.

Either run it as a background thread or create a seperate program which handles traffic routing.

Nice tutorial.

[Captain_Starblazer]

@Robin:

> I wouldn't suggest handling remote queries in your main VB6 application. We originally used that for the authentication system used to bridge the forum accounts with the game.
>
> Either run it as a background thread or create a seperate program which handles traffic routing.
>
> Nice tutorial.

Guess I should have stated the above is the SERVER SIDE code not the client. Those result string are what the server will send back to the client after it receives a text request from the client.

There won't be any remote queries at all in the main application (Client). All the client will do is sent the server an 'encrypted' text string stating it wants, say a planet info file thats on the database. When the secured server here receives the text string it will 'decode' it and the run the SQL Query and generate a text string as above which will be encrypted and sent back to the client.

The only thing going between the server <-> remote client will be strings like "&$2gJ^~)W*@#(';"
All SQL queries never leave the local network (and no remote access to the SQL Server)

Cheers

[Robin]

I was talking to the people who're going to see this post and try and re-create the Crystalshire user account bridging system. :P

The framework is fine for a small stand-alone app, but I'm going to cry when people start posting about how their server constantly hiccups after they tried to implement remote authentication.