.
  • Crear DSN
  • Cadena de conexión
  • Diferencias en SQL
  • Diferencias en SQL II
  • Diferencias en SQL III
  • Usando Recordsets
  • .

    ACCESS vs MySQL

    En este apartado dedicado al MySQL (MariaDb) pretendo 'arrejuntar' algunos tips de los charcos que nos encontramos los acceseros al trabajar con MySQL, no pretende ser un estudio exhaustivo de Access vs MySQL.

    En mi caso, estoy convirtiendo actualmente una aplicación que funciona en entorno local Office-2016 64 bits, tanto front-end como back-end, a un entorno Office365-Pro 64 bits el front-end y los datos en un servidor MySql situado en 'la nube'.

    El primer paso es conseguir que la aplicación siga trabajando con la tablas vinculadas a través de un ODBC, para después, en un segundo paso, ir cambiando (poco a poco) los accesos de los formularios / informes / etc mediante objetos Connection y así eliminar las tablas vinculadas y los ODBC creados.

    Las entradas van a ser desordenadas, según me vaya acordando y tenga tiempo y ganas.

    Un saludo.



    Creación del DSN en nuestros ODBC de 32/64 bits      9-may-2020

    Una vez instalado el driver adecuado a nuestro Office 32 ó 64 bits (el que sea 32 ó 64 depende del Office que usemos no del S.O.) procederemos a crear el DSN en nuestra colección de ODBC.


    Incluyo imágenes de cómo lo he configurado. Datos principales y pestaña Connection:




    y en la pestaña Cursors/Results:



    - es aconsejable marcar el valor:
       Allow big result sets

    - e imprescindible hacerlo con:
       Return matched rows instead of affected rows

    si no marcamos este último nos aparecerá el error:





    Si necesitásemos automatizar la creación del DSN lo podemos hacer mediante VBA. En un módulo independiente:


    Option Compare Database
    Option Explicit

    #If Win64 Then
         Private Declare PtrSafe Function SQLConfigDataSource Lib "ODBCCP32.DLL" ( _
             ByVal hwndParent As Long, ByVal fRequest As Long, _
             ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long
    #Else
         Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" ( _
             ByVal hwndParent As Long, ByVal fRequest As Long, _
             ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long
    #End If

    Function CreaDSN_MySQL8() As Boolean
         Dim Driver As String
         Dim Attributes As String
         Const vbAPINull As Long = 0& ' Puntero NULL
         Const ODBC_ADD_DSN = 1 ' Tipo fRequest
        
         Driver = "MySQL ODBC 8.0 ANSI Driver"
        
         Attributes = "192.168.1.125" & Chr$(0) & _
             "DESCRIPTION=Esta es la descripcion DSN" & Chr$(0) & _
             "DSN=MARCH-TECSYS" & Chr$(0) & _
             "DATABASE=cdt_test" & Chr$(0) & _
             "PWD=****" & Chr$(0) & _
             "UID=JESUS" & Chr$(0) & _
             "PORT=3306" & Chr$(0) & _
             "BIG_PACKETS=1" & Chr$(0) & _
             "FOUND_ROWS=1" & Chr$(0)

         CreaDSN_MySQL8 = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, Driver, Attributes)
    End Function


    La descripción de los atributos y valores la podemos encontrar en la siguiente dirección, así como todo lo relativo a MySQL:
         Site MySQL

    Mis agradecimientos a la comunidad de acceseros de AUGE en general y a Xavi en particular por el código, bueno lo que me pasó era mucho más completo y extenso, yo le he dejado en lo imprescindible.




    Cadena de conexión ODBC      7-sep-2020

    Ya tenemos la aplicación funcionando con tablas vinculadas, ahora viene el segundo paso, el de verdad, crear un objeto Conection y referir todos los accesos a la BD a través del mismo, eliminando las tablas vinculadas.


    En mi caso uso el driver 8.0. Como veis en mi rutina de conexión los pasos son:
    - primero compruebo el estado de la conexión, si es correcta compruebo si puede acceder a una tabla de control (es posible que la conexión este abierta pero no sea valida)
    - si puede realizar la consulta del campo el valor será TRUE y salimos - en caso de que no sea OK, los pasos siguientes son:
       - asegurarnos de que esté cerrada
       - abrir la conexión


    Global CnRemota As New ADODB.Connection

    Function RT_ConexionSLQ1() As Boolean
         Dim NumReg As Long

         ' comprobamos la conexión si esta lanzada / correcta valor a TRUE y salimos
         RT_ConexionSQL = False
         If CnRemota.State = 1 Then
             On Error GoTo Errores1
             CnRemota.Execute "SELECT Codigo_tpar FROM tparametros WHERE Codigo_tpar = 'CONTROL'", NumReg
             RT_ConexionSQL = True
             Exit Function
         End If
           
    SigoConexion:
         ' nos aseguramos de que esté cerrada
         On Error Resume Next
         CnRemota.Close
         Set CnRemota = Nothing
         On Error GoTo 0
        
         ' y ahora la abrimos
         On Error GoTo Errores
       
         CnRemota.ConnectionString = "DRIVER={MySQL ODBC 8.0 ANSI Driver};SERVER=NombreServidor;DATABASE=NombreBd;USER=Usuario;PASSWORD=Clave;OPTION=3;"
         CnRemota.Open
        
         ' nombres de fecha en Español
         CnRemota.Execute "SET lc_time_names = 'es_ES'"
            
         ' todo correcto: salimos
         RT_ConexionSQL = True
         RT_EntornoTecsys
         Exit Function
        
    Errores:
         MsgBox "Hay un problema de conexión con los datos introducidos", vbCritical, TituloMsgbox

    Errores1:
         ' la conexión existe pero no es 'valida', vamos a cerrarla y volverla a crear
         On Error GoTo 0
         GoTo SigoConexion
    End Function


    El comando "SET lc_time_names = 'es_ES'" es importante para el tratamiento de fechas como veremos en otra entrada




    Esas pequeñas diferencias del SQL si atacamos una BD Access o una MySQL:      7-sep-2020

    - No existen las funciones First ni Last, hay que sustituirlas por un Min / Max segun corresponda (y se pueda):

         SELECT Codigo, First(Campo) AS C1 FROM Tabla GROUP BY Codigo -> SELECT Codigo, Min(Campo) AS C1 FROM Tabla ...


    - La cláusula TOP cambia de formato:

         SELECT TOP 10 Codigo, Nombre FROM Tabla -> SELECT Codigo, Nombre FROM Tabla LIMIT 10


    - La función IIF pasa a llamarse IF, poquita cosa xD:

         SELECT IIF(Codigo > 0, 'S', 'N') AS Valor ... -> SELECT IF(Codigo > 0, 'S', 'N') AS Valor ...


    - No existen IsDate IsNumeric IsNull, hay que utilizar Is Null o Not .. Is Null

         WHERE IsDate(MiFecha) AND IsNull(Campo) -> WHERE Not MiFecha Is Null AND Campo Is Null

                la verdad es que esto deberíamos hacerlo en VBA vs Tablas Access, las SQL son más rápidas de ejecución


    - Concatenar campos se hace con la función Concat:

         SELECT Campo1 & '-' & Campo2 AS Valor -> SELECT Concat(Campo1, '-', Campo2) AS Valor


    - El símbolo comodín cambia de * a % (al igual que una BD SQL):

         WHERE Campo1 Like '*abc*' -> WHERE Campo1 Like '%abc%'


    - Los corchetes [] se cambian por ':

         SELECT [Un Campo] AS UnCampo FROM [Esta Tabla] -> SELECT 'Un Campo' AS UnCampo FROM 'Esta Tabla'




    Esas pequeñas diferencias del SQL si atacamos una BD Access o una MySQL II:      8-sep-2020

    - No reconoce TRUE / FALSE, hay que poner los valores 0 y -1

         ... WHERE Campo = True AND Valor = False -> ... WHERE Campo = -1 AND Valor = 0

                 esto trae muchos quebraderos de cabeza, porque la instrucción SQL no funciona, pero no casca, con lo que cuesta acordarse


    - Esta me gusta, no hacen falta los paréntesis para agrupar las JOIN, con lo que según necesitas las agrupaciones vas copiando y pegando:

         " From siniestros" & _
         " LEFT JOIN circunstancias ON siniestros.Si_circunstancias = circunstancias.CIR_Codigo" & _
         " LEFT JOIN situacion ON siniestros.Si_situacion = situacion.SIT_Id" & _
         " LEFT JOIN seccion ON siniestros.Si_seccion = seccion.SEC_Codigo" & _
         " LEFT JOIN centros ON siniestros.Si_centro = centros.CE_id" & _
         " WHERE Id > 1000"


    - en el código anterior ya se ve esta diferencia, según esté definida la BD no reconoce mayúsculas en los nombres de las tablas, te dice que no existe, en cambio en los nombres de los campos y las funciones se puede poner lo que se quiera, se lo traga todo:


    - para borrar mas de una columna con SQL hay que incluir la instrucción DROP COLUMN por cada una de ellas:

         ALTER TABLE Mitabla DROP COLUMN Campo1, DROP COLUMN Campo2


    - para crear una tabla con una consulta de selección no vale el SELECT INTO, el formato es el siguiente:

         CREATE TABLE Mitabla SELECT Campo1, Campo2 FROM OtraTabla ....


    - si queremos crear campos a partir de valores, hay que tener cuidado porque crea el campo a la medida de lo escrito:

         CREATE TABLE Mitabla SELECT Campo1, Campo2, 'X' AS C1, 'XXX' AS C2, 0 AS N1, 00 AS N2, 00.00 AS N3, 000000.00 AS N4 FROM ...

                 C1 -> varchar(1)
                 C2 -> varchar(3)
                 N1 -> int(1)
                 N2 -> Int(2)
                 N3 -> decimal(4,2)
                 N4 -> decimal(8,2)


    - cuando queremos cambiar registros (o borrarlos) en una tabla que no tiene índice se queja, para que no nos de error:

         SET SQL_SAFE_UDATES = 0
         UPDATE Mitabla SET Campo = 0
         SET SQL_SAFE_UDATES = 1


    - La instrucción de borrado de registros va sin el *:

         DELETE * FROM Mitabla -> DELETE FROM Mitabla






    Esas pequeñas diferencias del SQL si atacamos una BD Access o una MySQL III:      10-sep-2020

    - El formato de las instrucciones UPDATE con más de una tabla es muy distinto de como estamos acostumbrados en VBA:

    · por un lado hay que poner un calificador a cada una de las tablas
    · por otro no hay que poner campos en la SELECT, simplemente basta con relacionar las tablas
    · defininir las modificaciones a realizar en la SET
    · por último poner la cláusula WHERE

    Es decir:

    UPDATE (SELECT CampoTabla1, CampoTabla2 FROM Tabla1 LEFT JOIN Tabla2 ON Tabla1.Id = Tabla2.Id WHERE Tabla1.Id = 4)
                 SET CampoTabla1 = CampoTabla2

    quedaría:

    UPDATE Tabla1 AS T1 LEFT JOIN Tabla2 AS T2 ON T1.Id = T2.Id
                 SET CampoTabla1 = CampoTabla2
                 WHERE T1.Id = 4

    A mi me parece más fácil ....



    - la fecha actual se obtiene como curdate() y la hora actual como curtime(), todo junto ni me lo he mirado porque yo siempre pongo por separado fechas y hora, juntos son una fuente de incordios:

           INSERT INTO Tabla1 (Fecha, Hora) VALUES (curdate(), curtime())


    - por cierto la fecha hay que pasarla como: 'aaaa-mm-dd'
      y la hora: 'hh:mm' o 'hh:mm:ss'

           INSERT INTO Tabla1 (Fecha, Hora) VALUES ('2020-09-10', '21:45')


    - Caracteres especiales, secuencias de escape. Al igual que en VBA la comilla simple y la doble dan guerra a la hora de actualizar datos de campos, en MySQL ocurre lo mismo, el que más incordia es el backslah (\) ya que cualquier ruta que vayamos a almacenar lo lleva. Hay que cambiar esos caracteres por una secuencia de escape, las detallo a continuación:

    · Escape Sequence Character Represented by Sequence:
           \0 An ASCII NUL (X'00') character
           \' A single quote (') character
           \" A double quote (") character
           \b A backspace character
           \n A newline (linefeed) character
           \r A carriage return character
           \t A tab character
           \Z ASCII 26 (Control+Z); see note following the table
           \\ A backslash (\) character
           \% A % character; see note following the table
           \_ A _ character; see note following the table

    Más información sobre este tema en: https://dev.mysql.com/doc/refman/8.0/en/string-literals.html



    22-abr-2021   ON DUPLICATE KEY
    Buscando cosas por un lado y por otro pasó por delante de mí una instrucción que no había visto en otros lenguajes SQL y que me picó la curiosidad:
                                   INSERT INTO .…. ON DUPLICATE KEY
     
    Cuántas veces nos encontramos con un proceso en el que tenemos que dar los siguientes pasos:
    • comprobar si existe un registro
    • si existe actualizamos un valor
    • si no existe tenemos que crear el registro
    con las consiguientes instrucciones para su realización.
     
    Pues bien, en MySQL se puede hacer tan fácil como:
    INSERT INTO tparametros (Codigo_tpar, Descripcion_tpar, Valor_tpar)
    VALUES (Codigo1, 'Descripcion del registro', ValorInicial)
    ON DUPLICATE KEY UPDATE Valor_tpar = NuevoValor
    A ver cuando la incluyen en VBA y en SQL.



    Usando Recordsets      13-sep-2020

    Vamos con el último grupo de charcos de esta carrera de obstáculos: los Recordsets.

    - Reconozco que me siento muy cómodo con ellos ... sólo cuándo los tiempos de ejecución lo aconsejan uso SQL puro:
         Conection.Execute "UPDATE ..."

    - Hay que cambiar de usar DAO a ADO y aunque parezca un trabalenguas las diferencias son notables ... personalmente me gusta mas DAO, pero es lo que hay.

    - Para ahorrarme tecleos a la hora de abrirlos y de recordar las distintas opciones que hay que emplear me he creado tres rutinas que me permiten abrir el 99 % de los que voy a emplear:


    Function RT_AbrirRs(SQL As String, Optional TipoCursor As ADODB.CursorTypeEnum = adOpenForwardOnly, _      Optional LockRs As ADODB.LockTypeEnum = adLockReadOnly, _
         Optional LadoCursor As ADODB.CursorLocationEnum = adUseServer) As ADODB.Recordset

         Set RT_AbrirRs = New ADODB.Recordset
         RT_AbrirRs.CursorLocation = LadoCursor
         RT_AbrirRs.Open Lcase$(SQL), CnRemota, TipoCursor, LockRs
    End Function


    Con esta cubro un amplio espectro de posibilidades, ojo a seleccionar bien el tipo de cursor, el bloqueo y el lado cursor (éste último siempre en lado Server, excepto en dónde no hay más remedio que usarlo en lado Client.




    Function RT_AbrirRsLista(SQL As String) As ADODB.Recordset
         Set RT_AbrirRsLista = New ADODB.Recordset
         RT_AbrirRsLista.CursorLocation = adUseClient
         RT_AbrirRsLista.Open Lcase$(SQL), CnRemota, adOpenStatic, adLockReadOnly
    End Function


    Esta segunda la uso para abrir cuadros de lista (y combos), como nota decir que deben ser en lado cliente y al no modificar registros el tipo de cursor es adOpenStatic con bloqueo adLockReadOnly. También sería el que habría que usar si asignáramos el RecordSet de un formulario en sólo lectura.

    En el caso de un formulario con escritura de registros tendrá que ser adOpenDynamic - adLockOptimistic. Aclarar que no uso formularios a los que ponga un origen de registros, siempre uso listas, por tema de bloqueos que repercute en la velocidad y fiabilidad de la aplicación. Como no los uso no he creado rutina para abrirlos.

    Para dar valores a una lista (llamada Usuario) haríamos lo siguiente:

       Set Me.Usuario.Recordset = RT_AbrirRsLista("SELECT Id, Usuario FROM Usuarios WHERE Activo = -1 ORDER BY Usuario")




    Function RT_AbrirRsAdd(ByVal Tabla As String, ByVal CampoId As String, _
         Optional TipoCursor As ADODB.CursorTypeEnum = adOpenKeyset, _
         Optional LockRs As ADODB.LockTypeEnum = adLockOptimistic, _
         Optional LadoCursor As ADODB.CursorLocationEnum = adUseServer, _
         Optional IdAsString As Boolean = False) As ADODB.Recordset
        
         Set RT_AbrirRsAdd = New ADODB.Recordset
         RT_AbrirRsAdd.CursorLocation = LadoCursor
         RT_AbrirRsAdd.Open LCase$("SELECT * FROM " & Tabla & " WHERE " & CampoId & _
                            IIf(IdAsString, " = 'ZZZZZZZZZZZZZ'", " = 0")), CnRemota, TipoCursor, LockRs
    End Function


    Este tercer caso es muy atípico y reconozco que no lo debería usar, pero me gusta mucho más dar de alta un registro con un recordset que con una instrucción SQL, sobre todo cuando tiene un montón de campos, no hay nada que me fastidie más que me de un error de esos crípticos en una consulta de inserción de 30 campos y ... hala, ponte a averiguar cuál de ellos es el culpable, si falta una coma, si sobra un campo, si falta.

    La cuestión es que la apertura de un recordset en dbAppendOnly en ADO no existe ... y se echa de menos porque si abres un recordset con 100.000 registros sin una cláusula WHERE hace que tengas que ver como pasan segundos hasta que ejecuta la apertura (se carga la tabla completa), el truco que he empleado es realizar la apertura como una WHERE por su índice con un valor inexistente con lo que la apertura es inmediata y ya se puede ejecutar a continuación el Rs.addNew, ... Rs.Update.

    Por cierto esto vale para un registro, si intentáis insertar más de uno, la segunda vez que ejecuta el Rs.Update casca miserablemente. Así que en caso de inserción masiva hay que utilizar SQL, pero con una variante, en vez de realizar una instrucción SQL por cada registro lo que hay que construir es una SQL del tipo:

         INSERT INTO Tabla (Campo1, Campo2) VALUES (Valor1, Valor2), (Valor12, Valor22), (Valor13, Valor23)

    Esta forma de hacerlo me la indico Juan Carlos de Contab@t , gracias apañero.



    - Una cuestión importante sobre el lado del cursor, cuando necesitemos usar el valor de RecordCount o conocer el Id del registro que se acaba de crear el cursor debe estar del lado cliente, sino los valores que ofrece no son reales, mis disgustos me ha costado esta tontería.

    - Por cierto, el Id toma valor después de realizar el Update, no justo después del AddNew (¿OK @Xavi?)


         Set Rs = RT_AbrirRsAdd("Usuarios", "Id")
         Rs.AddNew
             Rs!Usuario = "Juan"
             Rs.Update
             'Guardamos el id
             User_Id = Rs!Id
         Rs.Close





    Otra tema aparte es dar formato a los literales de numeros, fechas y horas para poder mostrarlos en listas debidamente alineados. Para ello hay que hacer que los resultados tengan la misma longitud con los blancos necesarios por delante y el fuente debe ser de paso fijo (yo uso normalmente Consolas).

    Como es MySQL el que debe crear los formatos hay que utilizar las funciones de éste, las rutinas que me he creado son las siguientes:


    Function RT_FormHoraMySQL(ByVal Campo As String, Optional Longitud As Integer = 6) As String
         RT_FormHoraMySQL = "LPAD(date_format(" & Campo & ", '%k:%i'), " & Longitud & ", ' ')"
    End Function

    Function RT_FormFechaMySQL(ByVal Campo As String, Optional Longitud As Integer = 10) As String
         RT_FormFechaMySQL = "LPAD(date_format(" & Campo & ", '%e-%b-%y'), " & Longitud & ", ' ')"
    End Function

    Function RT_FormNumeroMySQL(ByVal Campo As String, Optional Longitud As Byte = 14, _
             Optional NumeroDecimales As Byte = 2) As String
         RT_FormNumeroMySQL = "LPAD(FORMAT(" & Campo & ", " & NumeroDecimales & ", 'de_DE')," & Longitud & ", ' ')"
    End Function





    Para llamarlas:

         Xs = "SELECT IDpagos, " & RT_FormFechaMySQL("fechaapunte") & " AS F1, " & _
             RT_FormNumeroMySQL("importepagos", 14, 2) & " AS I1, " & _
             " FROM Tabla …."
         Set Me.Lista.Recordset = RT_AbrirRsLista(Xs)





    Y el resultado:



    En el caso de las fechas para que funcione es necesario haber ejecutado el comando SET lc_time_names al que nos referíamos en la entrada referente a la cadena de conexión ODBC ... si no las abreviaturas de la misma saldrían en Inglés.



    - Un error muy genérico que me surgió y me dió muchos quebraderos de cabeza fué este:

         ** Error: La operación de múltiples pasos de OLE DB generó errores. Compruebe los valores de estado de OLE DB si es posible. No se realizó ningún trabajo.

    El error lo daba en el update de una tabla, y al final resultó ser que por aquello de las pruebas estaba intentando dar de alta un registro cuyo Id estaba duplicado ... ¡Ya podía dar mensajes más descriptivos del error! .... bueno, es lo que hay.




    Obviamente faltan muchas cosas, pero según me vaya acordando o surgiendo las iré posteando. Espero que estas entradas os eviten unos cuantos dolores de cabeza a la hora de afrontar como acceseros el uso de MySQL como BD.




    Tecsys Proyectos Informaticos, S.L.