Après windows pour les nuls, voici PB pour les bons (ou presque).

Le forum (ô combien francophone) des utilisateurs de Powerbuilder.

Recherche rapide

Annonce

Certaines rubriques, dont des cours, sont uniquement visibles par les membres du forum ^^.
Dans la rubrique Liens & Références, vous avez accès à un sommaire de téléchargement, profitez-en !
Il existe maintenant un nouveau TOPIC "Votre CV en Ligne" accessible uniquement par demande.
  • Index
  •  » Base de données
  •  » [RESOLU] Erreur lors de l'execution d'une procédure stockée dans une datawindow

#1 21-07-2017 17:10:32

ydl  
Membre Geek
Date d'inscription: 23-10-2007
Messages: 60
Pépites: 10,321
Banque: 0

[RESOLU] Erreur lors de l'execution d'une procédure stockée dans une datawindow

Environnement
PB11.5.1 B4608/PB2017 B1666
MSSQL 2008R2 SP3
Windows 10
J'ai une procédure stockée, s'exécutant sans problème dans le SQL manager.
Dans l'ide un retrieve de la datawindow (une grid) retourne les valeurs sans problèmes.
En exécution, j'ai le message d'erreur suivant

erreur SQL = (102)
Object : dd_tcharge_test
Select Error: SQLSTATE = 42000
Microsoft SQL Server Native Client 10.0
Syntaxe incorrecte vers '!'.
----------------------------------------
J'ai vérifié les droits sur les tables et la procédure stockée et il n'y a rien qui cloche.
J'ai l'erreur avec la version PB11.5 et la version 2017.
Là je tourne en rond.
Merci
Yann

Procédure stockée

Code: sql

CREATE PROCEDURE [dbo].[proc_chargelib] 
  -- Add the parameters for the stored procedure here
  (@pol int,@pod int,@tier int,@agence int,@catid int,@ilang integer)
AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
  -- =============================================
  -- Declare and using a READ_ONLY cursor
  -- =============================================
  Create table #libtemp
   (tchargeId varchar(12)
   ,name varchar(50)
   ,intname varchar(50)
   ,intname2 varchar(50)
   ,intname3 varchar(50)
   ,intname4 varchar(50)
   ,computebase decimal(18,10)
   ,fixedbase bit
   ,curname varchar(50)
   ,unname varchar(100)
   ,forfait int)
  
  DECLARE cur_charge CURSOR
  READ_ONLY
  FOR 
  SELECT typecharge.tcharge_id,
         typecharge.name,   
         typecharge.intname,   
         typecharge.intname2,   
         typecharge.intname3,   
         typecharge.intname4,    
         typecharge.computebase,   
         typecharge.fixedbase,   
         currency.name,   
         unit.name,   
         (select count(*) from typecharge_repart where tcharge_id =typecharge.tcharge_id) as forfait
    FROM typecharge,   
         currency,   
         unit  
   WHERE ( currency.currency_id = typecharge.currency_id ) and  
         ( unit.unit_id = typecharge.unit_id ) and  
         ( typecharge.inuse = 1 ) AND  
         (isnull(typecharge.cattchg_id,@catid) = @catid OR  
         isnull(@catid,0) = 0)
         --AND ISNULL(@chg,tcharge_id)=tcharge_id

  DECLARE @tchargeId varchar(12),@name varchar(50),@intname varchar(50),@intname2 varchar(50),@intname3 varchar(50),@intname4 varchar(50),
  @defname varchar(50),@defintname varchar(50),@defintname2 varchar(50),@defintname3 varchar(50),@defintname4 varchar(50)
  ,@compute decimal(18,10),@fixe bit,@curname varchar(50),@unname varchar(100),@forfait int
  OPEN cur_charge

  FETCH NEXT FROM cur_charge INTO @tchargeID,@name,@intname,@intname2,@intname3,@intname4,@compute,@fixe,@curname,@unname,@forfait
  WHILE (@@fetch_status <> -1)
  BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
    set @defname=null
    set @defintname=null
    set @defintname2=null
    set @defintname3=null
    set @defintname4=null
    
    SELECT top (1) @defname=lab.name,
    @defintname=lab.intname,
    @defintname2=lab.intname2,
    @defintname3=lab.intname3,
    @defintname4=lab.intname4
    FROM tcharge_label lab
    WHERE lab.agency_id=@agence
    AND lab.tcharge_id=@tchargeId
    and (
         ( --correspondance sur les trois
          isnull(@tier,0) =lab.tier_id
          and ISNULL(@pol,0)=lab.pol
          and ISNULL(@pod,0)=lab.pod
          )
       or  (  --correspondance sur tier/pol
          isnull(@tier,0) =lab.tier_id
          and ISNULL(@pol,0)=lab.pol
          and lab.pod=0
         )
      or  (  --correspondance sur tier/pod
          isnull(@tier,0) =lab.tier_id
          and ISNULL(@pod,0)=lab.pod
          and lab.pol=0
        )
      or  (  --correspondance sur pol,pod
          ISNULL(@pol,0)=lab.pol
          and ISNULL(@pod,0)=lab.pod
          and lab.tier_id=0
        )
      or  (  --correspondance sur le pol
          ISNULL(@pol,0)=lab.pol  
          and lab.tier_id=0
          and lab.pod=0
        )
      or  (  --correspondance sur le pod
          ISNULL(@pod,0)=lab.pod
          and lab.tier_id=0
          and lab.pol=0
        )
      or  (  --correspondance sur le tiers
          lab.pod=0
          and isnull(@tier,0)=lab.tier_id
          and lab.pol=0
        )
       )
     order by lab.tier_id desc,lab.pod desc,lab.pol desc
    
    insert into #libtemp
    (tchargeId,name,intname,intname2,intname3,intname4,computebase,fixedbase,curname,unname,forfait)
    values
    (@tchargeId,ISNULL(@defname,@name),ISNULL(@defintname,@intname),ISNULL(@defintname2,@intname2),ISNULL(@defintname3,@intname3),ISNULL(@defintname4,@intname4),@compute,@fixe,@curname,@unname,@forfait)        

    END
    FETCH NEXT FROM cur_charge INTO @tchargeID,@name,@intname,@intname2,@intname3,@intname4,@compute,@fixe,@curname,@unname,@forfait
  END

  CLOSE cur_charge
  DEALLOCATE cur_charge

  select  IsNull(  ( CASE @ilang WHEN 1 THEN  name WHEN 2 THEN intname WHEN 3 THEN intname2 WHEN 4 THEN intname3 WHEN 5 THEN intname4  ELSE name END ) ,name)  as chgname,
   tchargeId,name,intname,intname2,intname3,intname4,computebase,fixedbase,curname,unname,forfait from #libtemp
  
END

Code Datawindow

Code: pb

release 11.5;
datawindow(units=0 timer_interval=0 color=1073741824 brushmode=0 transparency=0 gradient.angle=0 gradient.color=8421504 gradient.focus=0 gradient.repetition.count=0 gradient.repetition.length=100 gradient.repetition.mode=0 gradient.scale=100 gradient.spread=100 gradient.transparency=0 picture.blur=0 picture.clip.bottom=0 picture.clip.left=0 picture.clip.right=0 picture.clip.top=0 picture.mode=0 picture.scale.x=100 picture.scale.y=100 picture.transparency=0 processing=1 HTMLDW=no print.printername="" print.documentname="" print.orientation = 0 print.margin.left = 110 print.margin.right = 110 print.margin.top = 96 print.margin.bottom = 96 print.paper.source = 0 print.paper.size = 0 print.canusedefaultprinter=yes print.prompt=no print.buttons=no print.preview.buttons=no print.cliptext=no print.overrideprintjob=no print.collate=yes print.background=no print.preview.background=no print.preview.outline=yes hidegrayline=no showbackcoloronxp=no picture.file="" grid.lines=0 )
header(height=72 color="536870912" transparency="0" gradient.color="8421504" gradient.transparency="0" gradient.angle="0" brushmode="0" gradient.repetition.mode="0" gradient.repetition.count="0" gradient.repetition.length="100" gradient.focus="0" gradient.scale="100" gradient.spread="100" )
summary(height=0 color="536870912" transparency="0" gradient.color="8421504" gradient.transparency="0" gradient.angle="0" brushmode="0" gradient.repetition.mode="0" gradient.repetition.count="0" gradient.repetition.length="100" gradient.focus="0" gradient.scale="100" gradient.spread="100" )
footer(height=0 color="536870912" transparency="0" gradient.color="8421504" gradient.transparency="0" gradient.angle="0" brushmode="0" gradient.repetition.mode="0" gradient.repetition.count="0" gradient.repetition.length="100" gradient.focus="0" gradient.scale="100" gradient.spread="100" )
detail(height=72 color="536870912" transparency="0" gradient.color="8421504" gradient.transparency="0" gradient.angle="0" brushmode="0" gradient.repetition.mode="0" gradient.repetition.count="0" gradient.repetition.length="100" gradient.focus="0" gradient.scale="100" gradient.spread="100" )
table(column=(type=char(50) updatewhereclause=yes name=chgname dbname="chgname" )
 column=(type=char(12) updatewhereclause=yes name=tchargeid dbname="tchargeId" )
 column=(type=char(50) updatewhereclause=yes name=name dbname="name" )
 column=(type=char(50) updatewhereclause=yes name=intname dbname="intname" )
 column=(type=char(50) updatewhereclause=yes name=intname2 dbname="intname2" )
 column=(type=char(50) updatewhereclause=yes name=intname3 dbname="intname3" )
 column=(type=char(50) updatewhereclause=yes name=intname4 dbname="intname4" )
 column=(type=decimal(10) updatewhereclause=yes name=computebase dbname="computebase" )
 column=(type=number updatewhereclause=yes name=fixedbase dbname="fixedbase" )
 column=(type=char(50) updatewhereclause=yes name=curname dbname="curname" )
 column=(type=char(100) updatewhereclause=yes name=unname dbname="unname" )
 column=(type=long updatewhereclause=yes name=forfait dbname="forfait" )
 procedure="1 execute dbo.proc_chargelib;1 @pol = :pol, @pod = :pod, @tier = :tier, @agence = :agence, @chg = :chg, @catid = :catid, @ilang = :ilang" arguments=(("pol", number),("pod", number),("tier", number),("agence", number),("chg", string)("catid", number),("ilang", number),) )
text(band=header alignment="2" text="#" border="6" color="33554432" x="9" y="4" height="60" width="411" html.valueishtml="0"  name=tchargeid_t visible="1"  font.face="Tahoma" font.height="-8" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="2" background.color="67108864" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )
text(band=header alignment="2" text="Type de charge" border="6" color="33554432" x="430" y="4" height="60" width="1381" html.valueishtml="0"  name=chgname_t visible="1"  font.face="Tahoma" font.height="-8" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="2" background.color="67108864" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )
text(band=header alignment="2" text="Base" border="6" color="33554432" x="1824" y="4" height="60" width="279" html.valueishtml="0"  name=computebase_t visible="1"  font.face="Tahoma" font.height="-8" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="2" background.color="67108864" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )
text(band=header alignment="2" text="Currency" border="6" color="33554432" x="2117" y="4" height="60" width="389" html.valueishtml="0"  name=curname_t visible="1"  font.face="Tahoma" font.height="-8" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="2" background.color="67108864" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )
text(band=header alignment="2" text="Unit" border="6" color="33554432" x="2514" y="4" height="60" width="507" html.valueishtml="0"  name=unname_t visible="1"  font.face="Tahoma" font.height="-8" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="2" background.color="67108864" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )
column(band=detail id=2 alignment="0" tabsequence=32766 border="6" color="33554432" x="9" y="8" height="60" width="411" format="[general]" html.valueishtml="0"  name=tchargeid visible="1" edit.limit=12 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes  font.face="Tahoma" font.height="-8" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="0" background.color="67108864~tif( forfait >0,20866303,67108864)" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )
column(band=detail id=1 alignment="0" tabsequence=32766 border="0" color="33554432" x="430" y="8" height="60" width="1381" format="[general]" html.valueishtml="0"  name=chgname visible="1" edit.limit=50 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes  font.face="Tahoma" font.height="-8" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )
column(band=detail id=8 alignment="1" tabsequence=32766 border="0" color="33554432" x="1824" y="8" height="60" width="279" format="[general]" html.valueishtml="0"  name=computebase visible="1" edit.limit=0 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes  font.face="Tahoma" font.height="-8" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )
column(band=detail id=10 alignment="0" tabsequence=32766 border="0" color="33554432" x="2117" y="8" height="60" width="389" format="[general]" html.valueishtml="0"  name=curname visible="1" edit.limit=50 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes  font.face="Tahoma" font.height="-8" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )
column(band=detail id=11 alignment="0" tabsequence=32766 border="0" color="33554432" x="2514" y="8" height="60" width="507" format="[general]" html.valueishtml="0"  name=unname visible="1" edit.limit=100 edit.case=any edit.focusrectangle=no edit.autoselect=yes edit.autohscroll=yes  font.face="Tahoma" font.height="-8" font.weight="400"  font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" background.transparency="0" background.gradient.color="8421504" background.gradient.transparency="0" background.gradient.angle="0" background.brushmode="0" background.gradient.repetition.mode="0" background.gradient.repetition.count="0" background.gradient.repetition.length="100" background.gradient.focus="0" background.gradient.scale="100" background.gradient.spread="100" tooltip.backcolor="134217752" tooltip.delay.initial="0" tooltip.delay.visible="32000" tooltip.enabled="0" tooltip.hasclosebutton="0" tooltip.icon="0" tooltip.isbubble="0" tooltip.maxwidth="0" tooltip.textcolor="134217751" tooltip.transparency="0" transparency="0" )
htmltable(border="1" )
htmlgen(clientevents="1" clientvalidation="1" clientcomputedfields="1" clientformatting="0" clientscriptable="0" generatejavascript="1" encodeselflinkargs="1" netscapelayers="0" pagingmethod=0 generatedddwframes="1" )
xhtmlgen() cssgen(sessionspecific="0" )
xmlgen(inline="0" )
xsltgen()
jsgen()
export.xml(headgroups="1" includewhitespace="0" metadatatype=0 savemetadata=0 )
import.xml()
export.pdf(method=0 distill.custompostscript="0" xslfop.print="0" )
export.xhtml()

Dernière modification par ydl (24-07-2017 11:21:44)

Hors ligne

 

#2 24-07-2017 11:24:19

ydl  
Membre Geek
Date d'inscription: 23-10-2007
Messages: 60
Pépites: 10,321
Banque: 0

Re: [RESOLU] Erreur lors de l'execution d'une procédure stockée dans une datawindow

la fatigue de la semaine sans doute, dans la fenêtre hébergeant la datawindow, une fonction modifiait le code SQL avec un ajout sur le Where.
Fonctionne effectivement très mal avec l'appel d'une procédure stockée

Hors ligne

 
  • Index
  •  » Base de données
  •  » [RESOLU] Erreur lors de l'execution d'une procédure stockée dans une datawindow

Pied de page des forums

Propulsé par FluxBB 1.2.22