jueves, 7 de mayo de 2015

Stored Procedure SQL-XML como External Object

En esta ocasión les dejo una pequeña guía para ejecutar un query sql-xml en SQL Server, mediante un sencillo stored procedure que en GX usaremos definido como External Object. Para que tengan de referencia, este ejemplo lo hice usando GXXEv2u6, generador C# y MS Sql Server 2008 R2 sp2.

Creación del Stored Procedure

Acá les dejo el script:
CREATE PROCEDURE [dbo].[sp_dynQuery]
 @query nvarchar(2048) ,
 @outNvar nvarchar(max) OUT
AS
BEGIN
    DECLARE @parmDef nvarchar(512);
    DECLARE @queryTmp nvarchar(2048)
    DECLARE @outTmp nvarchar(max);
    SET NOCOUNT ON;
    SET @parmDef = '@out nvarchar(max) OUTPUT';
    SET @queryTmp = 'SELECT @out = CAST((' + @query + ') AS NVARCHAR(max))';
    EXECUTE sp_executesql @queryTmp, @parmDef, @out=@outTmp OUTPUT; 
    SELECT @outNvar = @outTmp;
    SET NOCOUNT OFF
    RETURN(0)
END
Verán que básicamente lo que hace el SP es ejecutar la query que le daremos por parámetros, pasando el resultado a una variable de tipo nvarchar y retornando en el parámetro de salida. Como se trata de un proc que ejecuta queries dinámicas, que eventualmente puede representar un problema de seguridad, debemos asegurar dejarlo asociado a un usuario con restricciones. Específicamente, sólo permiso para ejecutar el SP y de lectura en tablas. Para estos seteos podemos pedir ayuda a nuestro buen amigo Google, al DBA o algún crack del sql que tengamos cerca.

Para que todo resulte más cómodo, les dejo scripts para crear la tabla del ejemplo y cargarle algunos datos.
CREATE TABLE [dbo].[Developer](
 [DeveloperId] [smallint] NOT NULL,
 [DeveloperName] [varchar](64) NOT NULL,
 [DeveloperLastName] [varchar](64) NOT NULL,
 [DeveloperMail] [varchar](128) NOT NULL,
PRIMARY KEY([DeveloperId])
INSERT INTO [Developer]
([DeveloperId],[DeveloperName],[DeveloperLastName],[DeveloperMail])
VALUES
    (1,'ZERR','Angelo','angelo.zerr@gmail.com')
   ,(2,'Leclercq','Pascal','pascal.leclercq@gmail.com')

A continuación la query que pasaremos al SP desde GX:
SELECT [DeveloperName]     as devname 
      ,[DeveloperLastName] as devlastname
      ,[DeveloperMail]     as devmail
FROM [Developer]
WHERE [DeveloperId] < 3
FOR XML PATH ('developer'), ROOT ('devs')
Este ejemplo es bastante sencillo, consulta algunos campos en una tabla, pero se pueden construir cosas bastante más complejas. Aquí hay un buen post sobre el tema.


Creación de External Object

Lo siguiente es la creación del External Object que en nuestra KB representará al stored procedure. Como con cualquier objeto, lo podemos iniciar con Ctrl-N, asignando el nombre que deseamos, en este caso "dynQuery". Desde antes debemos tener preparado un Data Store, que llamaremos "StoredProcs", especial para ejecutar nuestro Stored Procedure, seteado con una cuenta de usuario que mencioné antes, la cual debe tener ciertas restricciones.
En las propiedades seleccionamos el tipo "Stored Procedure" y asignamos el datastore creado previamente.
El único método a definir será "dynQuery" y en External Name ponemos el nombre del Stored Procedure, en este caso "sp_dynQuery" (se puede ver en el script de creación del SP).

En la definición de cada parámetro debemos considerar las propiedades del Stored Procedure en cuanto a Tipo de Acceso, Tipo de Dato y Nombre.



Uso del External Object

Finalmente, en algún objeto de prueba, podemos poner la invocación al EO de la siguiente forma:
&query = 'SELECT [DeveloperName]     as devname ' +
         '      ,[DeveloperLastName] as devlastname ' +
         '      ,[DeveloperMail]     as devmail ' +
         'FROM [Developer] ' +
         'WHERE [DeveloperId] < 3 ' +
         "FOR XML PATH ('developer'), ROOT ('devs')"
csharp try{
    &result = &dynQuery.dynQuery(&query,&return)
csharp }
csharp catch ( Exception e ){
csharp     [!&error!] = e.ToString();
csharp }

La ejecución de esto, si no hay problemas, debiera darnos en la variable &return el siguiente xml:
<devs>
    <developer>
        <devname>ZERR</devname>
        <devlastname>Angelo</devlastname>
        <devmail>angelo.zerr@gmail.com</devmail>
    </developer>           
    <developer>
        <devname>Leclercq</devname>
        <devlastname>Pascal</devlastname>
        <devmail>pascal.leclercq@gmail.com</devmail>
    </developer>           
</devs>           
Nota: La "indentacion" y los saltos de linea los puse para efectos de mejor visualización.

Este tipo de solución se puede usar en innumerables situaciones. Por mencionar algo, están los reportes dinámicos que les mencioné en el post anterior.
También podemos convertir a JSON si tenemos algún User Control que lo requiera, etc., etc..

9 comentarios:

  1. muy buena idea, habia usado de forma muy parecida un Store procedure para evaluar formulas, con excelente resultado !

    Tengo comentado eso en
    http://estebancarle.blogspot.com/2013/04/evaluar-expresiones-aritmeticas.html

    Se podra grabar el resultado en un tabla temporal (que tambien se cree dinamicamente!) ???

    ResponderEliminar
  2. Seguramente pasar el resultado a una tabla temporal es "fácil" y se hace en el mismo Stored Procedure. Salu2!!!

    ResponderEliminar
  3. Hola.

    Soy medio nuevo en genexus.

    Le hago una pregunta. Estoy tratando de hacer esto, en Ev3 U5 y no me genera el codigo C# del External Object, por lo tanto me da
    error CS0234: El tipo o el nombre del espacio de nombres 'SdtdynQuery' no existe en el espacio de nombres 'GeneXus.Programs' (¨falta una referencia de ensamblado?)

    Que me puede estar faltando?

    Gracias, Ariel

    ResponderEliminar
    Respuestas
    1. Hola Ariel
      Ese error es un viejo conocido... Lo más probable es que se solucione haciendo un Build-All. Si no anda, intenta un Rebuild-All.
      Si aún así no se soluciona, me avisas para hacer pruebas en la Ev3 y ver si hay algo más que deba reportarse a la gente de GX.

      salu2!!!

      Eliminar
    2. Hola Miguel.

      Muchas gracias por la respuesta.

      Le comento que lo primero que intenté fue el rebuild all, pero no funcionó. Sigue sin generarlo.

      ¿Tenes el codigo c# que genera asi lo hago a mano mientras?

      Gracias, Ariel

      Eliminar
    3. Hola
      Apenas pueda verifico con la Ev3u5 y te comento el resultado.

      salu2!

      Eliminar
    4. Ariel
      Hice una prueba con la Ev3u5 y trabajó perfecto al primer intento.
      Si quieres te envío la kb para que revises qué te pudo faltar antes.

      salu2!

      Eliminar
  4. Ariel

    Dale, escríbeme a miguel.figueroa.j@gmail.com y te envío el xpz.

    salu2!

    ResponderEliminar