Certaines philosophies ou normes de développement interdisent aux applications clientes de récupérer directement les jeux de résultats retournés par une procédure stockée. C'est souvent le cas dans les normes de développement autour des outils de reporting comme Business Objects etc... Les données doivent être récupérées depuis des vues ou des tables.
Dans d'autres cas, les données doivent être exportées dans un fichier plat avec le binaire bcp de Sybase mais le formatage complexe des données ou une dénormalisation nécessaire implique obligatoirement l'utilisation d'une procédure stockée.
Sybase Component Integration Services ou CIS avec les tables proxy offre la possibilité de faire apparaître les jeux de résultats d'une procédure sous forme de table pour répondre à ces problématiques.
Un article était déjà paru sur ce sujet en juillet 2002 : Mise en œuvre de CIS (Component Integration Services ». Cet article approfondit ce sujet technique en évoquant à travers un exemple les restrictions, l'avantage du nouveau serveur local loopback, comment récupérer les tables proxy depuis les tables systèmes etc...
L'objectif est de créer une table pt_bo_getnotif (pt pour proxy table) qui correspond au jeu de résultats (result sets) retournés par la procédure sp_bo_getnotif effectuant tous les calculs et toutes les dénormalisations nécessaires. Cette table est mise à disposition pour un univers BO (Business Objects).
La structure de la table pt_bo_getnotif est donnée ci-dessous et elle repose sur la procédure sp_bo_getnotif prenant en paramètre deux variables optionnelles de type date @DATE_FROM et @DATE_TO pour délimiter un intervalle de temps:
| Table pt_bo_getnotif | Procédure sp_bo_getnotif |
|---|---|
THRE_TYPE_CODE varchar(5) not null INDUS_CODE int not null ISS_ID numeric(18,0) not null CTR_ID numeric(18,0) not null USR_ID int not null HOLDING_ID_DATE_1 date null LAST_REPORT_DT_1 numeric(8,0) null LAST_EXPOSURE_1 numeric(18,2) null LAST_NOTIF_ENTITY_ID_1 numeric(18,0) null HOLDING_ID_DATE_2 date null LAST_REPORT_DT_2 numeric(8,0) null LAST_EXPOSURE_2 numeric(18,2) null LAST_NOTIF_ENTITY_ID_2 numeric(18,0) null HOLDING_ID_DATE_3 date null LAST_REPORT_DT_3 numeric(8,0) null LAST_EXPOSURE_3 numeric(18,2) null LAST_NOTIF_ENTITY_ID_3 numeric(18,0) null _DATE_FROM date null _DATE_TO date null |
create proc sp_bo_getnotif
@DATE_FROM date = null,
@DATE_TO date = null
as
begin
.......
select
THRE_TYPE_CODE,
INDUS_CODE,
ISS_ID ,
CTR_ID,
USR_ID,
max( case when rownum=minrownum then HOLDING_ID_DATE
else null end ) HOLDING_ID_DATE_1,
max( case when rownum=minrownum then DT_NOTIFICATION
else null end ) LAST_REPORT_DT_1,
max( case when rownum=minrownum then EXPOSURE
else null end ) LAST_EXPOSURE_1,
max( case when rownum=minrownum then NOTIFYING_ENTITY_ID
else null end ) LAST_NOTIF_ENTITY_ID_1,
max( case when rownum=minrownum +1 then HOLDING_ID_DATE
else null end ) HOLDING_ID_DATE_2,
max( case when rownum=minrownum +1 then DT_NOTIFICATION
else null end ) LAST_REPORT_DT_2,
max( case when rownum=minrownum +1 then EXPOSURE
else null end ) LAST_EXPOSURE_2,
max( case when rownum=minrownum +1 then NOTIFYING_ENTITY_ID
else null end ) LAST_NOTIF_ENTITY_ID_2,
max( case when rownum=minrownum +2 then HOLDING_ID_DATE
else null end ) HOLDING_ID_DATE_3,
max( case when rownum=minrownum +2 then DT_NOTIFICATION
else null end ) LAST_REPORT_DT_3,
max( case when rownum=minrownum +2 then EXPOSURE
else null end ) LAST_EXPOSURE_3,
max( case when rownum=minrownum +2 then NOTIFYING_ENTITY_ID
else null end ) LAST_NOTIF_ENTITY_ID_3
from #tmp2
where rownum< minrownum+3
group by THRE_TYPE_CODE,
INDUS_CODE,
ISS_ID,
CTR_ID
return 0
end
go
|
Les deux colonnes subsidiaires _DATE_FROM et _DATE_TO dans la table pt_bo_getnotif ne sont pas retournées par le jeu de résultats de la procédure sp_bo_getnotif mais sont nécessaires si l'on souhaite donner des valeurs aux paramètres @DATE_FROM et @DATE_TO de la procédudre stockée sp_bo_getnotif.
Si les tables MDA ont déjà été installées sur le serveur ASE, un serveur local appelé loopback est déjà défini dans sysservers. Ce serveur local loopback est réutilisé dans la définition de la table proxy.
sp_helpserver
name network_name class ... ---------- -------------- ------------ ... ... ... ... ... loopback BOR_P1_ASE ASEnterprise ...
Dans le cas contraire, si le serveur loopback n'existe pas, utiliser la commande sp_addserver pour le créer.
sp_addserver loopback,null,@@servername
La table proxy pt_bo_getnotif est créée avec la syntaxe create existing table ... external procedure at "loopback.dbname.owner.procedure_name".
La syntaxe "external procedure at" spécifie le nom de la procédure stockée attachée à la table proxy.
create existing table (...) external procedure at "server_name.database_name.owner.procedure_name"
Ces colonnes additionnelles doivent être de même type que les paramètres d'entrée de la procédure, ne doivent pas être définies à NOT NULL et ont une nomenclature spéciale : elles ont la nomenclature des variables en entrée de la procédure et sont préfixées par un caractère underscore au lieu d'un caractère @.
La procédure stockée sp_bo_getnotif ayant deux paramètres optionnels en entrée @DATE_FROM et @DATE_TO de type date null, la table pt_bo_getnotif est créée avec deux colonnes supplémentaires _DATE_FROM et _DATE_TO de type date null :
create existing table pt_bo_getnotif (
THRE_TYPE_CODE varchar(5) not null ,
INDUS_CODE int not null ,
ISS_ID numeric(18,0) not null ,
CTR_ID numeric(18,0) not null ,
USR_ID int not null ,
HOLDING_ID_DATE_1 date null ,
LAST_REPORT_DT_1 numeric(8,0) null ,
LAST_EXPOSURE_1 numeric(18,2) null ,
LAST_NOTIF_ENTITY_ID_1 numeric(18,0) null ,
HOLDING_ID_DATE_2 date null ,
LAST_REPORT_DT_2 numeric(8,0) null ,
LAST_EXPOSURE_2 numeric(18,2) null ,
LAST_NOTIF_ENTITY_ID_2 numeric(18,0) null ,
HOLDING_ID_DATE_3 date null ,
LAST_REPORT_DT_3 numeric(8,0) null ,
LAST_EXPOSURE_3 numeric(18,2) null ,
LAST_NOTIF_ENTITY_ID_3 numeric(18,0) null ,
_DATE_FROM date null ,
_DATE_TO date null
) on 'default'
external procedure
at 'loopback.BOR.dbo.sp_bo_getnotif'
go
Si la colonne _DATE_FROM ou _DATE_TO est définie à NOT NULL, une erreur est retournée à la création de la table :
create existing table (... _DATE_TO date not null)
on 'default'
external procedure at 'loopback.BOR.dbo.sp_bo_getnotif'
Msg 11270, Level 16, State 2: Server 'BOR_P1_ASE', Line 16: Column '_DATE_TO' does not allow null. Any column defined as a parameter column for RPC tables must allow null. A parameter column is a column whose name begins with an underscore.
La table pt_proxy est interrogée comme une table normale :
select THRE_TYPE_CODE, INDUS_CODE from pt_bo_notif
THRE_TYPE_CODE INDUS_CODE -------------- ---------- ABCDE 1
Les valeurs des clauses where sur les colonnes _DATE_FROM et _DATE_TO lors de l'interrogation de la table proxy sont automatiquement données aux paramètres @DATE_FROM et @DATE_TO lors de l'appel de la procédure stockée sp_bo_getnotif.
select * from pt_bo_getnotif where _DATE_FROM='2009 SEP 01' and _DATE_TO='2009 SEP 30' |
» | exec sp_bo_getnotif @DATE_FROM='2009 SEP 01', @DATE_TO='2009 SEP 30' |
Lorsque les paramètres de la procédure stockée sont obligatoires et ne sont pas spécifiés dans les clauses where correspondantes de la table proxy, le message classique "Procedure %1 expects parameter..." est renvoyé au client :
Procedure sp_bo_getnotif expects parameter @DATE_FROM, which was not supplied.
Outre le fait de pouvoir extraire les résultats de procédures stockées en interrogeant des tables de manière classique etc... les autres intérêts et champs d'application sont multiples :
bcp BOR..pt_bo_getnotif out 1.txt -Usa -SBOR_P1_ASE -t";" -c
select * into #tmp1 from pt_bo_getnotif
Une restriction évidente : les colonnes du jeu de résultats de la procédure stockée doivent être définies dans la table proxy.
Lorsque les conversions automatiques sont possibles et la précision de la colonne dans la table proxy plus courte que celle du jeu de résultats de la procédure stockée, les données sont alors tronquées.
Dans l'exemple ci-dessous la colonne THRE_TYPE_CODE est définie avec un type varchar(2) dans la table proxy pt_bo_getnotif alors que le jeu de résultats de la procédure sp_bo_getnotif retourne des données de type varchar(5) pour cette colonne, la conversion étant possible, les données sont tronquées :
create existing table pt_bo_getnotif (
THRE_TYPE_CODE varchar(5) not null,
...
)
on 'default'
external procedure at
'loopback.BOR.dbo.sp_bo_getnotif'
go
select THRE_TYPE_CODE from pt_bo_getnotif
go
THRE_TYPE_CODE -------------- ABCDE |
create existing table pt_bo_getnotif (
THRE_TYPE_CODE varchar(2) not null,
...
)
on 'default'
external procedure at
'loopback.BOR.dbo.sp_bo_getnotif'
go
select THRE_TYPE_CODE from pt_bo_getnotif
go
THRE_TYPE_CODE -------------- AB |
Il faut s'assurer que les types de données des jeux de résultats de la procédure stockée sont conformes aux types de données de la table proxy, ou au moins qu'ils soient automatiquement convertibles. Une erreur de conversion se produit à l'interrogation de la table proxy lorsque les types de données sont incompatibles.
Dans l'exemple ci-dessous la colonne THRE_TYPE_CODE est définie avec un type int dans la table proxy pt_bo_getnotif alors que le jeu de résultats de la procédure sp_bo_getnotif retourne des données de type varchar(5) pour cette colonne :
create existing table pt_bo_getnotif (
THRE_TYPE_CODE int null ,
...)
on 'default' external procedure at 'loopback.BOR.dbo.sp_bo_getnotif'
go
select * from pt_bo_getnotif
go
Msg 11216, Level 16, State 2: Server 'BOR_P1_ASE', Line 1: Internal Ct-Lib/Cs-Lib error 33816856: 'cs_convert: cslib user api layer: common library error: The conversion/operation was stopped due to a syntax error in the source field.'. Command has been aborted. (0 rows affected)
Lorsque les colonnes de la table proxy ne correspondent pas exactement aux colonnes du jeu de résultats de la procédure stockée, une erreur interne se produit à l'interrogation de la table proxy.
Dans l'exemple ci-dessous une colonne supplémentaire crdate de type datetime est définie dans la table proxy pt_get_usertables par rapport au jeu de résultats de la procédure sp_get_usertables :
create procedure sp_get_usertables as
begin
select name, type from sysobjects where type='U'
end
go
create existing table pt_get_usertables (
... ... ...
name longsysname not null ,
type char(2) not null ,
crdate datetime not null )
on 'default' external procedure at 'loopback.BOR.dbo.sp_get_usertables'
go
select * from pt__get_usertables
go
Internal Ct-Lib/Cs-Lib error 33620242: 'cs_convert: cslib user api layer: external error: An illegal value of -16232 was placed in the maxlength field of the CS_DATAFMT structure.'.
Lorsqu'une procédure stockée retourne plusieurs jeux de résultats, seul le premier jeu de résultats est retourné à la table proxy. Tous les jeux de résultats suivants sont écartés.
La table système sysattributes affiche les définitions des tables proxy (object_type='OD') :
select object_cinfo, char_value from sysattributes where object_type='OD'
object_cinfo char_value ------------------- -------------------------------------------------------- pt_bo_getnotif loopback.BOR.dbo.sp_bo_getnotif
| Version | Date | Commentaires |
|---|---|---|
| 1.0 | 12/2009 | Version initiale |
SQLPAC : Mise en œuvre de CIS (Component Integration
Service)
ASE 15.0.2 BOL
: Understanding Component Integration Services, Proxy tables
ASE 15.0.2 BOL
: create existing table