NUEVA VERSION EDITOR SQL

El título lo dice todo, hoy publico una nueva versión : Editor Sql 1.4

La principal novedad es que se ha mejorado la compatibilidad con las nuevas versiones de Access. Se puede utilizar con Access 2000, 2002, 2003, 2007 y 2010.

Espero que os sea útil.

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

Ramon Poch, raipon. Terrassa a 10/05/2010.

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.

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.