Conexão MySQL com VB 6

[Klayton de Oliveira 0]

Mostra como você está fazendo.

Olá Luiz, gostaria de saber qual a melhor forma de connecção ao mysql.Cheguei a criar uma classe para iss chamada RepositorioMySqlO que eu quero é ter um único objeto de conexão ao mysql dentro de minha aplicaçãoa classe é o seguinte:Option ExplicitPublic Enum StatusConexao adStateClosed = 0 'Conexão esta fechada adStateOpen = 1 'Conexão esta aberta adStateConnecting = 2 'Conexão em andamento adStateExecuting = 4 'Executando instrução SQL adStateFetching = 8 'Retornando dadosEnd Enum'Private vNomeDSN As String = App.Path & ".\Reports\SISTEMA.dsn"'local variable(s) to hold property value(s)Private vDBmyCon As New ADODB.ConnectionPrivate vDBmyCmd As New ADODB.CommandPrivate vDBmyRS As New ADODB.Recordset'Private mvarvDBmySql As StringPrivate mvarvDBConector As String 'local copyPrivate mvarvDBServidor As String 'local copyPrivate mvarvDBNomeINIConector As String 'local copyPrivate mvarvDBPorta As String 'local copyPrivate mvarvDBUser As String 'local copyPrivate mvarvDBPassword As String 'local copyPrivate mvarvDBBanco As String 'local copyPrivate mvarvDBStatusConexao As Integer 'local copyPrivate mvarvDBStringConexao As String 'local copyPrivate vDBNomeDsn As StringPrivate vDBNomeINI As StringPublic Property Let vDBStringConexao(ByVal vData As String)'used when assigning a value to the property, on the left side of an assignment.'Syntax: X.vDBStringConexao = 5 mvarvDBStringConexao = vDataEnd PropertyPublic Property Get vDBStringConexao() As String'used when retrieving value of a property, on the right side of an assignment.'Syntax: Debug.Print X.vDBStringConexao vDBStringConexao = mvarvDBStringConexaoEnd PropertyPublic Property Let vDBStatusConexao(ByVal vData As Integer)'used when assigning a value to the property, on the left side of an assignment.'Syntax: X.vDBStatusConexao = 5 mvarvDBStatusConexao = vDataEnd PropertyPublic Property Get vDBStatusConexao() As Integer'used when retrieving value of a property, on the right side of an assignment.'Syntax: Debug.Print X.vDBStatusConexao' Permite conhecer o modo do estado da conexão do banco de dados vDBStatusConexao = mvarvDBStatusConexaoEnd PropertyPublic Property Let vDBBanco(ByVal vData As String)'used when assigning a value to the property, on the left side of an assignment.'Syntax: X.vDBBanco = 5 mvarvDBBanco = vDataEnd PropertyPublic Property Get vDBBanco() As String'used when retrieving value of a property, on the right side of an assignment.'Syntax: Debug.Print X.vDBBanco vDBBanco = mvarvDBBancoEnd PropertyPublic Property Let vDBPassword(ByVal vData As String)'used when assigning a value to the property, on the left side of an assignment.'Syntax: X.vDBPassword = 5 mvarvDBPassword = vDataEnd PropertyPublic Property Get vDBPassword() As String'used when retrieving value of a property, on the right side of an assignment.'Syntax: Debug.Print X.vDBPassword vDBPassword = mvarvDBPasswordEnd PropertyPublic Property Let vDBUser(ByVal vData As String)'used when assigning a value to the property, on the left side of an assignment.'Syntax: X.vDBUser = 5 mvarvDBUser = vDataEnd PropertyPublic Property Get vDBUser() As String'used when retrieving value of a property, on the right side of an assignment.'Syntax: Debug.Print X.vDBUser vDBUser = mvarvDBUserEnd PropertyPublic Property Let vDBPorta(ByVal vData As String)'used when assigning a value to the property, on the left side of an assignment.'Syntax: X.vDBPorta = 5 mvarvDBPorta = vDataEnd PropertyPublic Property Get vDBPorta() As String'used when retrieving value of a property, on the right side of an assignment.'Syntax: Debug.Print X.vDBPorta vDBPorta = mvarvDBPortaEnd PropertyPublic Property Let vDBNomeINIConector(ByVal vData As String)'used when assigning a value to the property, on the left side of an assignment.'Syntax: X.vDBNomeINIConector = 5 mvarvDBNomeINIConector = vDataEnd PropertyPublic Property Get vDBNomeINIConector() As String'used when retrieving value of a property, on the right side of an assignment.'Syntax: Debug.Print X.vDBNomeINIConector vDBNomeINIConector = mvarvDBNomeINIConectorEnd PropertyPublic Property Let vDBServidor(ByVal vData As String)'used when assigning a value to the property, on the left side of an assignment.'Syntax: X.vDBServidor = 5 mvarvDBServidor = vDataEnd PropertyPublic Property Get vDBServidor() As String'used when retrieving value of a property, on the right side of an assignment.'Syntax: Debug.Print X.vDBServidor vDBServidor = mvarvDBServidorEnd PropertyPublic Property Let vDBConector(ByVal vData As String)'used when assigning a value to the property, on the left side of an assignment.'Syntax: X.vDBConector = 5 mvarvDBConector = vDataEnd Property'---------------------------------------------------------------------------------------' Procedure : vDBConector' DateTime : 14/3/2007 21:30' Author : KLAYTON' Purpose :'---------------------------------------------------------------------------------------'Public Property Get vDBConector() As String'used when retrieving value of a property, on the right side of an assignment.'Syntax: Debug.Print X.vDBConector On Error GoTo vDBConector_Error vDBConector = mvarvDBConector On Error GoTo 0 Exit PropertyvDBConector_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure vDBConector of Módulo de classe RepositorioMySql"End PropertyPublic Function CriarRepositorio()Dim vConector As StringDim vServidor As StringDim vPorta As StringDim vUser As StringDim vPassword As StringDim vBanco As String '//VERIFICA SE O ARQUIVO DBConnection.ini If Len(Dir$(vDBNomeINI)) = 0 Then MsgBox "Arquivo de conexão ao Banco de Dados não existe! Gere este arquivo com o Utilitário DBConnection localizado na pasta do sistema. Ou se preferir entre em contato com o suporte: Klayton de Oliveira. Fone: 81-9216.5011", vbExclamation + vbOKOnly End Else '// Inicializa as variáveis vConector = DeCriptSenha(LeEntradaIni("ODBC", "DRIVER", "", vDBNomeINI)) vServidor = DeCriptSenha(LeEntradaIni("ODBC", "SERVIDOR", "", vDBNomeINI)) vPorta = DeCriptSenha(LeEntradaIni("ODBC", "PORT", "", vDBNomeINI)) ' DeCriptSenha(GetSetting("Tools", "Options", "My_Port", "CHAVE NÃO EXISTE")) vUser = DeCriptSenha(LeEntradaIni("ODBC", "UID", "", vDBNomeINI)) ' DeCriptSenha(GetSetting("Tools", "Options", "My_User", "CHAVE NÃO EXISTE")) vPassword = DeCriptSenha(LeEntradaIni("ODBC", "PWD", "", vDBNomeINI)) 'DeCriptSenha(GetSetting("Tools", "Options", "My_Password", "CHAVE NÃO EXISTE")) vBanco = DeCriptSenha(LeEntradaIni("ODBC", "DATABASE", "", vDBNomeINI)) ' DeCriptSenha(GetSetting("Tools", "Options", "My_Database", "CHAVE NÃO EXISTE")) If FU_TestaConexao(vConector, vServidor, vPorta, vUser, vPassword, vBanco) = False Then MsgBox "Não foi possível se conectar ao Servidor. Refaça a conexão." End Else vDBConector = vConector vDBServidor = vServidor vDBPorta = vPorta vDBUser = vUser vDBPassword = vPassword vDBBanco = vBanco vDBStringConexao = FU_CriaStringConexao(vConector, vServidor, vPorta, vUser, vPassword, vBanco) 'Set DB = CreateObject(RepositorioMySql) 'MySqlOpen ' Dim NomeBAT As String, NomeSQL As String ' NomeBAT = "c:\mysql.bat" ' NomeSQL = "c:\" & App.EXEName & Format(Date, "ddmmyy") & Format(Time, "hh") & ".sql" ' If Len(Dir$(NomeSQL)) = 0 Then ' Open NomeBAT For Output As #1 ' Print #1, "cd.." ' Print #1, "cd.." ' Print #1, "cd.." ' Print #1, "cd.." ' Print #1, "cd.." ' Print #1, "cd.." ' Print #1, "mysqldump --host=" & vDBServidor & " --user=" & vDBUser & " --port=" & vDBPorta & " --password=" & vDBPassword & " --opt scg > " & NomeSQL ' 'Print #1, "pause" ' Close #1 ' ' If Len(Dir$(NomeBAT)) > 0 Then ' Dim iTask As Long, Ret As Long, pHandle As Long ' 'BACKUP.Show ' iTask = Shell(Chr(34) & NomeBAT & Chr(34), vbHide) ' pHandle = OpenProcess(SYNCHRONIZE, False, iTask) ' Ret = WaitForSingleObject(pHandle, INFINITE) ' Ret = CloseHandle(pHandle) ' 'exclui o arquivo mysql.bat ' Call Kill("c:\mysql.bat") ' 'Unload BACKUP ' End If ' End If 'MsgBox " /c mysqldump --host=" & vServidor & " --user=" & vUser & " --port=" & vPorta & " --password=" & vPassword & " --opt scg > " & Chr(34) & App.Path & "\" & Date & "_" & Time & ".sql" & Chr(34) 'Shell Environ$("COMSPEC") & " /c mysqldump --host=" & vServidor & " --user=" & vUser & " --port=" & vPorta & " --password=" & vPassword & " --opt scg > " & Chr(34) & App.Path & "\" & Format(Date, "dd-mm-yyyy") & "_" & Time & ".sql" & Chr(34) 'Call Shell("mysqldump --host=" & vServidor & " --user=" & vUser & " --port=" & vPorta & " --password=" & vPassword & " --opt scg > " & Chr(34) & App.Path & "\" & Format(Date, "dd-mm-yyyy") & "_" & Time & ".sql" & Chr(34) & " pause", vbNormalFocus) End If End If' Matar as classes não utilizadas' Set Crypt = Nothing End FunctionPublic Function OpenDB() On Error GoTo OpenDB_Error vDBmyCon.Open vDBStringConexao ' StringConnection '"driver={MySQL ODBC 3.51 Driver};server=localhost;database=cat;uid=root;pwd=kofb@123;port=3307;option=2;option=35;option=16834;option=18475" vDBStatusConexao = vDBmyCon.State On Error GoTo 0 Exit FunctionOpenDB_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure OpenDB of Módulo de classe RepositorioMySql" End FunctionPublic Function CloseDB() On Error GoTo CloseDB_Error If vDBmyCon.State = ADODB.adStateOpen Then vDBmyCon.Close Set vDBmyCon = Nothing End If vDBStatusConexao = vDBmyCon.State On Error GoTo 0 Exit FunctionCloseDB_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CloseDB of Módulo de classe RepositorioMySql" End FunctionPublic Function Insert(ByVal Sql As String) As Boolean If vDBStatusConexao = StatusConexao.adStateOpen Then Call ExecuteSQL(Sql) End IfEnd FunctionPublic Function Delete(ByVal Sql As String) As Boolean On Error GoTo Delete_Error If vDBStatusConexao = StatusConexao.adStateOpen Then Call ExecuteSQL(Sql) End If On Error GoTo 0 Exit FunctionDelete_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Delete of Módulo de classe RepositorioMySql"End FunctionPublic Function Edit(ByVal Sql As String) As Boolean If vDBStatusConexao = StatusConexao.adStateOpen Then Call ExecuteSQL(Sql) End IfEnd FunctionPublic Function Update(ByVal Sql As String) As Boolean If vDBStatusConexao = StatusConexao.adStateOpen Then Call ExecuteSQL(Sql) End IfEnd FunctionPublic Sub ExecuteSQL(Sql As String) On Error GoTo Error_ExecuteSQL With vDBmyCmd Set .ActiveConnection = vDBmyCon .CommandType = adCmdText .CommandText = Sql End With With vDBmyRS .LockType = adLockPessimistic .CursorType = adOpenKeyset .CursorLocation = adUseClient .Open vDBmyCmd End With 'myRS.MoveFirst 'MostraNumeroRegistro 'Mostra_dados 'vDBmyRS.Close Set vDBmyRS = Nothing Set vDBmyCon = Nothing On Error GoTo 0 Exit Sub Error_ExecuteSQL: MsgBox Err.Number & vbCrLf & Err.Description, vbExclamation, "Error_ExecuteSQL" 'NomeAdodc.ConnectionString = "Provider=MSDASQL.1;Password='';Persist Security Info=True;User ID=root;Data Source=" & XNOME_BANCO & ";option=35" 'NomeAdodc.RecordSource = SQL 'NomeAdodc.Refresh 'nomeAdodc.Recordset.Close End SubPublic Function CriarTB(Sql As String) As ADODB.RecordsetDim rs As New ADODB.Recordset'define os parâmetros do recordset On Error GoTo CriarTB_Errorrs.CursorType = adOpenStaticrs.CursorLocation = adUseClientrs.LockType = adLockOptimisticrs.ActiveConnection = vDBmyCon' executa a consuta e retorna um recordsetrs.Open SqlSet CriarTB = rs On Error GoTo 0 Exit FunctionCriarTB_Error: MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CriarTB of Módulo de classe RepositorioMySql"End FunctionPublic Function FU_TestaConexao(ByVal Driver As String, ByVal Server As String, ByVal Port As String, ByVal User As String, ByVal Password As String, ByVal DB As String) As BooleanDim myCon As New ADODB.ConnectionDim StringDeConexao As String StringDeConexao = FU_CriaStringConexao(Driver, Server, Port, User, Password, DB) On Error GoTo Error 'Debug.Print StringDeConexao myCon.Open StringDeConexao ' & "klayton de olivceira ferreira barbosa" If myCon.State = 1 Then FU_TestaConexao = True 'vDBStringConexao = StringDeConexao ElseIf myCon.State = 0 Then FU_TestaConexao = False 'vDBStringConexao = "" 'StringDeConexao End If myCon.Close Set myCon = NothingError: If Not Err.Number = 0 Then MsgBox Err.Number & vbCrLf & Err.Description, vbExclamation, "Erro em [FU_TestaConexao]" End If 'NomeAdodc.ConnectionString = "Provider=MSDASQL.1;Password='';Persist Security Info=True;User ID=root;Data Source=" & XNOME_BANCO & ";option=35" 'NomeAdodc.RecordSource = SQL 'NomeAdodc.Refresh 'nomeAdodc.Recordset.Close End FunctionPublic Function FU_CriaStringConexao(ByVal Driver As String, ByVal Server As String, ByVal Port As String, ByVal User As String, ByVal Password As String, ByVal DB As String) As String FU_CriaStringConexao = "driver={" & Driver & "};server=" & Server & ";database=" & DB & ";uid=" & User & ";pwd=" & Password & ";port=" & Port & ";option=35;option=16834;option=18475"End FunctionPublic Function FU_VerificaEstadoDaConexao() As Integer 'FU_VerificaEstadoDaConexao = v 'FU_TestaConexao(vDBConector, vDBServidor, vDBPorta, vDBUser, vDBPassword, vDBBanco) = True ' vDBStatusConexao = vDBmyCon.State ' FU_VerificaEstadoDaConexao = vDBmyCon.State' Select Case vDBmyCon.State' Case adStateClosed' vDBStatusConexao = "Conexão esta fechada"''' Case adStateOpen' vDBStatusConexao = "Conexão esta aberta"''' Case adStateConnecting' vDBStatusConexao = "Conexão em andamento"''' Case adStateExecuting' vDBStatusConexao = "Executando instrução SQL"'' Case adStateFetching' vDBStatusConexao = "Retornando dados"'' Case Else' vDBStatusConexao = "Estado desconhecido."'' End SelectEnd FunctionPrivate Sub Class_Initialize() vDBNomeDsn = App.Path & ".\Reports\SISTEMA.dsn" vDBNomeINI = App.Path & "\DBConnection" & App.EXEName & ".ini" CriarRepositorioEnd SubPrivate Sub Class_Terminate() DBCloseEnd SubPublic Sub Create_Dsn() Dim registros As Integer Open vDBNomeDsn For Output As #1 Print #1, "[ODBC]" Print #1, "DRIVER=" & vDBConector Print #1, "SERVIDOR=" & vDBServidor Print #1, "PORT=" & vDBPorta Print #1, "UID=" & vDBUser Print #1, "PWD=" & vDBPassword Print #1, "DATABASE=" & vDBBanco Close #1 'MsgBox "Foi criado o Arquivo: " & nome_dsn End Sub

[flaviojr 0]

Eu utlizo conexao via ODBC configuro o driver myodbc 3.51 no painel de controle e crio a conexão em um modulo onde crio o OBjeto de conexão e abro os Objetos recordsets nos respectivos forms.No modulo:Option Explicit Global objCN As ADODB.Connection Global objrs As ADODB.Recordset Global objStream As ADODB.Stream Global ConectaMySQL As String Global sSql As String Public Sub abrirConexao() Set objCN = New ADODB.Connection ConectaMySQL = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=MeuBanco;User=root;Password=flaviojr;Option=1 + 2 + 8 + 32 + 2048 + 16384;" objCN.CursorLocation = adUseClient objCN.Open ConectaMySQL End Sub++++++++++++++++++++++++++++++++++++++++++++++++++++no form deixo o recordsetabrirConexaoSet objrs = New ADODB.Recordset objrs.CursorLocation = adUseClient objrs.CursorType = adOpenStatic objrs.LockType = adLockOptimistic objrs.ActiveConnection = objCN Set objrs = objCN.Execute("SELECT * FROM tabela ")+++++++++++++++++++++++++++++++++++++++++Até mais