[RESUELTO] Sentencia SQL relación muchos-muchos con ejemplo

Discute sobre las herramientas de la base de datos

[RESUELTO] Sentencia SQL relación muchos-muchos con ejemplo

Notapor Fdv » Dom Feb 20, 2011 7:25 pm

Hola amigos,

Antes de nada, agradeceros mucho el foro que tenéis porque me esta ayudando muchísimo a crear mi base de datos, que compartiré cuando este terminada porque creo que puede ser útil a muchos.

Ahora bien, estoy ahora un pelín parado, a ver si me podéis ayudar...

Mi base de datos, contiene entre otras, tres tablas:
- PERSONAS
- VEHICULOS
- VINCULOS PERSONASVEHICULOS

Bien, Personas registra personas, vehículos registra vehículos y Vinculos PersonasVehiculos vincula a Registros concretos de personas con registros concretos de vehículos, con lo cual, existe una relación muchos a muchos, una persona puede estar relacionada con muchos vehículos y cualquier vehículo puede estar relacionado a la vez con muchas personas.

Mediante Formularios y Subformularios, he creado procedimientos para realizar las vinculaciones, no obstante he tenido que recurrir a sentencias SQL.

Pero, ahora mismo, estoy haciendo un formulario para introducir datos rápidamente, en el cual, yo introduzco datos de personas, de vehiculos y su vinculación según interese. Por tanto, una macro se encarga de comprobar si he rellenado texto en persona (entonces habría que crear un registro nuevo en la tabla personas), si he rellenado en vehículo (habría que meter un registro en la tabla vehículos) o ambos (habría que introducir registros en ambas tablas y crear un registro en la tabla vinculos personasvehiculos para la vinculación)

¿ COMO PUEDO OBTENER LOS IDS AUTONUMERICOS CREADOS AL INSERTAR LOS NUEVOS REGISTROS?

La tabla VINCULOS PERSONASVEHICULOS esta compuesta por:
- Id
- IdTipoVinculo
- IdPersona
- IdVehiculo

Con sentencias "Insert Into" puedo crear nuevos registros de personas y de vehiculos pero, no sé como obtener el "id" autonumerico nuevo creado que necesito para realizar el "insert into" en la tabla VINCULOS PERSONASVEHICULOS.

¿ Me podéis ayudar? o... Existe alguna forma más sencilla de hacerlo mediante formularios. Podéis indicarme algún manual sobre este tema...

Estoy como loco buscando y no solo en openoffice base, y no encuentro nada...

Muchas Gracias por Leer el mensaje y muchísimas más para el que me ayude.

Un Saludo y eso, luego subiré mi base de datos que me parece muy interesante.
Última edición por Fdv el Mié Feb 23, 2011 4:29 pm, editado 2 veces en total
OOo 3.3 Corriendo en Windows 7
En mi Espacio para OpenOffice Base tienes programas, recursos y vídeos que he ido creando... visítalo
Fdv
 
Mensajes: 104
Registrado: Lun Abr 05, 2010 6:19 pm

Re: Sentencia SQL para relación muchos a muchos

Notapor Fdv » Lun Feb 21, 2011 10:04 am

Bueno, ahora mismo estoy estudiando si puedo crear un formulario adaptando este http://user.services.openoffice.org/es/forum/viewtopic.php?f=33&t=4653&p=16324&hilit=insert+into#p16447 de cascabel ... Creo que podrá servir, pero me sería más interesante poder hacerlo directamente desde una sentencia SQL....

En fin, no pongo [Resuelto] porque aún estoy en ello...
OOo 3.3 Corriendo en Windows 7
En mi Espacio para OpenOffice Base tienes programas, recursos y vídeos que he ido creando... visítalo
Fdv
 
Mensajes: 104
Registrado: Lun Abr 05, 2010 6:19 pm

Re: Sentencia SQL para relación muchos a muchos

Notapor mauricio » Lun Feb 21, 2011 3:38 pm

Hola...

Inmediatamente que haces la inserción del nuevo registro, puedes ejecutar:
Código: Seleccionar todo   Expandir vistaContraer vista
select max(id) as ultimo from PERSONAS

para obtener el ultimo id insertado...

Saludos
______________________________________________
"Todo cuanto no podemos dar nos posee". - André Gide
AOO 4.0 | LibO 4.0 | ArchLinux | Gnome3
No respondo preguntas privadas, por favor, usa el foro
Avatar de Usuario
mauricio
 
Mensajes: 3418
Registrado: Sab Nov 22, 2008 5:36 am
Ubicación: México, D.F.

Re: Sentencia SQL para relación muchos a muchos

Notapor Fdv » Mar Feb 22, 2011 9:03 pm

Muchas gracias mauricio,

Había leído por ahí sobre el uso de usar select max, pero pensaba que existía algún tipo de función en SQL para obtener los idautomaticos automaticamente (valga la redundancia) con sentencias insert into anidadas o algo parecido. Ya que el tema creo que debe ser bastante común en base de datos, y puede existir problemática usando "select max" cuando la base de datos esta siendo compartida en red (no va a ser mi caso).

Ahora no dispongo de mucho tiempo, he hecho pruebas y funciona, pero de todas formas mañana termino la macro y la enseño para el resto...
OOo 3.3 Corriendo en Windows 7
En mi Espacio para OpenOffice Base tienes programas, recursos y vídeos que he ido creando... visítalo
Fdv
 
Mensajes: 104
Registrado: Lun Abr 05, 2010 6:19 pm

Re: Sentencia SQL para relación muchos a muchos

Notapor Fdv » Mié Feb 23, 2011 4:26 pm

Bueno, ahí esta la macro terminada, creo que puede ser útil para alguno... aunque supongo que pronto subiré la base de datos al completo...

Voy a explicar también un poquito como trabaja la macro, si alguien quiere contestar con alguna sugerencia, queja o consulta será bien recibida.

Para empezar:

- El formulario no está vinculado a ninguna Tabla, ni consulta, ni sentencia SQL.
- Existen controles campos de texto para cada dato que se introducirá en las tablas, salvo uno de fecha y dos de listados.

las Tablas en cuestión implicadas son varias:

  • Personas (Tabla que registra personas)
  • Vehiculos (Tabla que registra vehículos)
  • Vinculacion personas vehiculos (Tabla que registra relaciones entre personas y vehiculos, en relación muchos con muchos, ademas una persona puede estar relacionada al mismo vehículo varias veces, con distinto tipo de vinculo (conductor, pasajero, propietario...))
  • Documentos de Identificacion (Una persona puede tener varios documentos de identificacion (DNI, PASAPORTE, Tarjeta sanitaria, etc...), por tanto se necesita una tabla aparte)
  • Alias de persona (Los motes que puede tener una persona en concreto)

Existen otra tablas como

  • Tipo de documento. (esta tabla no la empleo para evitar complejidad, se entiende que el documento introducido es DNI (id=0))

Y luego las usadas en los listados

  • Tipo de Vehiculos (moto, coche, etc...)
  • Tipo Vinculacion Personas con Vehiculos (ya explicado, conductor, pasajero...)

¿ Como se rellenan los listados ?, pues con el siguiente macro que se inicia al cargar el formulario:

Código: Seleccionar todo   Expandir vistaContraer vista
Sub ER_IniciarListados(Evento as Object)

   Dim oForm as Object, Listado as Object, sSQL as string   
   oForm=Evento.Source
   
   Listado=oForm.getByName("LstVinculo")
   
   sSQL="SELECT ""TipoVinculacionPersoVehi"", ""Id"" FROM ""Tipo Vinculacion Personas con Vehiculos"" ORDER BY ""TipoVinculacionPersoVehi"" ASC"
   
   Rellena_ListBox (Listado, sSQL)
   
   if Ubound(Listado.ListSource)>0 then Listado.SelectedItems=Array(Listado.ListSource(2))
      ' Ojo, establecido el orden, el valor por defecto "Conductor", se encuentra en la posicion 2

   Listado=oForm.getByName("LstTipo")
   sSQL="SELECT ""TipoDeVehiculo"", ""Id"" FROM ""Tipo de Vehiculos"" "

   Rellena_ListBox (Listado, sSQL)
   
   if Ubound(Listado.ListSource)>0 then Listado.SelectedItems=Array(Listado.ListSource(0))
      ' Ojo, establecido el orden, el valor por defecto "Turismo", se encuentra en la posicion 0
      
End Sub

Sub Rellena_ListBox(oControl as Object, sSQL as string, Optional MostrarError as boolean)

   ' Rellena listados incluyendo Texto y Ids, para ello hay que enviar una consulta SQL donde el primer
   ' campo será el texto a rellenar y el segundo el Id (u otra información que deba almacenar el control)

   If IsMissing(MostrarError) then MostrarError=true

   dim  oStat As Object, oRes As Object
   oStat=ThisDatabaseDocument.CurrentController.ActiveConnection.CreateStatement()
   oStat.setPropertyvalue("ResultSetType", com.sun.star.sdbc.ResultSetType.SCROLL_SENSITIVE)
   oRes=oStat.executeQuery(sSQL)
   Dim VarItems() As String, VarTexto() as String, Posicion as integer
   Posicion=0
   
   If Not IsNull(oRes) Then
      While oRes.next
         redim preserve VarItems(posicion)
         redim preserve varTexto(posicion)
         VarItems(posicion)=str(oRes.getInt(2)) ' Id
         VarTexto(posicion)=oRes.getString(1) ' Tipo de Vinculacion
         posicion=posicion+1
         Wend
         oControl.StringItemList=VarTexto() 'Informacion adicional
         oControl.ListSource=VarItems()
          'listado.SelectedItems=VarTexto
       else
      if MostrarError then msgbox "Error al rellenar el ListBox ("+oControl.name+"), la base de datos no contine registros",16,"Error"
      endif
End sub


Como detalle, se puede observar como a cada campo listado, ademas del texto a mostrar, introduzco en cada opción un dato extra, el id del registro.

El Procedimiento ER_IniciarListados se encarga de localizar los ListBox a rellenar, construir las sentencias SQL, enviarlas al procedimiento Rellena_ListBox para rellenar cada listado con el resultado de esa sentencia SQL y luego añade un valor por defecto (esto ultimo no es necesario)

Ahora el meollo de la cuestión, la macro encargada de introducir los datos. Ojo, como veréis, se comprueba si existe la necesidad de introducir un registro en Personas, o/y introducirlo en Vehiculos o si es necesario realizar la vinculación:

Código: Seleccionar todo   Expandir vistaContraer vista

Sub CrearRegistros (Evento as Object)

   Dim oForm as Object   
   oForm=Evento.Source.model.parent

   Dim NombreCampos as String, DatosCampos as string
   
   Dim sSQL As String, oStat As Object, oRes As Object
   Dim IdPersona as integer, IdVehiculo as integer, Introducidos as integer
   Introducidos=0
   
   NombreCampos="": DatosCampos=""
   
   CompruebaRegistro ("Nombre","TxtNombre",oForm,NombreCampos,DatosCampos)
   CompruebaRegistro ("Apellido_1","TxtApellido1",oForm,NombreCampos,DatosCampos)
   CompruebaRegistro ("Apellido_2","TxtApellido2",oForm,NombreCampos,DatosCampos)
   CompruebaRegistro ("Fecha_Nac","TxtFecha",oForm,NombreCampos,DatosCampos)
   CompruebaRegistro ("Notas","TxtNotasPersona",oForm,NombreCampos,DatosCampos)
   
   if NombreCampos<>"" then
      ' Metemos personas
      sSQL="INSERT INTO ""Personas"" ("+NombreCampos+") VALUES ("+DatosCampos+"); Call Identity()"
      oStat=ThisDatabaseDocument.CurrentController.ActiveConnection.CreateStatement()
      oRes=oStat.executeQuery(sSQL)
      
      If Not IsNull(oRes) then
         Introducidos=Introducidos+1
         oRes.Next
          IdPersona=oRes.GetInt(1)
          else
          msgbox "Error al introducir la persona": exit sub
          endif
          
       ' Entrada de DNI o Alias de existir.
       if oForm.getByName("TxtDNI").text<>"" then
          sSQL="INSERT INTO ""Documentos de Identificacion"" (""IdPersona"",""IdTipo"",""Numero"") VALUES ("+IdPersona+",0,'"+oForm.getByName("TxtDNI").text+"')"
          oRes=oStat.executeQuery(sSQL)
          endif
       if oForm.getByName("TxtAlias").text<>"" then
          sSQL="INSERT INTO ""Alias de Personas"" (""IdPersona"",""Alias"") VALUES ("+IdPersona+",'"+oForm.getByName("TxtAlias").text+"')"
          oRes=oStat.executeQuery(sSQL)          
          endif
       else
      
       ' Error al meter datos en base, advertir por si acaso de que el registro de persona no ha sido introducido
       if oForm.getByName("TxtDNI").text<>"" then msgbox "No basta con introducir DNI, hay que incluir otro dato sobre la persona.",16,"No se puede añadir el registro": Exit sub
       if oForm.getByName("TxtAlias").text<>"" then msgbox "No basta con introducir Alias, hay que incluir otro dato sobre la persona.",16,"No se puede añadir el registro": Exit sub
       
   endif
   
   ' Metemos Vehiculo
   NombreCampos="": DatosCampos=""
   CompruebaRegistro ("Matricula","TxtMatricula",oForm,NombreCampos,DatosCampos)
   CompruebaRegistro ("Marca","TxtMarca",oForm,NombreCampos,DatosCampos)
   CompruebaRegistro ("Modelo","TxtModelo",oForm,NombreCampos,DatosCampos)
   CompruebaRegistro ("Notas","TxtNotasVehiculo",oForm,NombreCampos,DatosCampos)
   
   if NombreCampos<>"" then
      CompruebaRegistro ("IdTipo","LstTipo",oForm,NombreCampos,DatosCampos)
      ' Metemos el vehiculo
      sSQL="INSERT INTO ""Vehiculos"" ("+NombreCampos+") VALUES ("+DatosCampos+"); Call Identity()"
      oStat=ThisDatabaseDocument.CurrentController.ActiveConnection.CreateStatement()
      oRes=oStat.executeQuery(sSQL)
      If Not IsNull(oRes) then
         Introducidos=Introducidos+1
         oRes.Next
          IdVehiculo=oRes.GetInt(1)
          else
          msgbox "Error al introducir el vehiculo": exit sub
          endif
      endif

   if introducidos=2 then
      'Vinculamos los dos
      Dim vTipoVinculo as integer, indice as integer
      indice=oForm.getByName("LstVinculo").SelectedItems(0)   
       vTipoVinculo=oForm.getByName("LstVinculo").ValueItemList(indice)      
                  
      sSQL="INSERT INTO ""Vinc Personas-Vehiculos"" (""IdPersona"", ""IdVehiculo"", ""TipoDeVinculo"") VALUES ("+IdPersona+","+IdVehiculo+","+vTipoVinculo+")"
      oRes=oStat.executeQuery(sSQL)
      endif
   
   if introducidos>0 then msgbox "Registro Introducido",64,"Información": LimpiaEntradas (oForm)
   
End Sub

Sub CompruebaRegistro (ByVal NCampo as string, ByVal NControl as string,ByRef oForm as object, ByRef NombreCampos as string, ByRef DatosCampos as string)
   
   Dim DatoIntroducido as string
   If NControl<>"LstTipo" then
      DatoIntroducido=oForm.getByName(NControl).text
      else
      DatoIntroducido="Valor en Listado"
      endif
   
   if DatoIntroducido<>"" then
      if NombreCampos<>"" then
         NombreCampos=NombreCampos+", "
         DatosCampos=DatosCampos+", "
         endif
      NombreCampos=NombreCampos+""""+NCampo+""""
      Select Case NCampo
         Case "Fecha_Nac":
            DatosCampos=DatosCampos+"'"+Format(oForm.getByName(NControl).text,"YYYY-MM-DD")+"'"
         Case "IdTipo":
            Dim indice as integer
            indice=oForm.getByName(NControl).SelectedItems(0)   
            DatosCampos=DatosCampos+oForm.getByName(NControl).ValueItemList(indice)
         Case Else:
            DatosCampos=DatosCampos+"'"+oForm.getByName(NControl).text+"'"
         end Select
      endif
End Sub

Sub LimpiaEntradas(ByRef oForm as Object)
   oForm.getByName("TxtNombre").text=""
   oForm.getByName("TxtApellido1").text=""
   oForm.getByName("TxtApellido2").text=""
   oForm.getByName("TxtFecha").text=""
   oForm.getByName("TxtNotasPersona").text=""
   oForm.getByName("TxtDNI").text=""
   oForm.getByName("TxtAlias").text=""
   oForm.getByName("TxtMarca").text=""
   oForm.getByName("TxtModelo").text=""
   oForm.getByName("TxtMatricula").text=""
   oForm.getByName("TxtNotasVehiculo").text=""
End Sub



La función CrearRegistros, debe estar enlazada al botón del formulario designado para proceder a introducir los datos.

La función CrearRegistros, llama a la función CompruebaRegistro, que es la que se encarga de comprobar el dato introducido en el campo de texto, para así ir creando la consulta SQL de ser necesario. La función CompruebaRegistro la he creado para que el código sea más limpio.

Al final de crear el registro, se limpian los campos de texto, con la función LimpiaEntradas.

En el código, se ve claro los nombres de los controles empleados y los nombres de los campos de la base de datos.

Lo suyo, sería que antes de introducir los datos en la base de datos, se comprobaran si existe la persona o vehiculo, ya en base, para evitar duplicados. No lo he hecho para evitar complejidad, quizás más adelante.

Espero que sea de utilidad.

Nota: Queda pendiente, si alguien sabe alguna forma de introducir los registros mediante instrucciones SQL anidadas, a fin de evitar el procedimiento "Select Max(id)"....
Última edición por Fdv el Dom Feb 27, 2011 10:41 am, editado 1 vez en total
OOo 3.3 Corriendo en Windows 7
En mi Espacio para OpenOffice Base tienes programas, recursos y vídeos que he ido creando... visítalo
Fdv
 
Mensajes: 104
Registrado: Lun Abr 05, 2010 6:19 pm

Re: [Resuelto] Sentencia SQL relación muchos-muchos con eje

Notapor Fdv » Vie Feb 25, 2011 7:40 pm

Acabo de editar mi ejemplo, y con esto ya RESUELVO completamente mis dudas...

Existe una sentencia mejor que
Código: Seleccionar todo   Expandir vistaContraer vista
select max(id) as ultimo from PERSONAS

Para obtener el id del ultimo registro introducido, y es Call Identity()

Ademas, la sentencia SQL la concateno, y así es todo más claro...

El código entero para introducir un registro en la tabla y obtener el id introducido sería (nota, la tabla Personas posee un campo llamado id el cual es del tipo integer, autoincrementado y llave maestra de la tabla):

Código: Seleccionar todo   Expandir vistaContraer vista
sSQL="INSERT INTO ""Personas"" (""Nombre"",""Apellidos"") VALUES ('Antonio','Perez'); Call Identity()"
oStat=ThisDatabaseDocument.CurrentController.ActiveConnection.CreateStatement()
oRes=oStat.executeQuery(sSQL)
      
If Not IsNull(oRes) then
        oRes.Next
     IdPersona=oRes.GetInt(1)
    endif


Mi ejemplo del Post anterior ha sido editado aplicando esta forma de introducir los datos.

Según he leido en HSQL, Call Identity obtiene el Id del ultimo insert realizado en una conexión. Por tanto no hay problemas de errores a la hora de captarlo.
OOo 3.3 Corriendo en Windows 7
En mi Espacio para OpenOffice Base tienes programas, recursos y vídeos que he ido creando... visítalo
Fdv
 
Mensajes: 104
Registrado: Lun Abr 05, 2010 6:19 pm

Re: [RESUELTO] Sentencia SQL relación muchos-muchos con eje

Notapor Fdv » Dom Feb 27, 2011 10:49 am

Acabo de editar otra vez mi ejemplo de como realizar la tarea en el sentido de iniciar los ListBox...

No me gustaba como estaba el código desarrollado, además no facilita el que los procedimientos puedan ser aprovechados en más ocasiones, así que he creado el procedimiento para buscar los ListBox en el formulario en cuestion y crear las sentencias SQL y luego, otro procedimiento genérico para rellenar los datos en el ListBox. (el procedimiento rellena los datos con el resultado de la consulta y con los ids relacionados a cada registro)

Ejemplo de iniciar los listbox:

Código: Seleccionar todo   Expandir vistaContraer vista
Sub ER_IniciarListados(Evento as Object)

   Dim oForm as Object, Listado as Object, sSQL as string   
   oForm=Evento.Source
   
   Listado=oForm.getByName("LstVinculo")
   
   sSQL="SELECT ""TipoVinculacionPersoVehi"", ""Id"" FROM ""Tipo Vinculacion Personas con Vehiculos"" ORDER BY ""TipoVinculacionPersoVehi"" ASC"
   
   Rellena_ListBox (Listado, sSQL)
   
   if Ubound(Listado.ListSource)>0 then Listado.SelectedItems=Array(Listado.ListSource(2))
      ' Ojo, establecido el orden, el valor por defecto "Conductor", se encuentra en la posicion 2

   Listado=oForm.getByName("LstTipo")
   sSQL="SELECT ""TipoDeVehiculo"", ""Id"" FROM ""Tipo de Vehiculos"" "

   Rellena_ListBox (Listado, sSQL)
   
   if Ubound(Listado.ListSource)>0 then Listado.SelectedItems=Array(Listado.ListSource(0))
      ' Ojo, establecido el orden, el valor por defecto "Turismo", se encuentra en la posicion 0
      
End Sub



Procedimiento: Rellenar_ListBox

Código: Seleccionar todo   Expandir vistaContraer vista
Sub Rellena_ListBox(oControl as Object, sSQL as string, Optional MostrarError as boolean)

   ' Rellena listados incluyendo Texto y Ids, para ello hay que enviar una consulta SQL donde el primer
   ' campo será el texto a rellenar y el segundo el Id (u otra información que deba almacenar el control)

   If IsMissing(MostrarError) then MostrarError=true

   dim  oStat As Object, oRes As Object
   oStat=ThisDatabaseDocument.CurrentController.ActiveConnection.CreateStatement()
   oStat.setPropertyvalue("ResultSetType", com.sun.star.sdbc.ResultSetType.SCROLL_SENSITIVE)
   oRes=oStat.executeQuery(sSQL)
   Dim VarItems() As String, VarTexto() as String, Posicion as integer
   Posicion=0
   
   If Not IsNull(oRes) Then
      While oRes.next
         redim preserve VarItems(posicion)
         redim preserve varTexto(posicion)
         VarItems(posicion)=str(oRes.getInt(2)) ' Id
         VarTexto(posicion)=oRes.getString(1) ' Tipo de Vinculacion
         posicion=posicion+1
         Wend
         oControl.StringItemList=VarTexto() 'Informacion adicional
         oControl.ListSource=VarItems()
          'listado.SelectedItems=VarTexto
       else
      if MostrarError then msgbox "Error al rellenar el ListBox ("+oControl.name+"), la base de datos no contine registros",16,"Error"
      endif
End sub


De esta forma, para averiguar el id relacionado a la opcion seleccionada del listbox utilizamos
Código: Seleccionar todo   Expandir vistaContraer vista
Id=oForm.getByName("Listado").ValueItemList(oForm.getByName("Listado").SelectedItems(0))


Agradecería cualquier critica o sugerencia
OOo 3.3 Corriendo en Windows 7
En mi Espacio para OpenOffice Base tienes programas, recursos y vídeos que he ido creando... visítalo
Fdv
 
Mensajes: 104
Registrado: Lun Abr 05, 2010 6:19 pm


Volver a Base

¿Quién está conectado?

Usuarios navegando por este Foro: No hay usuarios registrados visitando el Foro y 2 invitados