domingo, 29 de noviembre de 2015

Listas y Linq

Linq definitivamente es una herramienta que necesitamos dominar, son muchos los escenarios en los que podemos aprovechar su potencia para simplificar las tareas que otrora nos tomaría varias líneas de código. Aquí unas pequeñas pinceladas con arreglos y listas.

Supongamos que tenemos dos arreglos de enteros y necesitamos saber si ambos se intersecan y cuáles son los números que comparten. Con Linq sería algo así:

static void Main(string[] args)
{
    int[] x = { 1, 2, 3, 4, 5 };
    int[] y = { 2, 4, 6 };
    int[] z = x.Intersect(y).ToArray();
    System.Diagnostics.Debug.WriteLine("Resultado: " + 
    string.Join(",", z.Select(s => s.ToString())));
}
¡Simple!
Otro ejemplo
Supongamos que tenemos la siguiente clase
public class Empleado
{
    public int idEmpleado { get; set; }
    public string nomEmpleado { get; set; }
    public decimal mtoSalario { get; set; }
}
Tenemos un array de enteros y queremos inicializar una lista de empleados únicamente con los id a partir del array, procedemos así;
static void Main(string[] args)
{
    int [] ids =  { 1, 2, 3, 4, 5 };
    List<Empleado> empleados = ids
        .Select(i => new Empleado() { idEmpleado = i })
        .ToList();
    System.Diagnostics.Debug.WriteLine("Cantidad: " + empleados.Count);
}
Una más. Supongamos que adicionalmente a la clase anterior de tenemos otra correspondiente a clientes
public class Cliente
{
    public int idCliente { get; set; }
    public string nomCliente { get; set; }
}
Supongamos que comparten un mismo origen, de manera que un cliente y empleado con la misma id, son la misma persona, teniendo dos listas: una de empleados y otra de clientes, queremos saber cuáles empleados también son clientes, entonces:
static void Main(string[] args)
{
    //Clientes
    List<Cliente> clientes = new List<Cliente> (){
        new Cliente() {idCliente=1, nomCliente= "María"}, 
        new Cliente () {idCliente=2, nomCliente="Jose"},
        new Cliente() {idCliente=3, nomCliente="David"}, 
        new Cliente() {idCliente=4,nomCliente="Ana"} 
    };

    //Empleados
    List<Empleado> empleados = new List<Empleado>(){
        new Empleado() {idEmpleado=2, nomEmpleado="Jose", mtoSalario=3500},
        new Empleado() {idEmpleado=4, nomEmpleado="Ana", mtoSalario=5500},
        new Empleado() {idEmpleado=6, nomEmpleado="Carlos", mtoSalario=3500}
    };

    //ids de clientes
    List<int> idsClientes = clientes.Select(s => s.idCliente).ToList();

    //Empleados que son Clientes
    List<Empleado> empleadosClientes = empleados
        .Where(e => idsClientes.Contains(e.idEmpleado))
        .ToList();

    System.Diagnostics.Debug.WriteLine("Empleados y Clientes: " + string.Join(", ", empleadosClientes.Select(c => c.nomEmpleado.ToString())));
}
Como podemos ver son muchas las posibilidades que nos ofrece Linq, o sus Extension Methods para ayudarnos en nuestra labor.

Leia Mais…

sábado, 24 de octubre de 2015

RPAD y LPAD en TSQL

Son un par de funciones que los que algún día estuvimos trabajando con Oracle estrañamos en SQL Server, Básicamente permiten rellenar a la izquierda o derecha con un caracter dado. Aquí una implementación de ambas en T-SQL

--Permite rellenar con un caracter dado a la izquierda hasta completar
-- la longitud deseada
CREATE FUNCTION [dbo].[fnPADL](
 @strOrigen VARCHAR(50), --String original
 @intlen INT,            --longitud a completar
 @chrCaracter CHAR)      --Caracter con el que se rellena
 RETURNS VARCHAR(50)
 AS 
 BEGIN
    DECLARE @len INT = LEN(@strOrigen)
    RETURN (
        CASE 
            WHEN @len < @intlen THEN REPLICATE(@chrCaracter, @intlen - @len) + @strOrigen 
            ELSE  @strOrigen
        END)
END

--Permite rellenar con un caracter dado a la derecha hasta completar
-- la longitud deseada
CREATE FUNCTION [dbo].[fnPADR](
 @strOrigen VARCHAR(50), --String original
 @intlen INT,            --longitud a completar
 @chrCaracter CHAR)      --Caracter con el que se rellena
 RETURNS VARCHAR(50)
 AS 
 BEGIN
    DECLARE @len INT = LEN(@strOrigen)
    RETURN (
        CASE 
            WHEN @len < @intlen THEN  @strOrigen + REPLICATE(@chrCaracter, @intlen - @len)
            ELSE  @strOrigen
        END)
END
Ambas funciones se aseguran que si la longitud de la palabra es mayor o igual a la longitud a cubrir devuelva la misma palabra.

Su forma de utilización sería
SELECT DBO.fnPADL ('HOLA',6,'X')
--devuelve: XXHOLA

SELECT DBO.fnPADR ('HOLA',6,'X')
-- devuelve: HOLAXX

SELECT DBO.fnPADL ('HOLA',2,'X')
-- devuelve: HOLA

Es muy muy importante a tener en cuenta que aunque estén disponibles las UDF (User-Defined Function) que devuelven escalares (como las que acabamos de definir) no es muy recomendable su uso, principalmente con grandes conjuntos de datos, En otro post intentaré ahondar mas en el por qué, quizás con algunas comparativas pero de momento el consejo sería: siempre que sea posible usar la lógica "in line" o dentro de la misma sentencia SQL, por ejemplo es más eficiente esto
SELECT REPLICATE('X',6 - LEN(miColumna)) + miColumna
FROM miTabla
en lugar de esto:
SELECT  DBO.fnPADL (miColumna,6,'X')
FROM miTabla

Obviamente con una cantidad de datos significativa, Además para usar la primera sentencia tenemos que estar seguros que la longitud de miColumna no es mayor a 6.

En todo caso recordar que si vamos a usar UDF escalares debemos realizar pruebas con buenos volúmenes de datos.

Leia Mais…

viernes, 18 de septiembre de 2015

Error en instalación de SQL Server 2014 en Windows 10


Hace ya un tiempo que migré de Windows 8.1 a Windows 10 pro. En el proceso instalé Visual Studio  2013 sin ningún problema pero al tratar de instalar el SQL server 2014 comenzaron los problemas:




Intenté varias soluciones, todas sin ningún efecto. Luego de revisarlo incluso con técnicos de Microsoft y a pesar de que ellos tampoco lograron resolver el problema, si me pusieron en la dirección correcta.

Aquí dejo los pasos con los que logré solventar la situación:

Primero se intentó instalar Visual C++ 2005, sin embargo este a su vez producía un error similar al que producía la instalación de SQL Server solo que en lugar de Microsoft.VC80.CRT era Microsoft.VC80.ATL por lo que determinamos que el problema era más profundo.

Lo siguiente fue levantar un command promp con privilegios de administrador y ejecutar el siguiente comando:

sfc /scannow

Este arrojó lo siguiente:
There is a system repair pending which requires reboot to complete. Restart Windows and run sfc again.”

Fue aquí cuando relacioné con el problema una circunstancia que no había tomado en cuenta anteriormente: cada vez que apagaba o reiniciaba mi laptop el Windows me indicaba que habían actualizaciones pendientes por descargar y/o instalar, yo simplemente dejaba que terminase siempre sin darle mayor relevancia.

Lo que pasaba en realidad es que en alguna de estas instalaciones el sistema operativo no había podido finalizar el proceso adecuadamente y el archivo pending.xml en la carpeta del sistema, que es el cargado de manejar estos aspectos, se había corrompido.

Para arreglar este problema lo que se debe hacer es ir a la consola de recuperación (recovery console) de Windows y ejecutar cierto comando. Hago un paréntesis aquí para explicar cómo llegar a la consola re recuperación: 

Lo más sencillo es, usando la búsqueda de Windows (tecla de Windows + q), digitar “Recovey Settings”. Seleccionando la opción correspondiente nos llevará a una pantalla como la siguiente

Aquí seleccionamos “Restart Now”. El sistema se iniciará e ingresa a la consola de recuperación


 En esta seleccionamos la opción de "Troubleshoot"


Y en esta la opción de “Advanced options


Finalmente seleccionamos “Command Prompt


Ya estando en la ventana de comandos ejecutamos la siguiente línea:


dism.exe /image:C:\ /cleanup-image /revertpendingactions

Cuando nos indique que el proceso se ejecutó satisfactoriamente reiniciamos el equipo. 
Para asegurarnos que todo esté correcto volvemos a ejecutar, en un command prompt con permisos de administrador, el comando:

sfc /scannow

Esta vez nos debe indicar, una vez finalizado, que no hay ningún problema con la integridad del sistema.

Entonces procedemos a instalar el Visual C++ 2005 x64, en mi caso, ya sin ningún problema. Antes de proceder a la instalación de SQL SERVER vamos a la opcion“Turn Windows Features On of Off” y nos aseguramos que la casilla correspondiente a ."Net Framework 3.5 (Includes .Net 2.0 and 3.0)” este marcada (si no lo esta la marcamos y reiniciamos la máquina).


Finalmente podemos instalar SQL Server sin ningún problema


Leia Mais…

sábado, 25 de octubre de 2014

WHERE condicional

Las consultas a las base de datos son bastante poderosas, pero en algunos casos nos pueden parecer incomodas e incluso frustrantes. Uno de los casos  más comunes es donde necesitamos hacer una consulta donde dado un parámetro si este viene nulo devolver todos los registros pero si trae algún valor hay que devolver los registros filtrados por ese valor, básicamente un filtro condicional.


Luego de varias aproximaciones más o menos eficaces, encontré la que, posiblemente, sea la forma más eficiente de hacer esto sin dividir la consulta en dos (o en 'n' dependiendo de las condiciones) consultas.

Suponiendo que tengo una tabla de productos y que el store procedure recibe un código de categoría para filtrar los resultados, pero si este viene nulo retorna el listado completo de todos los productos, el resultado, usando SQL SERVER 2014,  seria el siguiente:
SELECT P.(...) FROM Producto P
WHERE (1 =  IIF(@pcocCategoria IS NULL, 1, 0) 
OR P.cocCategoria = @pcocCategoria)
En caso de no contar con la instrucción IIF de SQL Server 2014, entonces nos las arreglamos con un CASE:
SELECT P.(...)  FROM Producto P
WHERE (1 = (CASE WHEN @pcocCategoria IS NULL THEN 1 ELSE 0 END) 
OR P.cocCategoria = @pcocCategoria)
En otros caso podría ser dadas una fecha desde y fecha hasta como parámetros necesitemos retornar los registros en ese  rango pero si vienen nulas retornar todos los registros. Entonces utilizaríamos algo similar a esto:
SELECT P.(...)  FROM Producto P
WHERE (1=IIF(@fecDesde IS NULL, 1, 0) OR (P.fecIngreso >= @fecDesde))
AND (1=IIF(@fecHasta IS NULL, 1, 0) OR (P.fecIngreso <= @fecHasta))
Es siempre importante dejar claro que todo este tipo de "workarounds" tiene sus penalizaciones de rendimiento y se debería utilizar únicamente si no existiese alternativas. Siempre es mejor buscar consultas directas, procurando usar los campos índices y en el orden correcto, pero si no queda alternativa aquí queda este tip.


Leia Mais…

jueves, 25 de septiembre de 2014

Insertando, Actualizando y ¿el histórico?

En el post anterior traté de explicar como utilizar la instrucción MERGE para realizar tanto la inserción como la modificación e incluso la eliminación en una sola instrucción/estructura relacional.

Ahora, continuando con el mismo ejemplo, que sucedería si nos piden registrar un histórico de los cambios de existencias…

Pues bien la misma estructura MERGE nos proporciona un mecanismo mediante el cual tomar la salida de la misma, con los registros insertados y modificados, e insertarlos en otra tabla.

Comencemos con crear una estructura mediante la cual guardar nuestro histórico de existencias:

CREATE TABLE ProductoHistorico
(
    conProductoHistorico INT NOT NULL IDENTITY (1,1), 
    conProducto INT NOT NULL 
        CONSTRAINT ProductoHsitorico_Producto_FK FOREIGN KEY
        REFERENCES Producto(conProducto),
    numCantidadAnterior INT NULL,
    numCantidadNueva INT NULL
        CONSTRAINT ProductoHistorico_numCantidadNueva_DF DEFAULT (0),
    fecModificacion DATETIME NOT NULL 
        CONSTRAINT ProductoHistorico_fecModificacion DEFAULT (GETDATE()),
    usrModifico VARCHAR(10) NOT NULL
    CONSTRAINT ProductoHistorico_PK
        PRIMARY KEY CLUSTERED (conProductoHistorico)
        WITH (IGNORE_DUP_KEY = OFF)
);
Es simple, tenemos un consecutivo, una llave foránea con la tabla de productos, la cantidad anterior, la cantidad nueva, la fecha y el usuario que realizó la modificación.
Intentamos modificar el procedimiento almacenado de esta manera:
CREATE PROCEDURE spCargaProductosHistorico(@pusrModifico varchar(10) )
AS
  SET NOCOUNT ON;
  BEGIN TRANSACTION
  BEGIN TRY     

    INSERT INTO ProductoHistorico
    (conProducto,numCantidadAnterior, numCantidadNueva,fecModificacion, usrModifico)
    SELECT conProducto,numCantidadAnterior, numCantidad,fecModificacion, usrModifico 
    FROM
        (MERGE Producto WITH (HOLDLOCK) AS TGT
            USING (SELECT PC.cocProducto, desProducto, numCantidad
                FROM productoCarga PC WITH (HOLDLOCK)) 
                AS SRC (cocProducto, desProducto, numCantidad)
            ON (TGT.cocProducto = SRC.cocProducto)
            WHEN MATCHED THEN 
                UPDATE SET TGT.desProducto = src.desProducto,
                    TGT.numCantidad = (TGT.numCantidad + SRC.numCantidad)
            WHEN NOT MATCHED THEN
                INSERT (cocProducto, desProducto, numCantidad, fecIngreso)
                VALUES (SRC.cocProducto, SRC.desProducto, SRC.numCantidad, GETDATE())
            OUTPUT INSERTED.conProducto, COALESCE(DELETED.numCantidad,0) AS numCantidadAnterior, 
            INSERTED.numCantidad, GETDATE() as fecModificacion, @pusrModifico AS usrModifico)
            AS HIST (conProducto, numCantidadAnterior,numCantidad,fecModificacion, usrModifico);

        TRUNCATE TABLE productoCarga
  END TRY
  BEGIN CATCH
  SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
  END CATCH;
IF @@TRANCOUNT > 0
  COMMIT TRANSACTION;
GO
Expliquemos un poco: la cláusula OUTPUT nos devuelve dos tablas virtuales: INSERTED y DELETED, las mismas que están disponibles en los triggers, la primera corresponde con las filas insertadas (o a las modificadas con los nuevos valores) y la segunda a los valores eliminados o reemplazados. Además, no lo utilizamos en este caso, pero también devuelve un valor especial en la variable $Action que puede contener uno de los siguientes valores INSERT, UPDATE, DELETE, en función de la acción realizada en cada fila.  Ahora bien, utilizamos estas salidas de la cláusula OUTPUT para insertarlas en la tabla ProductoHistorico por medio de un INSERT SELECT FROM.

Pero hay un problema, si tratamos de compilar nuestro procedimiento almacenado nos cuenta que se nos presenta un error.

The target table 'ProductoHistorico' of the INSERT statement cannot be on either side of a (primary key, foreign key) relationship when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement. Found reference constraint 'ProductoHsitorico_Producto_FK'.

El mensaje es fácil de entender: no podemos poner una tabla como receptora de los datos de un MERGE si tiene una Foreign Key (FK, llave Foránea) con la tabla destino del MERGE. El hecho de que sea fácil de entender no significa que nos hayamos percatado del problema antes.

Ahora tenemos varias opciones: una sería eliminar la FK con la tabla de productos, pero perderíamos la integridad referencial, que puede que no ser malo, de hecho puede ser necesario si queremos guardar las eliminaciones físicas de los productos. Otra sería, aparte de la eliminación  de la COSNTRAINT de la FK, eliminar el campo conProducto propiamente de la tabla de histórico y añadir el código de producto sin FK, que al fin y al cabo es el que verdaderamente importaría en una tabla de históricos; esta parece ser la más acertada y conveniente en un escenario real. Sin embargo hay otra opción, que para fines didácticos, es la que voy implementar: guardar el resultado del MERGE en una variable tipo tabla y luego insertarla finalmente en la histórica.

Veamos como quería esta segunda aproximación de nuestro procedimiento almacenado
CREATE PROCEDURE spCargaProductosHistorico(@pusrModifico varchar(10) )
AS
  SET NOCOUNT ON;
  BEGIN TRANSACTION
  BEGIN TRY    

  DECLARE @TablaTemp Table (
    conProducto INT NOT NULL,
    numCantidadAnterior INT NULL,
    numCantidadNueva INT NULL DEFAULT (0),
    fecModificacion DATETIME NOT NULL DEFAULT (GETDATE()),
    usrModifico VARCHAR(10) NOT NULL
  )

    INSERT INTO @TablaTemp
    (conProducto,numCantidadAnterior, numCantidadNueva,fecModificacion, usrModifico)
    SELECT conProducto,numCantidadAnterior, numCantidad,fecModificacion, usrModifico 
    FROM
        (MERGE Producto WITH (HOLDLOCK) AS TGT
            USING (SELECT PC.cocProducto, desProducto, numCantidad
                FROM productoCarga PC WITH (HOLDLOCK)) 
                AS SRC (cocProducto, desProducto, numCantidad)
            ON (TGT.cocProducto = SRC.cocProducto)
            WHEN MATCHED THEN 
                UPDATE SET TGT.desProducto = src.desProducto,
                    TGT.numCantidad = (TGT.numCantidad + SRC.numCantidad)
            WHEN NOT MATCHED THEN
                INSERT (cocProducto, desProducto, numCantidad, fecIngreso)
                VALUES (SRC.cocProducto, SRC.desProducto, SRC.numCantidad, GETDATE() )
            OUTPUT INSERTED.conProducto, COALESCE(DELETED.numCantidad,0) AS numCantidadAnterior, 
            INSERTED.numCantidad, GETDATE() as fecModificacion, @pusrModifico AS usrModifico)
            AS HIST (conProducto, numCantidadAnterior,numCantidad,fecModificacion, usrModifico);

            INSERT INTO ProductoHistorico
            (conProducto,numCantidadAnterior, numCantidadNueva, fecModificacion, usrModifico)
            SELECT conProducto,numCantidadAnterior, numCantidadNueva,fecModificacion, usrModifico
            FROM @TablaTemp

        TRUNCATE TABLE productoCarga
  END TRY
  BEGIN CATCH
  SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

  IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
  END CATCH;
IF @@TRANCOUNT > 0
  COMMIT TRANSACTION;
GO
Simplemente “vaciamos” el resultado del MERGE en la variable tabla y luego está en la tabla ProductoHistorico. Ahora si nos compila.

Y para probarlo
INSERT INTO ProductoCarga
([cocProducto], [desProducto],[numCantidad])
VALUES ('AA001','Produto de Prueba 1',33),
('AA002','Produto de Prueba 2',17),
('AA003','Produto de Prueba 3',21),
('AA004','Produto de Prueba 4',33),
('AA005','Produto de Prueba 5',44),
('AA006','Produto de Prueba 6',20),
('AA007','Produto de Prueba 7',20),
('AA008','Produto de Prueba 8',60),
('AA009','Produto de Prueba 9',80)

SELECT * FROM ProductoCarga
SELECT  * FROM Producto

--EXECUTE spCargaProductos
DECLARE @pusrModifico VARCHAR(10)='FOY'
EXECUTE spCargaProductosHistorico @pusrModifico

SELECT * FROM ProductoCarga
SELECT * FROM Producto
SELECT * FROM ProductoHistorico
Todo funciona de manera adecuada
Igual que en el post anterior señalar que la cláusula MERGE no es la panacea ni la pomada canaria, recalcar que es necesario probar nuestros escenarios para no tener sobresaltos en nuestras puestas en producción, principalmente cuando haya triggers de pormedio.

Clausula MERGE

Problemas de la clausula MERGE

Leia Mais…