mySQL: How to implement/use?

[jian2587]

I've a hosted domain, get SQL support.
But how do I use it? I was just given my username and password
for that SQL database.
Wut should I use and do?

Thx!

[mxnmx]

Well, just open Query Analyzer if you have any queries to run. If you wish to Compact the database, create tables, delete tables, edit tables open Enterprise Manager...so what do you want to do?

[wpearsall]

OK This is how i am doing mine,
Its working great!

Declarations:

Const mySqlHost = "localhost"
Const DataBase = "myDataBase"
Const MySqlUID = "root"
Const MySqlUID = ""

Usage:

On Error GoTo Error_Msg:
Dim oConn As New ADODB.Connection
Dim myrs As New Recordset
Dim mySQL As String
 
' Creating the Connection Socket
oConn.Open "Driver={mySQL};" & _
           "Server=" & mySqlHost & ";" & _
           "Port=3306;" & _
           "Option=131072;" & _
           "Stmt=;" & _
           "Database=" & DataBase & ";" & _
           "Uid=" & MySqlUID & ";" & _
           "Pwd=" & MySqlPass & ";"
 
' SQL Query to preform,
' Remember when using WHERE stuff, you need to use ' around stuff like "User" etc.
mySQL = "SELECT uid, uname, pass from " & UserTable & " WHERE uname='" & User & "'"
 
' Just set the cursor location etc for the record set
myrs.CursorLocation = adUseClient
' Create the record set By executing the MySQL query (Dont need a record set for Update / Delete / Insert :)
Set myrs = oConn.Execute(mySQL)
 
' Now, There may be an error if no records are found, if checking The Password agains the password in the Database (myrs("pass"))
On Error GoTo CheckError:
 
    If (myrs("pass") = Password) Then
              ' Do my authorize access here
    Else
              ' Do my unauthorized access here
    End If
 
' We use a "GoTo" for the error, so i use this for it :)
GoTo CloseUp:
 
Exit Sub
 
CloseUp:
      ' Close the connection
      oConn.close
      ' Created a Object to connect, so lets delete it
      set oConn = Nothing
      ' As well as delete the record set
      set myrs = Nothing
Exit Sub
 
CheckError:
    If Err.Number = 3021 Then ' 3021 = No Records Found
        ' Since no records are found, there isnt a username like dat,
        ' Do failed Login
        GoTo CloseUp:
    Else
        MsgBox Err.Description
        Err.Clear
        Resume Next
    End If
Exit Sub

REMEMBER: ALWAY DELETE MADE CONTROLS
ALWAYS CLOSE CONNECTIONS!

Insert use like:

SQL = "INSERT INTO " & Table & " (field1, field2, field3) VALUES ('Field1Val', 'Field2Val', 'Field3Val')"

To do a "While(List(...)...){":

Do until mrrs.EOF
      ' Use it however you need to here
      txtLog.SelText = myrs("uname") & vbcrlf
      ' Move to the next record
      myrs.MoveNext
Loop

Hope this helps

[jian2587]

Thx a lot!
I know SQL statements, but about connecting and stuffs like that,
can u pls tell me more about it?

[wpearsall]

'OK, Lets start on the Basics:
 
' Names Can be anything, as long as you tell VB What it is!
' Always Dim it (of course) Before you use it, 
' (Best to do Dimming at the top, unless its a REALLY long function,
' and with some, its best to create the object as you need it, else Lag-O-Ramma)
 
' Can use anything as the Connection name:
' --> Dim oConn As New ADODB.Connection <-- Can be:
Dim MyUsers As New ADODB.Connection
' Very great for when you need to do more then one Database Connection at a time etc :)
 
' Same goes for the record set, Can have 6 million or whatever
' (Doubt any system would handle that much usage tho ;))
Dim myrs As New Recordset
 
' Just the string (YOU DONT NEED TO DO IT LIKE THIS,
' SEE LOWER)
Dim mySQL As String
 
 
' Creating the Connection Socket
' This is the "ADODB.Connection": YourName.Open
 
' You can hard code the stuff in, but if you wanna allow the user to change the stuff,
' on demange, use a INI file or something, but all the same:
oConn.Open "Driver={mySQL};" & _
           "Server=" & mySqlHost & ";" & _
           "Port=3306;" & _
           "Option=131072;" & _
           "Stmt=;" & _
           "Database=" & DataBase & ";" & _
           "Uid=" & MySqlUID & ";" & _
           "Pwd=" & MySqlPass & ";"
 
' ------------------------------------------------------------
 
MyNewDIMMEDName.Open "Driver={mySQL};" & _
           "Server=MyWebHost.com;" & _
           "Port=" & UseYourMysqlPortNumberHere_Default_3306 & ";" & _
           "Option=131072;" & _
           "Stmt=;" & _
           "Database=MyDataBaseNameHere;" & _
           "Uid=MyMySQLUName;" & _
           "Pwd=*******;"
 
' NEVER use your "Root" MySQL name for distributed App's,
' DONT Give em an easy ride ;) Make their Username:
' SELECT / (Delete if needed) / (Update If Needed) / ETC.
' Only what they need to do!
 
' Remember the mySQL was dimmed as String?
mySQL = "SELECT uid, uname, pass from " & UserTable & " WHERE uname='" & User & "'"
 
' Well, you dont HAVE to do it like this. Doing:
Set myrs = oConn.Execute("SELECT myField1, MyField2 FROM MyTableName Where MyField3='MyNeededSearch'")
 
' Deleting you can just do:
 oConn.Execute("DELETE FROM .... WHERE .... = ' ... '")
' Updating Do:
 oConn.Execute("Update ... Set .. = '...' WHERE ...='...'")
 
' To use your selected Record set:
Text1.Text = myrs("FieldName")
 
' MySQL (to my knowledge) Doesn't allow you to do row count :(
' Anyone knows how do do this Properly, Brill ;)
' I do:
Do Until myrs.EOF
lstRCount.AddItem myrs("field1")
myrs.movenext
Loop
myrs.movefirst
 
Dim Count as Integer
Count = lstRCount.ListCount

And so on, If you tell me something Specific you need help with, i would be more then happy to (Since There Was quite a few people who helped me) ' No good at "Manual Things"

I aint no VB MySQL Expert, but it isnt that hard to learn, once you get into it

I Would say it is easier then PHP / MySQL some of the time (RecordCount is a little sad tho )

[jian2587]

wpearsall:
Thx! Thx! Thx!

I started to understand sumthin'
but what about the option argument as in the Open method?
Can u explain 2 me 1 by 1 of all the arguments in the Open
method?

And BTW, do u think Access 97 or 2k will allow me to update
server database (as in this case)?
Thx a lot!

[wpearsall]

How do u mean, (Access update?)

oConn.Open u mean ?

"Driver={mySQL};" '< Tell the system it needs to connect to a MySQL Server
"Server=" & mySqlHost & ";" '< Tell the System, The Host Name (localhost / MySite.Com / ***.***.***.***)
"Port=3306;" '< Port number too, Since some MySQL Run on a different Port
"Option=131072;"' < This i dont know (Lol)
"Stmt=;" '< Same goes for this
"Database=" & DataBase & ";" ' < What database THIS connection is using (You can make more then one connection (oConn1. oConn2. oConn3 etc, but u gotta dim all of em ;)
"Uid=" & MySqlUID & ";" '< User ID (As i said before, Dont use use Root Access Name(s).. Make a new user (on phpmyadmin, its in the front page "Users") .. Best to be DB Specific, (Security and all ;))
"Pwd=" & MySqlPass & ";" '< Password.  U can set this to "Pwd=;" if it is PW-less ;)

ADO + Access though, not got to dat part yet (I was starting to use it, but opted for MySQL, Since my user-base is MySQL)

But basically the same, (There is a few differences etc tho)

OH And BTW,:

[sql]
Where MyField != 'myval'
[/sql]

Dat results in, ??? WHERE The Value of MyField IS NOT myval (Just like PHP

OH BTW, Did u Install the myODBC Driver for MySQL yet?

[jian2587]

Uh...not sure 'bout that.

By Access I meant Microsoft Access. Does it allow me to update
my online database? If yes, things will be simpler