Excel i MySQL | strona główna: A po co ten Excel ;-) |
||||
Tematyką MySQL zainteresowałem się dosłownie wczoraj. Wyszło jak zwykle z tematu na excelforum.pl + trochu mojej ciekawości | |||||
co do wszelkich nowości (przynajmniej dla mnie nowości :-P). Bo, tak teoretycznie, skoro trochu kumam ADO i SQL to przecież | |||||
jakoś pójdzie. No i jakoś poszło :-) Ciekawych połączenia Excela z MySQL zapraszam do lekturki. | |||||
Function fConnString | |||||
Function CzyJestTabelaADO | |||||
Sub CloseConObject i Sub CloseRSObject | |||||
Sub CreateTableSQL_ADO | |||||
Sub DropTableSQL_ADO | |||||
Sub InsertIntoTableSQL_ADO | |||||
Sub SelectFromMySQL | |||||
Na początek trzeba było zainstalować MySQL. Mi wystarczył zestaw... | |||||
mysql-essential-5.0.51b-win32.msi | Download MySQL | ||||
mysql-gui-tools-5.0-r12-win32.msi | |||||
mysql-connector-odbc-3.51.14-win32.msi | |||||
Jak już instalacje mamy za sobą odpalamy Excela / Edytor VBA i... :-) | |||||
Pierwszym problemem jest utworzenie "ciągu połączenia" ConnectionString. Pomocna będzie strona connectionstrings.com, z której | Connection strings for MySQL | ||||
można ściągnąć przykłady ciągów połączeń do dosłownie wszystkiego ;-) Nas teraz interesuje część poświęcona MySQL (link z prawej) | |||||
Na liście odnajdujemy MySQL Connector/ODBC 3.51 a mnie teraz interesuje Local database | |||||
więc przykład ConnectionString jakiego potrzebuję to: | |||||
Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=myDataBase; User=myUsername;Password=myPassword;Option=3; | |||||
jeżeli jednak wasz MySQL jest np.: zainstalowany na serwerze gdzie będzie miało dostęp do niego szereg użyszkodników lub wymaga | |||||
szeregu innych specyficznych ustawień ( o których natenczas nie mam zielonego pojęcia) wykorzystajcie inny przykład dostępny | |||||
na ww stronie. | |||||
Ja tego ciągu będę używał do każdego polecenia więc napisałem funkcyjkę która go zwraca. Dzięki temu łatwiej również reagować | |||||
na zmiany dot. np.: nazwy użytkownika, bazy... Nie muszę grzebać po każdej procedurze żeby coś zmienić. Wszystko jest w jednym | |||||
miejscu. | |||||
Public Function fConnString() As String | |||||
'http://www.connectionstrings.com/mysql | |||||
'MySQL Connector/ODBC 3.51 | |||||
'Local database | |||||
Const strSerwerName As String = "localhost" | |||||
Const strDatabase As String = "MySQL" | |||||
Const strUserName As String = "root" | |||||
Const strPass As String = "haslo123" | |||||
fConnString = "Driver={MySQL ODBC 3.51 Driver};" & _ | |||||
"Server=" & strSerwerName & ";" & _ | |||||
"Database= " & strDatabase & ";" & _ | |||||
"User=" & strUserName & ";" & _ | |||||
"Password=" & strPass & ";" & _ | |||||
"Option=3;" | |||||
End Function | |||||
root to domyślna nazwa użytkownika. Innego na razie nie zakładałem. (temat na aktualizacje :-P) | |||||
Kolejna funkcja która wydaje mi się potrzebna to CzyJestTabelaADO. Ma ona za zadanie sprawdzić czy tabela o nazwie wskaza- | |||||
nej w arg. strTblName jest już utworzona w bazie danych. | |||||
Public Function CzyJestTabelaADO(objConnection As Object, _ | |||||
strTblName As String) As Boolean | |||||
On Error GoTo CzyJestTabelaADO_Error | |||||
Const adSchemaTables = 20 | |||||
Dim objRecordset As Object | |||||
Set objRecordset = objConnection.OpenSchema(adSchemaTables) | |||||
With objRecordset | |||||
Do Until .EOF | |||||
If UCase(.Fields("TABLE_TYPE").Value) = "TABLE" Then | |||||
If UCase(.Fields("TABLE_NAME").Value) = UCase(strTblName) Then | |||||
CzyJestTabelaADO = True | |||||
Exit Do | |||||
End If | |||||
End If | |||||
.MoveNext | |||||
Loop | |||||
End With | |||||
CzyJestTabelaADO_Exit: | |||||
On Error Resume Next | |||||
CloseRSObject objRecordset | |||||
Exit Function | |||||
CzyJestTabelaADO_Error: | |||||
MsgBox "Błąd Nr - " & Err.Number & vbCrLf & vbCrLf & _ | |||||
Err.Description, vbExclamation, "VBAProject - CzyJesTabADO" | |||||
Resume CzyJestTabelaADO_Exit | |||||
End Function | |||||
Ww funkcja jak i wszystkie procedury wykonujące zadania na bazie danych będą w w ramach obsługi błędów zamykać i usuwać | |||||
tworzone obiekty ADODB.Connection i ADODB.Recordset. Procedury te to... | |||||
Public Sub CloseConObject(Cnn As Object) | |||||
If Not (Cnn Is Nothing) Then | |||||
If Cnn.State = adStateOpen Then Cnn.Close | |||||
Set Cnn = Nothing | |||||
End If | |||||
End Sub | |||||
Public Sub CloseRSObject(Rs As Object) | |||||
If Not (Rs Is Nothing) Then | |||||
With Rs | |||||
If CBool(.State And adStateOpen) Then | |||||
If .EditMode <> adEditNone Then .CancelUpdate | |||||
.Close | |||||
End If | |||||
End With | |||||
Set Rs = Nothing | |||||
End If | |||||
End Sub | |||||
Brakuje nam jeszcze deklaracji stałych zdefiniowanych w bibliotece ADO używanych w procedurach. Mało która procedura będzie | |||||
potrzebowała jakichś specyficznych stałych dlatego wszystkie wykorzystywane stałe zadeklarujemy na poziomie modułu (część de- | |||||
klaracji modułu) żeby były dostępne dla wszystkich procedur. | |||||
Ważnym jest żeby deklaracja tych stałych była w części deklaracji modułu - na samej górze, powyżej funkcji i procedur. Żeby to | |||||
zapewnić proponowałbym na wszystko co powyżej utworzyć oddzielny moduł standardowy. | |||||
W prezentowanym przykładzie wykorzystuję następujące stałe: | |||||
Option Explicit | |||||
Const adUseClient = 3 | |||||
Const adModeRead = 1 | |||||
Const adModeWrite = 2 | |||||
Const adCmdText = 1 | |||||
Const adExecuteNoRecords = 128 | |||||
Const adStateOpen = 1 | |||||
'------------------DataTypeEnum Values-------------------- | |||||
'http://www.w3schools.com/ado/met_comm_createparameter.asp | |||||
Const adVarChar = 200 | |||||
Const adInteger = 3 | |||||
'--------------------------------------------------------- | |||||
Const adParamInput = 1 | |||||
Const adEditNone = 0 | |||||
Wydzielona część stałych dotyczy typów danych. Stałe dla wszystkich typów danych i ich wartości są dostępne na ww stronie. | |||||
Stwórzmy więc tabelę w naszej bazie za pomocą SQL wykorzystując obiekty ADO. | |||||
Sub CreateTableSQL_ADO() | |||||
On Error GoTo CreateTableSQL_ADO_Error | |||||
Dim objConnection As Object 'ADODB.Connection | |||||
Dim strSQL As String | |||||
Dim bErr As Boolean | |||||
Const strTblName As String = "tblTabela" | |||||
Set objConnection = CreateObject("ADODB.Connection") | |||||
With objConnection | |||||
.CursorLocation = adUseClient | |||||
.Open fConnString | |||||
End With | |||||
strSQL = "CREATE TABLE " & strTblName & " " & _ | CREATE TABLE examples | ||||
"(" & _ | |||||
"ID INTEGER, " & _ | |||||
"pole1 VARCHAR(10), " & _ | |||||
"pole2 VARCHAR(10)" & _ | |||||
");" | |||||
If Not CzyJestTabelaADO(objConnection, strTblName) Then | |||||
objConnection.Execute CommandText:=strSQL, _ | |||||
Options:=adExecuteNoRecords | |||||
End If | |||||
MsgBox "Tabela Utworzona :-)", vbInformation | |||||
CreateTableSQL_ADO_Exit: | |||||
On Error Resume Next | |||||
CloseConObject objConnection | |||||
Exit Sub | |||||
CreateTableSQL_ADO_Error: | |||||
MsgBox "Unexpected error - " & Err.Number & vbCrLf & vbCrLf & _ | |||||
Err.Description, vbExclamation, "VBAProject - ExeSQLCom" | |||||
Resume CreateTableSQL_ADO_Exit | |||||
End Sub | |||||
Metoda Execute obiektu ADODB.Connection posiada arg.Options stałą adExecuteNoRecords. Przekazujemy nim informację że zadaniem | |||||
procedury nie jest utworzenie Recordsetu (zestawy danych wynikowych). Ma to za zadanie zoptymalizować czas wykonania zadania. | |||||
To teraz ją usuńmy :-) | |||||
Sub DropTableSQL_ADO() | |||||
On Error GoTo DropTableSQL_ADO_Error | |||||
Dim objConnection As Object 'ADODB.Connection | |||||
Dim strSQL As String | |||||
Const strTblName As String = "tblTabela" | |||||
Set objConnection = CreateObject("ADODB.Connection") | |||||
With objConnection | |||||
.CursorLocation = adUseClient | |||||
.Open fConnString | |||||
End With | |||||
strSQL = "DROP TABLE " & strTblName & ";" | |||||
If CzyJestTabelaADO(objConnection, strTblName) Then | |||||
objConnection.Execute CommandText:=strSQL, _ | |||||
Options:=adExecuteNoRecords | |||||
End If | |||||
MsgBox "Tabela USUNIĘTA :-)", vbInformation | |||||
DropTableSQL_ADO_Exit: | |||||
On Error Resume Next | |||||
CloseConObject objConnection | |||||
Exit Sub | |||||
DropTableSQL_ADO_Error: | |||||
MsgBox "Unexpected error - " & Err.Number & vbCrLf & vbCrLf & _ | |||||
Err.Description, vbExclamation, "VBAProject - ExeSQLCom" | |||||
Resume DropTableSQL_ADO_Exit | |||||
End Sub | |||||
Te procedury były zasadniczo dość proste. Kolejna tj wypełniająca tabelę będzie trudniejsza. | |||||
Sub InsertIntoTableSQL_ADO() | |||||
On Error GoTo InsertIntoTableSQL_ADO_Error | |||||
Dim objConnection As Object 'ADODB.Connection | |||||
Dim objCommand As Object 'ADODB.Command | |||||
Dim bTrans As Boolean | |||||
Dim strSQL_CreateTable As String | |||||
Dim strSQL As String | |||||
Dim i As Long, tblParam(0 To 2) As Variant | |||||
Const strTblName As String = "tblTabela" | |||||
Set objConnection = CreateObject("ADODB.Connection") | |||||
With objConnection | |||||
.CursorLocation = adUseClient | |||||
.Mode = adModeWrite | |||||
.Open fConnString | |||||
.BeginTrans | |||||
bTrans = True | |||||
End With | |||||
If Not CzyJestTabelaADO(objConnection, strTblName) Then | |||||
strSQL_CreateTable = "CREATE TABLE " & strTblName & " " & _ | |||||
"(" & _ | |||||
"ID INTEGER, " & _ | |||||
"pole1 VARCHAR(10), " & _ | |||||
"pole2 VARCHAR(10)" & _ | |||||
");" | |||||
objConnection.Execute CommandText:=strSQL, _ | |||||
Options:=adExecuteNoRecords | |||||
End If | |||||
Set objCommand = CreateObject("ADODB.Command") | |||||
With objCommand | |||||
.ActiveConnection = objConnection | |||||
.CommandType = adCmdText | |||||
.CommandText = "INSERT INTO `" & strTblName & "` (`ID`, `pole1`, `pole2`) " & _ | |||||
"VALUES (?,?,?);" | |||||
.Prepared = True | |||||
.Parameters.Append .CreateParameter(Name:="F1", _ | |||||
Type:=adInteger, _ | |||||
Direction:=adParamInput) | |||||
.Parameters.Append .CreateParameter(Name:="F2", _ | |||||
Type:=adVarChar, _ | |||||
Direction:=adParamInput, _ | |||||
Size:=10) | |||||
.Parameters.Append .CreateParameter(Name:="F3", _ | |||||
Type:=adVarChar, _ | |||||
Direction:=adParamInput, _ | |||||
Size:=10) | |||||
For i = 1 To 15 | |||||
tblParam(0) = i | |||||
tblParam(1) = "test" & i | |||||
tblParam(2) = "test1" & Format(i, "00") | |||||
.Execute Parameters:=tblParam, Options:=adExecuteNoRecords | |||||
Next | |||||
End With | |||||
MsgBox "INSERT INTO przeszło :-)", vbInformation | |||||
objConnection.CommitTrans | |||||
bTrans = False | |||||
InsertIntoTableSQL_ADO_Exit: | |||||
On Error Resume Next | |||||
Set objCommand = Nothing | |||||
CloseConObject objConnection | |||||
Exit Sub | |||||
InsertIntoTableSQL_ADO_Error: | |||||
If bTrans Then | |||||
objConnection.RollbackTrans | |||||
End If | |||||
MsgBox "Unexpected error - " & Err.Number & vbCrLf & vbCrLf & _ | |||||
Err.Description, vbExclamation, "VBAProject - InsIntMDBADO" | |||||
Resume InsertIntoTableSQL_ADO_Exit | |||||
End Sub | |||||
Zadanie polega na przekazaniu do tabeli strTblName zestawu danych. Wykorzystamy do tego klauzulę INSERT INTO jednak trzeba | |||||
będzie powtórzyć ją w pętli określoną ilość razy. | |||||
Zadanie to wymaga parametryzacji zapytania i przekazywania kolejnych zestawów danych zapisanych do tablicy tblParam przekazy- | |||||
wanej do metody Execute obiektu ADODB.Command w arg. Parameters. I jeżeli, ktoś słusznie by zauważył, że poprzednie procedury | |||||
można by sprowadzić do jednego narzędzia do którego przekazywalibyśmy tylko ConnectionString i Zapytanie SQL to w tym przypadku | |||||
nie widzę możliwości takiej automatyzacji. Każde zapytanie może mieć inną ilość parametrów o różnych typach danych. Dlatego cała | |||||
procedura jest zupełnie nie uniwersalna. Na razie innego wyjścia nie znajduję ale, jak starczy chęci i czasu, poeksperymentuję próby | |||||
pominięcia parametryzacji i oparcia się tylko o obiekt ADODB.Connection. Temat uważam za otwarty :-) | |||||
Ważnym jest też żeby cała procedura odbywała się w "ramach Transakcji". Powodem jest możliwość wystąpienia nieoczekiwanego | |||||
błędu który przerwie działanie procedury. Obsługa błędów oczywiści zamknie i usunie co trzeba - baza będzie względnie bezpieczna, | |||||
ale jak sprawdzić jaka część roboty się wykonała a jaka nie :-|. I po to właśnie nam Transakcje. W przypadku błędu wszystko zostanie | |||||
"odkręcone" :-) Nie zostanie utworzona tabela jeżeli jej nie było i nie zostanie przekazany żaden zestaw danych - co likwiduje problem. | |||||
No i wyciągnięcie danych z bazy | |||||
Tu wykorzystam gotowe narzędzie, które już prezentowałem w innym przykładzie który dotyczył zapytań na plikach XLS. Tu jest | |||||
równie pomocne i uniwersalne :-) Chodzi o procedurę ExecuteSQLCommand_ADO do której przekazuję ConnectionString - zwracany | |||||
funkcją fConnString :-), strSQL - zapytanie oparte o klauzulę SELECT, i komórkę do której mają być zwracane dane. | |||||
Sub SelectFromMySQL() | |||||
Dim strSQL As String | |||||
Dim wks As Excel.Worksheet | |||||
Const strTblName As String = "tblTabela" | |||||
strSQL = "SELECT * " & _ | |||||
"FROM " & strTblName & " " & _ | |||||
"WHERE ID > 5;" | |||||
Set wks = ThisWorkbook.Worksheets("Arkusz1") | |||||
ExecuteSQLCommand_ADO fConnString, strSQL, wks.[A1] | |||||
Set wks = Nothing | |||||
End Sub | |||||
Private Sub ExecuteSQLCommand_ADO(strConnectionString As String, _ | |||||
strSQL As String, _ | |||||
rngKomCel As Excel.Range) | |||||
On Error GoTo ExecuteSQLCommand_ADO_Error | |||||
Dim objConnection As Object, objRecordset As Object | |||||
Set objConnection = CreateObject("ADODB.Connection") | |||||
With objConnection | |||||
.CursorLocation = adUseClient | |||||
.Mode = adModeRead | |||||
.Open strConnectionString | |||||
Set objRecordset = .Execute(strSQL, , adCmdText) | |||||
With objRecordset | |||||
If Not (.BOF And .EOF) Then rngKomCel.CopyFromRecordset objRecordset | |||||
End With | |||||
End With | |||||
ExecuteSQLCommand_ADO_Exit: | |||||
On Error Resume Next | |||||
CloseRSObject objRecordset | |||||
CloseConObject objConnection | |||||
Exit Sub | |||||
ExecuteSQLCommand_ADO_Error: | |||||
MsgBox "Byk nr: - " & Err.Number & vbCrLf & vbCrLf & _ | |||||
Err.Description, vbExclamation, "VBAProject - ExeSQLComADO" | |||||
Resume ExecuteSQLCommand_ADO_Exit | |||||
End Sub | |||||
Na razie tyle w temacie ale na pewno jeszcze do tego siądę, a o efektach na pewno kilka słów napiszę. :-)) | |||||
Pozdrawiam tych co dobrnęli do tego fragmentu - Brawo!! W Was nadzieja ;-)) |