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 asíncrona 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 registros 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 la Facturas.Nfactura y Numeradores.Numerador. El valor de dicho campo ha de ser tal, que sea imposible que coincida con 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.

Ramon Poch, raipon. Terrassa a 04/11/2009.

REVISION DE FORMULARIOS DESCONECTADOS

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

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.

Ramon Poch, raipon. Terrassa a 10/01/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

Ramon Poch, raipon. Terrassa a 24/02/2009.

DISTINGUIR MAYÚSCULAS Y MINÚSCULAS

A raiz de una pregunta en los grupos de discusión : microsoft.public.es.access : ¿ como conseguir que un campo de texto sea sensible al uso de mayúsculas y minúsculas ?, se me ocurrió la siguiente solución :

Utilizar un campo tipo binary, ya que en este contexto Access guarda los datos como bytes y no como texto. Esto no lo podemos conseguir desde la vista diseño de una tabla, pues este tipo de campo no está disponible, lo más parecido es un campo 'Objeto Ole', o lo que es lo mismo 'Long Binary', pero para este propósito no nos sirve. Para ello utilizaremos una instrucción ddl (Create Table, o Alter Table), bién desde una consulta (teniendo activada, en 'Opciones', la compatibilidad con Sql Server) o mediante el método Execute de Ado.

Ejemplo :

Create Table MiTabla (Campo1 Binary);

Insert Into MiTabla Values('A');
Insert Into MiTabla Values('a');
Insert Into MiTabla Values('B');
Insert Into MiTabla Values('b');
Insert Into MiTabla Values('C');
Insert Into MiTabla Values('c');
Insert Into MiTabla Values('D');
Insert Into MiTabla Values('d');
Insert Into MiTabla Values('E');
Insert Into MiTabla Values('e');

Select *, Asc(Campo1) As Char_code From MiTabla Order By Campo1;

Drop Table MiTabla;

Al abrir la consulta de selección, vemos como efectivamente el órden establecido corresponde al código del caracter (a pesar de ordenar por 'Campo1'), y no al valor como texto. Por tanto, Access realiza implícitamente la conversión de texto a bytes cuando guardamos datos en este campo, y al reves (de bytes a texto) cuando nos muestra el contenido del mismo.

Así mismo, esta técnica, nos permite tener un campo 'de texto', indexado, de valores únicos, que distinga entre mayúsculas y minúsculas. Ejemplo para crear un campo binary indexado sin duplicados :

Create Table MiTabla (Campo1 Binary Unique);

o si queremos que sea el campo clave :

Create Table MiTabla (Campo1 Binary Primary Key);

Ramon Poch, raipon. Terrassa a 31/01/2009.

FIRST Y LAST

Si utilizamos estas funciones en una instrucción sql que apunte directamente sobre una tabla :

Create Table MiTabla (Campo1 Text (100));

Insert Into MiTabla Values ('Hola mundo');
Insert Into MiTabla Values ('Prueba first y last');
Insert Into MiTabla Values ('Fin de la prueba');

Select First(Campo1) As Primero, Last(Campo1) As Último
From MiTabla;

Drop Table MiTabla;

... vemos que los resultados son absolutamente coherentes : first devuelve el valor para dicho campo del primer registro insertado en la tabla, y viceversa ...
Sin embargo, estos resultados no se ven afectados si establecemos un orden concreto desde la vista 'hoja de datos' de la tabla. Es decir, si desde dicha vista establecemos un orden ascendente (alfabético) para campo1, y guardamos los cambios, First sigue devolviendo 'Hola mundo' en vez de 'Fin de prueba'. ¿ Significa esto que no podemos definir otro criterio de ordenación para establecer qué valor es primero o último ?. En la ayuda de Access podemos leer :

"... Devuelven simplemente el valor de un campo especificado en el primer o el último registro, respectivamente, del conjunto de resultados devueltos por una consulta. Debido a que los registros se devuelven normalmente sin un orden determinado (a no ser que la consulta incluya una cláusula ORDER BY), los registros devueltos por estas funciones son arbitrarios."

... vamos a probar :

Select First(Campo1) As Primero, Last(Campo1) As Último
From
(
Select Campo1
From MiTabla
Order By Campo1
);

... pues no, no es cierto, o no quiere decir lo que parece; nada ha cambiado. Y da igual que guardemos la consulta embebida dentro de la cláusula From como una nueva consulta, que establezcamos también el orden desde la vista 'hoja de datos' de esta nueva consulta, ... nada, ¡ es inútil !. Sencillamente : el orden establecido en una instrucción 'Select', no es persistente para posteriores llamadas a la misma.
Si pensamos en ello detenidamente, este comportamiento es del todo lógico, ya que el orden de los resultados solo debe ser relevante en cuanto a una mejor interpretación de los mismos, pero no para la validez de los datos devueltos. Y en cambio, ordenar los registros para cada 'Select' en una serie de llamadas entre consultas, penalizaría sin duda el rendimiento. Sin embargo hay un escenario en donde los registros devueltos pueden ser distintos según el criterio establecido en 'Order By' : ... cuando usamos la cláusula Top para restringir los resultados a un determinado número de registros. ¿ Solucionará también la cláusula Top nuestro problema ?. Efectivamente, si utilizamos dicha cláusula, el orden permanece para una posterior llamada a la consulta :

Select First(Campo1) As Primero, Last(Campo1) As Último
From
(
Select Top 100 Percent Campo1
From MiTabla
Order By Campo1
);

... y ahora sí, el valor devuelto por First es 'Fin de la prueba' y Last : 'Prueba first y last'. Valores coherentes si ordenamos alfabéticamente 'Campo1'. En conclusión : siempre que utilicemos dichas funciones, si queremos resultados consistentes y fiables debemos basar la sql sobre una consulta previa que incorpore la cláusula 'Top' (a no ser que deseemos recuperar valores directamente de la tabla según el orden de inserción).

Ramon Poch, raipon. Terrassa a 20/12/2008.

DESAFIO SQL (ORDER BY). LA SOLUCIÓN

En primer lugar, un comentario sobre las 'herramientas' a utilizar.

1. Para descomponer la cadena en segmentos : http://sqlraipon.blogspot.com/2008/04/descomponer-lista-csv-en-registros.html Se trata de la adaptación a Access de un ejemplo del mismo Itzik Ben-Gan.
2. Dentro del ejemplo anterior, es necesario generar una secuencia continua de números : http://sqlraipon.blogspot.com/2008/04/series-numricas.html.
3. Las subconsultas, son para Access un 'cuello de botella', colapsando con facilidad la ejecución de una consulta, si hay que procesar una cantidad considerable de registros. Una alternativa válida en determinados casos, consiste en incorporar, la tabla orígen de la subconsulta a la clausula From de la consulta principal, relacionando ambas tablas con un left join, mas la misma expresión utilizada en la cláusula Where de la subconsulta, para filtrar los resultados de la subconsulta a los registros relacionados de la consulta principal. Finalmente, para obtener el valor que nos devolvía la subconsulta, utilizamos una función de agregado.
   Ejemplo sobre la base de datos Neptuno.mdb (tabla Pedidos). Se trata de numerar los pedidos de cada cliente, según la fecha del pedido :
   
   Select
   (Select Count(*) From Pedidos As T
   Where T.IdCliente = Pedidos.IdCliente
   And
   T.FechaPedido < Pedidos.FechaPedido) + 1 As NºPedido_Cliente,
   IdPedido, IdCliente, FechaPedido
   From Pedidos;
   
   Select Pedidos.IdPedido, Pedidos.IdCliente, Pedidos.FechaPedido,
   Count(T.FechaPedido) + 1 As NºPedido_Cliente
   From Pedidos Left Join Pedidos As T
   On T.IdCliente = Pedidos.IdCliente And T.FechaPedido < Pedidos.FechaPedido
   Group By Pedidos.IdPedido, Pedidos.IdCliente, Pedidos.FechaPedido;
   
   Ambas consultas devuelven los mismos resultados, pero la segunda es 10 veces más rápida que la primera.
   Esta técnica de substitución de subconsultas la he empleado, dentro de la solución al reto, en 'Qry2' y la consulta final.
   
4. Un conjunto de valores tal, que el resultado de todas las sumas posibles sea un valor único, irrepetible, y que la suma de los elementos menores de la lista, no pueda jamás alcanzar un elemento superior. Ejemplo : una lista de potencias 2, 4, 8, 16, 32, 64, etc o 3, 9, 27, 81, 243, etc.

Y esta es la solución :

CREATE TABLE t1 (id identity primary key, f_val Memo not null);

insert into t1 (f_val) values ('100');
insert into t1 (f_val) values ('7.4.250');
insert into t1 (f_val) values ('22.40.5.60.4.100.300.478.19710212');
insert into t1 (f_val) values ('22.40.5.60.4.99.300.478.19710212');
insert into t1 (f_val) values ('22.40.5.60.4.99.300.478.9999999');
insert into t1 (f_val) values ('10.30.40.50.20.30.40');
insert into t1 (f_val) values ('7.4.250');
insert into t1 (f_val) values ('-1');
insert into t1 (f_val) values ('-2');
insert into t1 (f_val) values ('-11');
insert into t1 (f_val) values ('-22' );
insert into t1 (f_val) values ('-123');
insert into t1 (f_val) values ('-321');
insert into t1 (f_val) values ('22.40.5.60.4.-100.300.478.19710212');
insert into t1 (f_val) values ('22.40.5.60.4.-99.300.478.19710212');

Create Table Nums (Num Long Primary Key);

Insert Into Nums Values (0);
Insert Into Nums Values (1);
Insert Into Nums Values (2);
Insert Into Nums Values (3);
Insert Into Nums Values (4);
Insert Into Nums Values (5);
Insert Into Nums Values (6);
Insert Into Nums Values (7);
Insert Into Nums Values (8);
Insert Into Nums Values (9);

-- Descomponemos la cadena f_val en tantos registros como segmentos
-- (definidos por el caracter '.' como separador) tenga.
CREATE PROCEDURE Qry1 AS
SELECT id,
Num,
CLng(Mid(f_val,Num+1,Abs(InStr(Num+1,f_val & ".",".")-Num-1))) As Valor,
f_val
FROM
(
Select (TNums_1.Num*10)+ Nums.Num As Num
From Nums, Nums As TNums_1
) AS TNums_2, t1
WHERE Num<=Len(f_val)
And
(Mid(f_val,IIf(Num=0,1,Num),1) = "." Or Num=0);

-- Añadimos un campo 'Nivel' que nos indica el número de segmento
-- dentro de la cadena
CREATE PROCEDURE Qry2 AS
Select Qry1.Id, Qry1.Valor, Qry1.f_val, Count(T.Num) + 1 As Nivel
From Qry1 Left Join Qry1 As T
On Qry1.Id = T.Id And Qry1.Num > T.Num
Group By Qry1.Id, Qry1.Valor, Qry1.f_val, Qry1.Num;

-- El campo Orden se muestra solo a efectos didácticos
Select Id, f_val, Sum(Orden2) As Orden
From
(
-- Calculamos la siguiente potencia :
-- El valor máximo de nivel (obtenido mediante una subconsulta)
-- lo elevamos al Nivel (en negativo).
-- Y dicha expresión la multiplicamos por Orden. La idea es obtener
-- una cifra tal, que la suma de todos los valores posibles para
-- 'niveles' más pequeños de la misma cadena, no la pueda superar.
Select Id, f_val, Orden * ((Select Max(Nivel) From Qry2) ^ (Nivel * -1)) As Orden2
From
(
-- Obtenemos para cada nivel, el número de registros más pequeños
-- según valor.
Select Qry2.Id, Qry2.Nivel, Qry2.f_val, Count(T.Id) As Orden
From Qry2 Left Join Qry2 As T
On Qry2.Nivel = T.Nivel And Qry2.Valor > T.Valor
Group By Qry2.Id, Qry2.Nivel, Qry2.f_val
)
)
Group By Id, f_val
Order By Sum(Orden2);

-- Eliminamos los objetos :
Drop Procedure Qry2;
Drop Procedure Qry1;
Drop Table t1;
Drop Table Nums;

Ramon Poch, raipon. Terrassa a 03/01/2009.