Acceso a campos codificados en la BD

Los campos codificados contienen datos en formato XML que han sido comprimidos y codificados en un formato similar al BASE64+ZIP (utilizado en los ficheros anexos de los mensajes de correo, por ejemplo).

Atención! El contenido de este artículo está en parte obsoleto ya que en versiones a partir de SQL 2016 existe dos nuevas funciones que nos simplifican mucho el trabajar con campos comprimidos: las funciones compress y decompress. Básicamente esta nueva función nos permite el acceso relativamente simple al contenido de objetos PL en xml como puede ser el campo ACTIVITYSCH de la tabla WPASSIGNEDSEQSCH_TB. Para ello nos podemos apoyar en la función plzip2xml que están definidas a modo de ejemplo en la carpeta ..\BDs\scripts junto con otros scripts que permiten por ejemplo sacar el TimePeriod de un dia cualquiera.

Acceso a las actividades asignadas de un plan

select P.id_plan, W.id,
  cast(cast(P.ACTIVITYSCH as varbinary(max)) as varchar(max)) as zipped
from WPASSIGNEDSEQSCH_TB P
join WPCONTRACTWORKER_TB C on C.id = P.id_workerplan and C.activo=1 
join WPWORKER_TB W on W.id = C.ID_WPWORKER
where W.id=35156

La consulta anterior retorna todas las actividades planificadas para el empleado con ID interno 35156. Los registros los devuelve codificados. Si la consulta se lanza desde el SQL Manager, es posible hacer copy&paste sobre un bloc de notas del resultado, y grabar el fichero de texto luego para aplicar el procedimiento de decodificación por javascript, por ejemplo.

Nota importante: como los ciclos se guardan de forma completa es muy posible que haya que recortar y tener en cuenta el posible solape de los ciclos en función de las fechas de validez de cada intervalo de vigencia de los contratos. Para efectuar este recorte es necesario decodificar la información subyacente. Ejemplo extremo: una persona podría tener en una semana natural hasta 7 contratos diferentes (1 por día), en este caso como el ciclo mínimo que se almacena siempre son 7 días (aunque podrían ser de 14, 28,etc.), obtendríamos 7×7=49 registros para esa semana en cuestión todos ellos solapandose entre sí. Pero de cada uno de los ciclos anteriores la única información de asignación válida correspondería al día del contrato en que estaba vigente dicha asignación (ej.: el lunes para el primer contrato, el martes para el segundo y así sucesivamente)

Componente ActiveX decodificador ZipEncoder.dll

Junto con la aplicación instalada se registra un componente Active X accesible desde scripting que permite decodificar los campos anteriores. Habitualmente este componente está instalado en la carpeta ..\Prebuild2009\ZipEncoder.dll Si fuera necesario trasladar este componente a otro ordenador habría que repetir el registro del componente de la siguiente forma: abrir un cmd.exe en la nueva carpeta de instalación y ejecutar el siguiente comando:

regsvr32 ZipEncoder.dll

Nota importante: esta DLL tiene algunas dependencias de otros módulos de la instalación que deben estar accesibles para que funcione el componente (o su registro). Las librerías dependientes se encuentran en la misma carpeta “Prebuild” que debería estar en el path del sistema. Además esta librería y sus dependencias están en modo 32 bits.

Decodificación directamente desde un comando Transact SQL de SQL server

Nota: esta acción sólo es operativa con campos relativamente pequeños ya que existe un bug de conversión en la versión de SQL Server 2005 que impide decodificar campos de más de 4000 caracteres utilizando este procedimiento

SQL User function decodificadora

IF EXISTS (SELECT name FROM sysobjects 
        WHERE name = 'boldwp_unzip' AND type = 'FN')
  DROP FUNCTION boldwp_unzip
GO
CREATE FUNCTION [dbo].[boldwp_unzip](@zipped_xml image)
RETURNS varchar(8000)
AS
BEGIN
-- set string to decode
declare @zippedstr varchar(8000)
set @zippedstr = cast(cast(@zipped_xml as varbinary(8000)) as varchar(8000))
if charindex('MOD_ZIP', @zippedstr) > 0
begin
  set @zippedstr = substring(@zippedstr, 8, len(@zippedstr) - 8)
end
DECLARE @src varchar(255), @desc varchar(255)
declare @object int
declare @hr int
exec @hr = sp_OACreate 'ZipEncoder.ZipEncoderObj', @object OUT
if @hr <> 0
begin
 EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 
   RETURN @desc
end
EXEC @hr = sp_OASetProperty @object, 'UnzipAndDecode', @zippedstr
if @hr <> 0
begin
  EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 
  RETURN @desc
end
-- read decoded string
DECLARE @property varchar(8000)
EXEC @hr = sp_OAGetProperty @object, 'UnzipAndDecode', @property OUT
if @hr <> 0
begin
  EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 
  RETURN @desc
end
/*
EXEC @hr = sp_OADestroy @object
if @hr <> 0
begin
  EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT 
  RETURN @desc
end
*/
return @property
END

Uso de la User function desde una consulta SQL

Por ejemplo, para obtener las actividades asignadas ya decodificadas de antes el comando sería sería el siguiente:

select P.id_plan, W.id,
  cast(cast(P.ACTIVITYSCH as varbinary(max)) as varchar(max)) as zipped,
  dbo.boldwp_unzip(P.ACTIVITYSCH) as decoded
from WPASSIGNEDSEQSCH_TB P
join WPCONTRACTWORKER_TB C on C.id = P.id_workerplan and C.activo=1 
join WPWORKER_TB W on W.id = C.ID_WPWORKER
where W.id=35156

Notar el acceso a la User function: dbo.boldwp_unzip(P.ACTIVITYSCH) as decoded, se le pasa el campo codificado como parámetro y la función retorna el mismo decodificado.

En el caso anterior, como los ciclos suelen ser cortos no suele haber problema. En cambio si tratamos de acceder a la lista de incidencias introducidas en un empleado la longitud del campo ya puede dar más problemas utilizando la técnica del Transact SQL.

IMPORTANTE! Para poder utilizar componentes ActiveX desde una función de SQL Server, al menos en la versión SQL SERVER 2005 e necesario haber ejecutado los siguientes comandos:

/* in order to be able to use COM object from Transact SQL the in SQL 2005 the following commands must be used */
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Además, hay que tener en cuenta que la DLL ZipEncoder.dll es de 32 bits, y Microsoft no admite la creación de un objeto OLE desde un archivo DLL COM de 32 bits en una edición de x 64 de SQL Server 2005.

Uso desde un KPI o una expresion dinámica

En el ejemplo de la función interna PL AsString también se muestra un posible uso.

Decodificacion desde javascript

En el siguiente fragmento de código se supone que el campo que se quiere decodificar ha sido directamente volcado en el fichero de nombre zipencoded.txt

//-------------------------------------------
try
{
 var encoded;
 encoded = LoadFromFile("zipencoded.txt");
//  WScript.echo(encoded);
 if (encoded.substr(0, "MOD_ZIP".length) != "MOD_ZIP")
   throw "Los datos no contienen un campo comprimido de BOLD";
 encoded = encoded.substr("MOD_ZIP".length);
 var zipEncoder;
 zipEncoder = new ActiveXObject("ZipEncoder.ZipEncoderObj");
 zipEncoder.UnzipAndDecode = encoded;
 var decoded;
 decoded = zipEncoder.UnzipAndDecode;
 WScript.Echo(decoded);
}
catch (ex)
{
 WScript.echo(ex);
}
//-------------------------------------------
function FileExists(fileName)
{
 var fso;
 fso = new ActiveXObject("Scripting.FileSystemObject");
 var result;
 return fso.FileExists(fileName);
}
//-------------------------------------------
function LoadFromFile(cmdFile)
{
 if (!FileExists(cmdFile))
 	throw "File " + cmdFile + " does not exist!";
 var ForReading = 1;
 var fso, f;
 fso = new ActiveXObject("Scripting.FileSystemObject");
 f = fso.OpenTextFile(cmdFile, ForReading);
 return f.ReadAll();
}
//-------------------------------------------

El fragmento anterior hace lo siguiente:

1. Lee el archivo de texto con la información codificada
2. Pasa la información a la DLL decodificadora ZipEncoder
3. Vuelca la información en pantalla ya en formato XML

Para ejecutarlo, crear un archivo de texto con el nombre ejemplounzip.js, abrir un cmd.exe y ejecutar el siguiente comando:

cscript ejemplounzip.js

Decodificación desde una consulta preprogramada en el servidor

Otra posibilidad adicional de acceder a los campos comprimidos es a través de Consultas preprogramadas.

Estas consultas normalmente se ejecutan desde dentro de la aplicación desde el menú de Administración–>Consultas Pre-programadas (y también desde el menú de consultas de contratación).

La particularidad de estas consultas es que se pueden añadir campos calculados a las mismas de forma similar a como se definen nuevas columnas en los informes (ver configuración de campos calculados) o en los Contadores del empleado: mediante expresiones que utilizan algunas de las funciones internas de BOLD. Una de estas funciones es DecodeDebug que descomprime estos campos.

A continuación se muestra una configuración de ejemplo de una consulta que permite acceder a los horarios asignados a través de esta técnica para el empleado con ID interno 2136 (en la sección PredefinedQueries de la configuración):

<Query Type="ConsultasPreProgramadas" Name="TestUnzipFields" ShowName="Test de unzip de campos">
   <Metadata>
     <Field Name="zipped" ShowName="Campo comprimido"/>
   </Metadata>
   <ExtAttributes>
     <ExtAttribute Name="fieldexpr" ShowName="Campo calculado" Description="Procesa un campo de SQL pasándolo por una expresión BOLD" Type="string">
      <Expression>
        <![CDATA[
                       DecodeDebug(SubString(zipped, Length("MOD_ZIP") + 1, Length(zipped) - Length("MOD_ZIP")))
        ]]>
      </Expression>
    </ExtAttribute>  
   </ExtAttributes>       
   <Return>1</Return>
   <MSSQL>
     <![CDATA[
       select P.id_plan, W.id,
       cast(cast(P.ACTIVITYSCH as varbinary(max)) as varchar(max)) as zipped,
       from WPASSIGNEDSEQSCH_TB P
       join WPCONTRACTWORKER_TB C on C.id = P.id_workerplan and C.activo=1 
       join WPWORKER_TB W on W.id = C.ID_WPWORKER
       where W.id=2136
     ]]>
   </MSSQL>
   <ORACLE>
   </ORACLE>
 </Query>

También es posible efectuar la llamada directamente a través del webservice GlobalMgr_ExecSQLAsReportXMLMonitored.

El mensaje SOAP para la consulta anterior seria el siguiente (por ejemplo):

 <?xml version="1.0"?>
 <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/">
   <SOAP-ENV:Body SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
     <NS1:GlobalMgr_ExecSQLAsReportXMLMonitored xmlns:NS1="urn:SysObjetosService-ISysObjetosService">
       <a_stSessionId xsi:type="xsd:string">G0uX98oi651</a_stSessionId>
       <a_sQueryNameID xsi:type="xsd:string">TestUnzipFields</a_sQueryNameID>
       <a_sSQL xsi:type="xsd:string">select P.id_plan, W.id,
   cast(cast(P.ACTIVITYSCH as varbinary(max)) as varchar(max)) as zipped,
   dbo.boldwp_unzip(P.ACTIVITYSCH) as decoded
 from WPASSIGNEDSEQSCH_TB P
 join WPCONTRACTWORKER_TB C on C.id = P.id_workerplan and C.activo=1 
 join WPWORKER_TB W on W.id = C.ID_WPWORKER
 where W.id=2136</a_sSQL>
       <a_stParams xsi:type="xsd:string"/>
       <a_bZipped xsi:type="xsd:boolean">true</a_bZipped>
       <a_idObjClasif xsi:type="xsd:int">0</a_idObjClasif>
       <a_bAll xsi:type="xsd:boolean">false</a_bAll>
       <pXSDate xsi:type="xsd:dateTime">2012-06-27T14:30:34.342+02:00</pXSDate>
     </NS1:GlobalMgr_ExecSQLAsReportXMLMonitored>
   </SOAP-ENV:Body>
 </SOAP-ENV:Envelope>

Ejemplo para analizar las versiones de horarios asignados entre fechas

<Query Type="ConsultasPreProgramadas" Name="AnalizarHistoricoPlanificacion" ShowName="Analisis de versiones históricas de plan">
	<ParamList_Filtering>
			<Param Type="int" Name="pIDPLAN" ShowName="ID Escenario" Description="ID del escenario" Default="1"/>
			<Param Type="dateTime" Name="pBeginV" ShowName="Inicio versiones" Description="Indica la fecha de inicio de las versiones a analizar" Default="Today()-1"/>
			<Param Type="dateTime" Name="pEndV" ShowName="Fin versiones" Description="Indica la fecha de fin de las versiones a analizar" Default="Today()+1"/>
			
			<Param Type="dateTime" Name="pDateAnalisis" ShowName="Fecha a analizar" Description="Indica la fecha de la cual queremos información" Default="Today()"/>
			</ParamList_Filtering>
   <Metadata>
     <Field Name="zipped" ShowName="Campo comprimido"/>
   </Metadata>
   <ExtAttributes>
     <ExtAttribute Name="fieldexpr" ShowName="Campo calculado" Description="Procesa un campo de SQL pasándolo por una expresión BOLD" Type="string">
      <Expression>
        <![CDATA[
                       DecodeDebug(SubString(zipped, Length("MOD_ZIP") + 1, Length(zipped) - Length("MOD_ZIP")))
        ]]>
      </Expression>
    </ExtAttribute>  
   </ExtAttributes>       
   <Return>1</Return>
   <MSSQL>
     <![CDATA[
       
select V.id_versionplan, V.date, V.username, V.action_name, cast(cast(H.ACTIVITYSCH as varbinary(max)) as varchar(max)) as zipped, h.ID_WORKERPLAN from WPVERSIONPLAN_TB V 
join WPHISASSSEQSCH_TB H on H.ID_PLAN = V.ID_PLAN and H.ID_VERSIONPLAN = V.ID_VERSIONPLAN

where date > @pBeginV and date < @pEndV and V.ID_PLAN = @pIDPLAN
and @pDateAnalisis  between H.ASSIGNEDDAY and H.ASSIGNEDDAY +7


     ]]>
   </MSSQL>
   <ORACLE>
   </ORACLE>
 </Query>

Ejemplo obtención días festivos de calendarios

En este ejemplo se utilizan las funciones nativas de SQL Server disponibles únicamente a partir de la versión SQL 2014 xs:base64Binary para leer desde base64 y DECOMPRESS para leer campos zipeados:

select id,nombre, convert(datetime, x.value('.', 'int')-2) Festivo from (
	   SELECT id, nombre, cast( CONVERT(varchar (max), DECOMPRESS(CAST(N'' AS xml).value('xs:base64Binary(sql:column("PERIODLIST CLEAN"))', 'varbinary(max)'))) as xml) as periodlist
FROM (SELECT id, nombre, STUFF(cast(substring([PERIODLIST],1, 8000) as varchar(8000)),1,7,'') AS [PERIODLIST CLEAN] FROM [WPCALENDAR_TB]) Z

) Z
 CROSS APPLY periodlist.nodes('//TTimePeriod/@StartTime') x(x)