miércoles, 7 de mayo de 2014

ON DUPLICATE KEY UPDATE, EQUIVALENTE EN ACCESS SQL


La sentencia On duplicate Key Update (MySql), permite insertar o actualizar registros, dependiendo de si existe o no la clave en la tabla. Me parece algo sumamente útil. Veamos como implementar algo parecido en Access, y para ello nada más elocuente que un ejemplo :

Supongamos que tenemos una tabla donde anotamos el estoc de productos :

Create Table Stocs (IdArticulo Long Primary Key, Cantidad Single);

Cuando recibimos un nuevo pedido, necesitamos anotar la cantidad del mismo en la tabla. Pero para no tener que preocuparnos en buscar si IdArticulo existe, en cuyo caso empleariamos una sentencia Insert, o en el supuesto contrario usariamos Update, podemos utilizar una sql como esta :

Update Stocs
            Right Join
            (Select Top 1 [IdArticulo ?] As IdArticulo From MSysObjects) As T
            On Stocs.IdArticulo = T.IdArticulo
Set Stocs.IdArticulo = T.IdArticulo, Stocs.Cantidad = Nz(Stocs.Cantidad,0) + 1;

... utilizamos una tabla derivada con orígen en MSysObjects para obtener un único registro con el valor que definamos en el parámetro [IdArticulo ?] . Al realizar un Update, teniendo las dos tablas relacionadas con Right Join, si en la tabla de la izquierda no hay registro coincidente con la de la derecha, el efecto práctico será la inserción de un nuevo registro, mientras que en caso contrario, la sentencia Update actuará de la forma esperada. Para modificar o insertar valores en el resto de campos, podemos utilizar valores o expresiones según nos convengan en la clausula Set. En este caso y a título de ejemplo, incrementaremos en una unidad el valor del campo Cantidad (si el registro todavia no existe el valor asignado será 1).

raipon. Terrassa a 07/05/2014.

lunes, 27 de mayo de 2013

INTERCALACIÓN AI EN ACCESS (NO DISTINGUIR ACENTOS)


Por defecto, el motor sql de Access distingue siempre el uso de acentos en las comparaciones de cadena : "acción" <> "accion". Por el contrario obvia el uso de mayúsculas y minúsculas : "Acción" = "acción". Sobre esto último, ya hablé en este artículo : Distinguir mayúsculas y minúsculas. Hablemos ahora de los acentos, cuestión de vital importancia a la hora de realizar búsquedas sobre texto.
Para evitar que una búsqueda tenga en cuenta los acentos, es habitual crear una función en vba. Dichas funciones pueden seguir dos estrategias : 
- Modificar el valor pasado al parámetro de búsqueda. Un magnífico ejemplo de esta estrategia es la función Buscaacent de Chea.
- Modificar los valores del campo donde se busca, quitando todos los acentos del mismo, y también realizar la misma operación en el parámetro de búsqueda. 

Creo que la mejor opción es la primera, pues la función se ejecutará una sola vez, mientras que en el segundo caso, la función deberá actuar sobre el campo escogido en todos los registros del conjunto de datos, además de sobre el valor de búsqueda. Es decir, si tenemos una tabla de Clientes con 10.000 registros, la función se ejecutará 10.000 veces. A pesar de esto propongo una solución basada en la segunda posibilidad, pero utilizando la función StrConv que viene con el módulo Strings de la librería VBA.  Esta solución será útil si no queremos depender de tener un módulo con la función personalizada, o si deseamos utilizar la propiedad filter de los recordsets Ado, ya que Filter es mucho más restrictiva en el uso de comodines para el operador Like que el entorno Sql de Access.

StrConv adapta una cadena según el tipo de conversión escogida en el segundo parámetro de la función, y según el LocaleID definido en la tercera. A priori esto no da mucho 'juego' en cuanto a los acentos, pero resulta que si usamos idiomas que emplean un alfabeto distinto del nuestro, la mayoría obvian los acentos de las vocales. Uno de los que he probado y que eliminan correctamente los acentos es el Coreano, cuyo LCid es 1042.

Un ejemplo :

Select StrConv("aâàáäeëèéiïíîöôoòúuüûñ", 2, 1042)

devuelve :

aaaaaeeeeiiiioooouuuun

... y sí, a la 'ñ' le ha quitado el acento. Si no hacemos de esto una cuestión de honor, StrConv bien puede emplearse para obviar los acentos.

Así pues, para realizar una búsqueda obviando los acentos, deberiamos hacer algo parecido a esto (he empleado la tabla Clientes de Neptuno.mdb) :

Select NombreCompañía, NombreContacto
From Clientes
Where StrConv(NombreContacto, 2, 1042) Like '*hernandez*'

... y el resultado :


Nombre de compañíaNombre del contacto
HILARIÓN-AbastosCarlos Hernández



lunes, 10 de mayo de 2010

JUGANDO CON LAS MATES (NÚMEROS PRIMOS)


Un pequeño divertimento : generar una tabla de números primos (de 0 a 1000) usando la tabla auxiliar Nums (con un único campo, entero, indexado -sin duplicados-, de nombre Num, con valores de 1 a 1000).

Select Nums.Num * T.Num
From Nums, Nums As T
Where Nums.Num * T.Num Between 1 And 1000
Group By Nums.Num * T.Num
Having Count(Nums.Num * T.Num)=2;

... y si queremos prescindir de la tabla Nums :

SELECT R1.Num * R2.Num
FROM
(
Select (T2.Num*10)+Nums.Num+1 AS Num
From
(
Select (T1.Num*10)+Nums.Num As Num
From
(
Select Id - 2 AS Num From msysobjects Where Id Between 2 And 5
Union All
Select Id + 2 AS Num From msysobjects Where Id Between 2 And 5
Union All
Select Id + 6 AS Num From msysobjects Where Id Between 2 And 3
) As Nums,
(
Select Id - 2 AS Num From msysobjects Where Id Between 2 And 5
Union All
Select Id + 2 AS Num From msysobjects Where Id Between 2 And 5
Union All
Select Id + 6 AS Num From msysobjects Where Id Between 2 And 3
) AS T1
) AS T2, Nums
) AS R1,
(
Select (T2.Num*10)+Nums.Num+1 AS Num
From
(
Select (T1.Num*10)+Nums.Num As Num
From
(
Select Id - 2 AS Num From msysobjects Where Id Between 2 And 5
Union All
Select Id + 2 AS Num From msysobjects Where Id Between 2 And 5
Union All
Select Id + 6 AS Num From msysobjects Where Id Between 2 And 3
) AS Nums,
(
Select Id - 2 AS Num From msysobjects Where Id Between 2 And 5
Union All
Select Id + 2 AS Num From msysobjects Where Id Between 2 And 5
Union All
Select Id + 6 AS Num From msysobjects Where Id Between 2 And 3
) AS T1
) AS T2, Nums
) AS R2
WHERE R1.Num * R2.Num Between 1 And 1000
GROUP BY R1.Num * R2.Num
HAVING Count(R1.Num * R2.Num)=2

raipon. Terrassa a 10/05/2010.

miércoles, 4 de noviembre de 2009

EMULANDO AUTONUMERICOS


Un tema recurrente : generar una secuencia numérica correlativa, sin discontinuidades y a prueba de errores, para asignar valores a nuevos registros, al estilo de los autonuméricos, pero sin los inconvenientes de estos.
Ciertamente hay variedad de recetas para abordar este problema, pero la piedra de toque de todos ellos es su empleo en sistemas con elevada concurrencia de usuarios y que dicho sistema sea también efectivo dentro de una instrucción sql (y que permita por tanto un insert masivo). Personalmente siempre he usado transacciones (ADO) para asegurar que la lectura del posible nuevo valor, quede bloqueado (para nuevas lecturas) hasta que finalice el proceso de adjudicación del mismo. Esta vez propongo utilizar efectivamente transacciones, pero de forma implícita dentro de una instrucción sql. Esto, en un SGDB como Sql Server es bien simple, pues todas las instrucciones contenidas en un procedimiento almacenado, utilizan la misma transacción, de tal forma que un error de ejecución de una de ellas, trunca todo el procedimiento. Pero en Access, puesto que su lenguaje sql no admite lotes de instrucciones (solo podemos ejecutar/llamar comandos de uno en uno), esto no es posible, a no ser que seamos capaces en una sola sentencia de obtener un nuevo valor, actualizar la tabla y recuperar el valor escrito (hay que tener en cuenta que, una de las características de los autonuméricos es devolver el nuevo valor insertado).
Pongamos un ejemplo : Generar un nuevo número de factura. Para ello disponemos de un formulario para introducir la información de la cabecera del documento : Fecha, Cliente, Tarifa, etc, y un subformulario para los detalles de la misma. Una solución simple y clásica es obtener el mayor valor concedido al campo numerador mediante un Select Max() e incrementar en uno dicho dato ... Pero este sistema, desarrolla toda su lógica en el lado cliente y presenta inconvenientes evidentes en un entorno multiusuario. La solución que propongo consiste en tener dos tablas auxiliares, una en el 'lado datos' y otra en el 'lado cliente'. El objetivo es almacenar el último valor generado absoluto, y el relativo en cada máquina cliente. Ambas tablas deben estar accesibles en la base de datos donde ejecutemos el código : una como tabla local, y la otra (por ejemplo) como tabla vinculada.
Recapitulemos : Tenemos en una base de datos (de servidor) una tabla Facturas, y una tabla Numeradores que contiene el último número de factura generado. Cuando necesitemos generar un nuevo documento solo tenemos que ejecutar esta instrucción sql :

Update Numeradores

Left Join
(Select * From Facturas Where 1=0) As T
On Numeradores.Numerador = T.NFactura
Set Numeradores.Numerador = Numeradores.Numerador + 1,
Facturas.NFactura = Numeradores.Numerador,
Facturas.Fecha = #12/31/2009#,
Facturas.Cliente = 100,
Facturas.Tarifa = 1;

Condiciones previas : Numeradores debe contener un solo registro con valor 1 (valor inicial del autonumérico).
Dado que hemos relacionado de forma asimétrica ambas tablas, aún cuando la segunda tabla (en este caso una Select sobre Facturas) no devuelve resultados, puesto que hemos añadido la condición 1=0, la instrucción update (a efectos prácticos no es un update, sino un insert) afectará a tantos registros como haya en la tabla numeradores, es decir : uno. Al ejecutarse la sentencia, el valor del numerador se incrementa en 1 (no olvidemos que se trata de una instrucción update), en segundo lugar, el nuevo numerador generado se asigna al campo Nfactura de la tabla Facturas, pero dado que no hay ningún registro presente (en la consulta) de la tabla Facturas (hemos filtrado los resultados con esta condición imposible : 1=0), en realidad estamos insertando un nuevo registro en la tabla, y finalmente se actualizan el resto de los campos de Facturas con los valores incluidos en la instrucción sql.

Vamos a hablar de la tabla local de numeradores a la que previamente haciamos referencia. Supongamos que necesitamos agregar registros de uno en uno desde un formulario, y que vamos a introducir desde el propio formulario los datos en cada uno de los campos de la tabla Facturas : Fecha, Cliente, Tarifa, etc. Así pues, necesitamos generar un nuevo registro en dicha tabla, y recuperar automáticamente dicho registro para poder trabajar con él. ¿ En un escenario multiusuario con acceso concurrente a la tabla Facturas, si insertamos una nueva línea, como podemos estar seguros de recuperar el registro que nosotros hemos generado y no otro ? … cabe pensar en la posibilidad que escasos instantes despues de insertar el registro y antes de recuperarlo, otro usuario ha añadido otra línea en la tabla … Si utilizamos Max(NFactura) o leemos el valor de la tabla Numeradores, no nos sirve, puesto que el inoportuno usuario ha modificado también ambos campos …

La panacea a este dilema es incorporar una tabla de Numeradores local (o cliente) además de la tabla Numeradores del servidor que hasta ahora hemos ido usando en nuestra instrucción sql. Se trata de guardar en la tabla local el último Numerador asignado, y puesto que dicha tabla solo se modifica desde la aplicación cliente donde corre el código, tenemos la certeza de que el valor guardado en ella corresponde al último registro asignado por nosotros :

Update (Select Numeradores.Numerador As NumeradorServer,
Numeradores_locales.Numerador As NumeradorLocal
From Numeradores
Inner Join
Numeradores_locales
On Numeradores.Numerador >= Numeradores_locales.Numerador) As N
Left Join
(Select * From Facturas Where 1=0) As T
On N.NumeradorServer = T.NFactura
Set N.NumeradorServer = N.NumeradorServer + 1,
N.NumeradorLocal = N.NumeradorServer,
Facturas.NFactura = N.NumeradorServer,
Facturas.Fecha = #12/31/2009#,
Facturas.Cliente = 100,

Facturas.Tarifa = 1;

Para recuperar el último valor insertado (por nosotros) en la tabla Facturas :

Select Facturas.
*
From Facturas
Inner Join Numeradores_locales
On Facturas.NFactura = Numeradores_locales.Numerador

Pero … ¿ Solo puedo añadir registros de uno en uno ? …

No. Supongamos que tenemos que insertar un conjunto de registros en la tabla Facturas, estos registros los obtiene una consulta llamada Nuevas_Facturas. Dicha consulta debe tener los campos que nos interesa grabar en Facturas, y además, un campo para relacionarlo con Facturas.Nfactura y Numeradores.Numerador. El valor de dicho campo ha de ser tal, que sea imposible que coincida en cualquiera de ambas tablas. Si hemos empezado la numeración de documentos a partir de 1, un valor aceptable para este propósito seria -1.
Y esta es la sql a ejecutar :

Update (Numeradores
Left Join
Nuevas_facturas
On Numeradores.Numerador <> Nuevas_facturas.NFactura)
Left Join
Facturas On Facturas.NFactura = Nuevas_facturas.NFactura
Set Numeradores.Numerador = Numeradores.Numerador + 1,
Facturas.NFactura = Numeradores.Numerador,
Facturas.Fecha = Nuevas_facturas.Fecha,
Facturas.Cliente = Nuevas_facturas.Cliente,
Facturas.Tarifa = Nuevas_facturas.Tarifa;

… con ello añadiremos tantos registros en la tabla Facturas como nos devuelva la consulta Nuevas_Facturas.
En líneas anteriores comentaba la necesidad de que si alguna de las modificaciones en tabla, de alguno de los registros, fallara, se truncará todo el proceso. Podemos comprobar (manipulando la tabla Numeradores para producir duplicados en el campo clave Nfactura), que caso de haber un error, todo el proceso queda abortado, permaneciendo intactas ambas tablas.


raipon. Terrassa a 04/11/2009.

jueves, 9 de julio de 2009

REVISION DE FORMULARIOS DESCONECTADOS


Hola, he revisado y actualizado el código de Formularios desconectados.

domingo, 1 de marzo de 2009

SERIES NUMÉRICAS (SIN TABLA NUMS)


El 23 de Abril de 2008, publiqué este artículo : http://sqlraipon.blogspot.com/2008/04/series-numricas.html, donde se explica como obtener, una consulta con una secuencia de números continua (de 1 a 100, de 1 a 1000, etc) a partir de una tabla ('Nums') con solo 10 registros. Dichas consultas resultan de gran utilidad y las he empleado en muchos de los ejemplos de este blog.


Pues bien, es posible obtener los mismos resultados sin necesidad de la tabla 'Nums' :

-- Para bases de datos con formato Access 2000 :-- En una base de datos nueva, en la que todavia no se haya creado
-- ningún objeto, la tabla del sistema MSysAccessObjects contiene
-- 4 registros, con valores de 0 a 3 en el campo Id.
-- Es de resaltar, que en esta tabla jamás se elimina ningún registro,
-- cuando menos, la serie de valores del campo Id nunca presenta
-- discontinuidades.
-- Por tanto, esta consulta de unión nos asegura una serie numérica
-- de 10 líneas con valores entre 0 y 9, sea cual sea el estado de la
-- base de datos (nueva o con múltiples objetos).

Select Id As Num From MSysAccessObjects Where Id <= 3
Union All
Select Id + 4 As Num From MSysAccessObjects Where Id <=3
Union All
Select Id + 8 As Num From MSysAccessObjects Where Id < 2;

-- Para bases de datos en formato 2003 o 2007. Hay que tener en cuenta que la
-- tabla MSysAccessObjects no existe, por lo tanto emplearemos otra tabla del
-- sistema (MSysObjects). Es de destacar que en una base de datos nueva, están
-- siempre presentes cuatro registros (entre otros) con los siguientes Id :
-- 2, 3, 4 y 5. Por lo tanto, esta consulta de unión nos asegura igualmente 10
-- registros, con valores de 0 a 9 :

Select Id - 2 AS Num From msysobjects Where Id Between 2 And 5
Union All
Select Id + 2 AS Num From msysobjects Where Id Between 2 And 5
Union All
Select Id + 6 AS Num From msysobjects Where Id Between 2 And 3;

A partir de aquí, solo tenemos que guardar una de las dos consultas con el nombre Nums, y ya podemos emplear las consultas del enlace arriba citado sin necesidad de tener en la base de datos la tabla Nums.

raipon. Terrassa a 10/01/2009.

miércoles, 25 de febrero de 2009

FORMULARIOS DESCONECTADOS


Hoy no hablaré directamente de sql, si no de la posibilidad de trabajar en Access, con formularios desconectados de su orígen, al estilo de visual basic.
No es la primera vez que abordo este tema, pero siempre tropezamos con la misma piedra : la complejidad del código necesario. Pue bién, esta vez, y gracias a la clase 'cls_desc', este será todo el código necesario en un formulario (en este caso 'Proveedores' de 'Neptuno.mdb') :

Option Explicit
Option Compare Database
Private cl As New cls_desc

Private Sub cmdGrabar_Click()
On Error Resume Next

If Me.Dirty Then
Me.Refresh
End If

If cl.Dirty = True Then
cl.Actualizar CurrentProject.Connection
MsgBox "Se han realizado : " & cl.RecordsAffected & " actualizaciones."
MsgBox "Se han producido los siguientes errores : " & vbCrLf & cl.Errores
End If

End Sub

Private Sub Form_Open(Cancel As Integer)
Dim rst As New ADODB.Recordset

'Configuramos el recordset
rst.CursorLocation = adUseClient
rst.ActiveConnection = CurrentProject.Connection
rst.LockType = adLockBatchOptimistic
rst.CursorType = adOpenKeyset
rst.Source = "Proveedores"
'Abrimos el recordset
rst.Open
'Desvinculamos el recordset de su orígen
rst.ActiveConnection = Nothing
'Asignamos rst como recordset del formulario
Set Me.Recordset = rst
'Configuramos la clase
Set cl.ActiveRecordset = rst
cl.Campo_Clave = "IdProveedor"
cl.Unique_Table = "Proveedores"

End Sub

... ¿ no es demasiado complicado verdad ?. Y sin embargo podrás editar registros, añadir nuevos, eliminar, etc. y los cambios no se reflejarán en la base de datos a no ser que llamemos al método Actualizar de la clase. Puedes ver el código del módulo de clase 'cls_desc', así como un par de formularios de ejemplo en : Formularios desconectados.zip .
Por si lo anterior te ha parecido insuficiente : Esta clase, permite trabajar con un recordset obtenido de una consulta no actualizable (excepto consultas de unión). El recordset, al estar desconectado permite editar los registros, y al llamar al método Actualizar, los cambios serán volcados en la tabla definida por la propiedad Unique_Table.
Solo debes tener en cuenta que : este tipo de formularios no toleran bién los filtros y ordenar. Por lo demás han sido probados en las siguientes versiones de Access : XP, 2003 y 2007

raipon. Terrassa a 24/02/2009.
 

Contactar