Conexión a la base de datos MySQL en GAMBAS

 

Es necesario podernos conectar a la base de datos del punto de venta para poder leer los campos y en base a esto crear los archivos del catalogo de la tabla nueva

Los pasos son los siguientes

En caso de tener dificultades para encontrar el componente nos podemos ayudar del buscador de arriba

Creamos el modulo creadorModelo en la carpeta módulos, este archivo contendrá la función con la cual se conectara a la base de datos y leerá la la tabla y con esos datos creara el archivo modelo.php

Ahora pegamos el siguiente código

' Gambas module file

Public Function creaModelo() As String

Dim strModelo As String
Dim conexion As New Connection

  ' AQUI ABRIMOS LA CONEXION
  With conexion
    .Type = "mysql"
    .Port = "3306"
    .Host = Settings["servidor"]
    .User = Settings["usuario"]
    .Password = Settings["password"]
    .Name = Settings["baseDeDatos"]
    .Open()
  End With
  
  Dim $result As Result
  Dim strCampos As String
  Dim strCamposNoLLave As String
  Dim strLLavePrimaria As String
  Dim strCamposValue As String
  Dim strBindingInsert As String
  Dim strBindingUpdate As String
  
  Dim strCamposUpdate As String
  
  
  
  ' Hacemos el query para detectar la llave primaria
   $result = conexion.Exec("SHOW KEYS FROM  " & FMain.txtTabla.Text & " WHERE Key_name = 'PRIMARY'")
   strLLavePrimaria = $result["Column_name"]
   
   
  ' Identificamos los campos de la tabla nueva
   $result = conexion.Exec("describe " & FMain.txtTabla.Text)


' A partir de aqui vamos creando todo el texto que contendra el archivo tabla.modelo.php

Dim contador As Integer


contador = 0 

While $result.Length > contador
  
 ' CAMPOS PARA EL Select
  If contador == 0 Then 
    strCampos = $result["Field"] & gb.CrLf
     Else 
     strCampos = strCampos & "," & $result["Field"] & gb.CrLf
  Endif
  
  'CAMPOS PARA EL INSERT
  
  If $result["Key"] <> "PRI" Then
    If contador == 1 Then 
      strCamposNoLLave = $result["Field"] & gb.CrLf
      Else 
      strCamposNoLLave = strCamposNoLLave & "," & $result["Field"] & gb.CrLf
    Endif
    
  Endif
  
  
  
    ' CAMPOS PARA EL VALUE
    
    If $result["Key"] <> "PRI" Then
      If contador == 1 Then 
        strCamposValue = ":" & $result["Field"] & gb.CrLf
      Else 
        strCamposValue = strCamposValue & "," & ":" & $result["Field"] & gb.CrLf
        Endif
    Endif
    
     If $result["Key"] <> "PRI"
      strBindingInsert = strBindingInsert & "     $stmt -> bindParam(\":" & $result["Field"] & "\", $datos[\"nuevo" & UCase(Mid$($result["Field"], 1, 1)) & Mid$($result["Field"], 2, 100) & "\"], PDO::PARAM_STR);" & gb.CrLf
    Endif
    
    ' CAMPOS UPDATE
    If $result["Key"] <> "PRI" Then
      
      If contador == 1 Then 
          
          strCamposUpdate = strCamposUpdate & $result["Field"] & "= :" & $result["Field"] 
          
        Else 
          
           strCamposUpdate = strCamposUpdate & "," & $result["Field"] & "= :" & $result["Field"] 
          
      Endif
      
    Endif
    
    strBindingUpdate = strBindingUpdate & "     $stmt -> bindParam(\":" & $result["Field"] & "\", $datos[\"editar" & utilerias.strPrimeraMayuscula($result["Field"]) & "\"], PDO::PARAM_STR);" & gb.CrLf

  $result.MoveNext
  contador = contador + 1



Wend

strModelo = ""
strModelo &= "<?php" & gb.CrLf
strModelo &= "require_once \"conexion.php\";" & gb.CrLf
strModelo &= "" & "" & gb.CrLf



strModelo &= "" & "Class Modelo" & UCase(Mid$(FMain.txtTabla.text, 1, 1)) & Mid$(FMain.txtTabla.text, 2, 25) & " {" & gb.CrLf
strModelo &= "" & "   /* =============================================" & gb.CrLf
strModelo &= "" & "     MOSTRAR " & UCase(FMain.txtTabla.text) & gb.CrLf 
strModelo &= "" & "      ============================================= */" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "    Static Public Function mdlMostrar($tabla, $item, $valor) {" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "       If ($item != Null) {" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "           $stmt = Conexion:: conectar() -> prepare( \"Select " & strCampos

strModelo &= "" & "           From " & FMain.txtTabla.Text & " a WHERE $item =:$item \"); " " " & gb.CrLf 
strModelo &= "" & "" & gb.CrLf


strModelo &= "" & "           $stmt -> bindParam( \":\" .$item, $valor, PDO::PARAM_STR);" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "           Try {" & gb.CrLf
strModelo &= "" & "               $stmt -> execute();" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "                Return $stmt -> fetch();" & gb.CrLf
strModelo &= "" & "           } Catch (PDOException $e) {" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "               $arr = $stmt -> errorInfo();" & gb.CrLf
strModelo &= "" & "                $arr[3] = \" Error \";" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "               If ($e -> getMessage() == 23000) {" & gb.CrLf
strModelo &= "" & "                   $mensaje = \" El registro esta duplicado, Favor de checar el numero de nomina \";" & gb.CrLf
strModelo &= "" & "                   Return $mensaje;" & gb.CrLf
strModelo &= "" & "               } Else {" & gb.CrLf
strModelo &= "" & "                  Return $arr[2];" & gb.CrLf
strModelo &= "" & "              }" & gb.CrLf
strModelo &= "" & "           }" & gb.CrLf
strModelo &= "" & "           " & gb.CrLf
strModelo &= "" & "           " & gb.CrLf
strModelo &= "" & "       } Else {" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "           $stmt = Conexion:: conectar() -> prepare(\"Select * " "" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "           " & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "           From " & FMain.txtTabla.Text & " a \"); " "" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "           $stmt -> execute();" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "           Return $stmt -> fetchAll();" & gb.CrLf
strModelo &= "" & "       }" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "       $stmt -> close();" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "        $stmt = Null;" & gb.CrLf
strModelo &= "" & "   }" & gb.CrLf
strModelo &= "" & "" & gb.CrLf

' REGISTRO

strModelo &= "" & "   /* ==================================================================" & gb.CrLf
strModelo &= "" & "     REGISTRO" & gb.CrLf
strModelo &= "" & "    ==================================================================== */" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "   Static Public Function mdlIngresar($tabla, $datos) {" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "      $stmt = Conexion:: conectar() -> prepare(\"INSERT INTO " & FMain.txtTabla.Text & "(" & strCamposNoLLave & "" "" & gb.CrLf
strModelo &= "" & "        " & gb.CrLf
strModelo &= "" & "                                                                       )" & gb.CrLf
strModelo &= "" & "                                                                       VALUES(" & strCamposValue & ")" & gb.CrLf
strModelo &= "" & "                          " & gb.CrLf
strModelo &= "" & "                                                                              \"); " "" & gb.CrLf
strModelo &= "" & ""

strModelo &= "" & strBindingInsert & gb.CrLf

strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "       If ($stmt -> execute()) {" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "           Return \"ok\";" & gb.CrLf
strModelo &= "" & "       } Else {" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "           $arr = $stmt -> errorInfo();" & gb.CrLf
strModelo &= "" & "           $arr[3] = \" Error \";" & gb.CrLf
strModelo &= "" & "          Return $arr[2];" & gb.CrLf
strModelo &= "" & "      }" & gb.CrLf

strModelo &= "" & "      $stmt -> close();" & gb.CrLf

strModelo &= "" & "      $stmt = Null;" & gb.CrLf
strModelo &= "" & "  }" & gb.CrLf



'EDITAR ACTUALIZAR
strModelo &= "" & "  /* ==================================================================" & gb.CrLf
strModelo &= "" & "   EDITAR " & gb.CrLf
strModelo &= "" & "     ================================================================== */" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "   Static Public Function mdlEditar($tabla, $datos) {" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "       $stmt = Conexion:: conectar() -> prepare(\" UPDATE $tabla SET " & strCamposUpdate & " " "" & gb.CrLf
strModelo &= "" & ""
strModelo &= "" & "                                                                   WHERE id =:" & strLLavePrimaria & "  \"); " "" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & strBindingUpdate
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "       If ($stmt -> execute()) {" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "           Return \"ok\";" & gb.CrLf
strModelo &= "" & "     } Else {" & gb.CrLf
strModelo &= "" & ""
strModelo &= "" & "          Return \"Error\";" & gb.CrLf
strModelo &= "" & "      }" & gb.CrLf
strModelo &= "" & ""
strModelo &= "" & "     $stmt -> close();" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "      $stmt = Null;" & gb.CrLf
strModelo &= "" & "   }" & gb.CrLf
strModelo &= "" & "   " & gb.CrLf
strModelo &= "" & " " & gb.CrLf & gb.CrLf
strModelo &= "" & "  /* ===================================================================" & gb.CrLf
strModelo &= "" & "     BORRAR USUARIO" & gb.CrLf
strModelo &= "" & "     =================================================================== */" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "  Static Public Function mdlBorrar($tabla, $datos) {" & gb.CrLf

strModelo &= "" & "       $stmt = Conexion:: conectar() -> prepare( \" DELETE From " & FMain.txtTabla.Text & " WHERE id =:id \");" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "       $stmt -> bindParam(\":id\", $datos, PDO::PARAM_INT);" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "      If ($stmt -> execute()) {" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "Return \"ok\";" & gb.CrLf
strModelo &= "" & "      } Else {" & gb.CrLf

strModelo &= "" & "          Return \"Error\";" & gb.CrLf
strModelo &= "" & "      }" & gb.CrLf

strModelo &= "" & "        $stmt -> close();" & gb.CrLf

strModelo &= "" & "       $stmt = Null;" & gb.CrLf
strModelo &= "" & "    }" & gb.CrLf
strModelo &= "" & "" & gb.CrLf
strModelo &= "" & "}" & gb.CrLf

Return strModelo
  
End

Ahora explicamos las partes importantes

Aquí vemos como abrir una conexión a la base de datos, se observa como se usan las variables de configuración programadas en la publicación anterior

Y aquí vemos como ejecutar los querys en la base de datos y guardamos el resultado en la variable $result y luego leemos el nombre de la columna asignase la a la variable strLLavePrimeria

Podemos ejecutar cualquier query por ejemplo conexion.Exec("select * from usuarios")