Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
How to fix buffer overrun in psqlodbc driver
Message
Information générale
Forum:
Visual FoxPro
Catégorie:
Client/serveur
Versions des environnements
Visual FoxPro:
VFP 9 SP2
OS:
Windows 7
Network:
Windows 2008 Server
Database:
Visual FoxPro
Application:
Desktop
Divers
Thread ID:
01624769
Message ID:
01624800
Vues:
59
Code which causes crash is below. Can anybody to reproduce crash using Postgres official odbc driver.
SET TEXTMERGE ON
SET TEXTMERGE TO (sys(2015))
TEXT TEXTMERGE NOSHOW

CREATE or replace FUNCTION public.f_infA()
  RETURNS TABLE (
kuupaev date,
nimi text, 
tasudok text,

regnr text, 
kmprotsent text, 
neto numeric, 
myyk numeric, 
erisus01 bool, 
erisus02 bool, 
erisus03 bool
) AS $f_InfA$

WITH 
myyk as (

SELECT 
  dok.kuupaev, 
  dok.tasudok, 
  klient.regnr,
  CASE when klient.regnr is null OR klient.regnr='' then klient.nimi else '' end::char(80) as nimi,
  20 as kmprotsent, 
  k01+k011+k01erik as myyk20, 
  0 as myyk9, 

  MAX(klient.nimi)::char(80) as maxnimi,
  bool_or(k01erik<>0) as erisus01,
  bool_or(d09<>0) as erisus02, 

  SUM(  ROUND( rid.hind * case when rid.kogus<>0 then rid.kogus else 1 end*
    CASE when rid.kogpak<>0 then rid.kogpak else 1 end 
    /case when dok.doktyyp<>'Y' then 1 else 1+myygikoo.kmprotsent/100 end *  
    CASE when dok.raha=prpalk.pohiraha then 1 
       when dok.exchrate<>0 then dok.Exchrate else kurss.kurss end,2) 
     ) as neto 

FROM prpalk, tehing 
JOIN dok ON tehing.dokumnr=dok.dokumnr
JOIN rid ON dok.dokumnr=rid.dokumnr
JOIN klient ON dok.klient=klient.kood 
LEFT JOIN myygikoo ON rid.myygikood=myygikoo.myygikood or ( rid.myygikood is null and myygikoo.myygikood ='' )
LEFT JOIN kurss ON dok.raha=kurss.raha AND dok.kuupaev=kurss.kuupaev 
LEFT JOIN toode ON rid.toode=toode.toode 
WHERE (rid.toode is null or toode.grupp<>'S' OR rid.toode='LE' or (toode.klass like '%T%' AND toode.klass not like '%E%'
     AND toode.klass not like '%M%')) and
   tehing.alusdok='LG' AND ( (k01+k011+k01erik)<>0  )
AND klient.nimi not ilike '%AUDIITORBÜROO%'
AND klient.nimi not ilike '%ADVOKAADIBÜROO%'
AND klient.nimi not ilike 'Notar %'
AND klient.nimi not ilike '% Notar'
AND rid.hind <> 0     -- Et ei tekiks arvetel taara 0 hinnaga väljastamisel erisuse koodi 03
AND klient.nimi not ilike 'Eraisik'
AND klient.nimi not ilike 'Jaeostja'
and not klient.kmdkeeld

GROUP BY 1,2,3,4,5,6,7
union all
SELECT 
  dok.kuupaev, 
  dok.tasudok, 
  klient.regnr,
  CASE when klient.regnr is null OR klient.regnr='' then klient.nimi else '' end::char(80) as nimi,
  9, 
  0 as myyk20, 
  k02+k021 as myyk9, 

  MAX(klient.nimi)::char(80) as maxnimi,
  bool_or(k01erik<>0) as erisus01,
  bool_or(d09<>0) as erisus02, 

  SUM(  ROUND( rid.hind * case when rid.kogus<>0 then rid.kogus else 1 end*
    CASE when rid.kogpak<>0 then rid.kogpak else 1 end 
    /case when dok.doktyyp<>'Y' then 1 else 1+myygikoo.kmprotsent/100 end *  
    CASE when dok.raha=prpalk.pohiraha then 1 
       when dok.exchrate<>0 then dok.Exchrate else kurss.kurss end,2) 
     ) as neto 

FROM prpalk, tehing 
JOIN dok ON tehing.dokumnr=dok.dokumnr
JOIN rid ON dok.dokumnr=rid.dokumnr
JOIN klient ON dok.klient=klient.kood 
LEFT JOIN myygikoo ON rid.myygikood=myygikoo.myygikood or ( rid.myygikood is null and myygikoo.myygikood ='' )
LEFT JOIN kurss ON dok.raha=kurss.raha AND dok.kuupaev=kurss.kuupaev 
LEFT JOIN toode ON rid.toode=toode.toode 
WHERE (rid.toode is null or toode.grupp<>'S' OR rid.toode='LE' or (toode.klass like '%T%' AND toode.klass not like '%E%'
     AND toode.klass not like '%M%')) and
   tehing.alusdok='LG' AND (k02+k021)<>0
AND klient.nimi not ilike '%AUDIITORBÜROO%'
AND klient.nimi not ilike '%ADVOKAADIBÜROO%'
AND klient.nimi not ilike 'Notar %'
AND klient.nimi not ilike '% Notar'
AND rid.hind <> 0     -- Et ei tekiks arvetel taara 0 hinnaga väljastamisel erisuse koodi 03
AND klient.nimi not ilike 'Eraisik'
AND klient.nimi not ilike 'Jaeostja'
and not klient.kmdkeeld
GROUP BY 1,2,3,4,5,6,7
union all
SELECT 
  omrid.adkuupaev, 
  omrid.ettemarve, 
  klient.regnr, 
  CASE when klient.regnr is null OR klient.regnr='' then klient.nimi else '' end::char(80) as nimi,

  CASE when  sum(k01+k011+k01erik)<>0 then 20 else 9 end, 

  CASE when  sum(k01+k01erik+k011)<>0 then 

  sum( ROUND( 
   case when omrid.hinnalis='LM' then -omrid.tasusumma else omrid.tasusumma end *
     case when omrid.kogus<>0 then omrid.kogus else 1 end * 
     case when omrid.raha=prpalk.pohiraha then 1 else
      case when omrid.exchrate<>0 then omrid.Exchrate else kurss.kurss end END,2))

  else 0 end as myyk20, 


  CASE when  sum(k02+k021)<>0 then 

sum(  ROUND( 
   case when omrid.hinnalis='LM' then -omrid.tasusumma else omrid.tasusumma end *
     case when omrid.kogus<>0 then omrid.kogus else 1 end * 
     case when omrid.raha=prpalk.pohiraha then 1 else
      case when omrid.exchrate<>0 then omrid.Exchrate else kurss.kurss end END,2))

  else 0 end as myyk9, 

  MAX(klient.nimi)::char(80) as maxnimi,
  bool_or(k01erik<>0) as erisus01,
  bool_or(d09<>0) as erisus02, 

  SUM( ROUND( 
   case when omrid.hinnalis='LM' then -omrid.tasusumma else omrid.tasusumma end *
     case when omrid.kogus<>0 then omrid.kogus else 1 end * 
     case when omrid.raha=prpalk.pohiraha then 1 else
      case when omrid.exchrate<>0 then omrid.Exchrate else kurss.kurss end END,2)) as neto 
FROM prpalk, tehing 
join omdok on tehing.dokumnr=omdok.dokumnr
JOIN omrid ON omrid.dokumnr=tehing.dokumnr -- AND tehing.doknr=omrid.ettemarve 
JOIN klient ON omdok.klient=klient.kood 
LEFT JOIN kurss ON omrid.raha=kurss.raha AND omrid.adkuupaev=kurss.kuupaev 

WHERE tehing.alusdok in ('DT', 'DI')  AND  ( (k01+k01erik+ k011)>0 )
    and omrid.ettemarve is not null and omrid.ettemarve is distinct from ''
    AND klient.nimi not ilike '%AUDIITORBÜROO%'
    AND klient.nimi not ilike '%ADVOKAADIBÜROO%'
    AND klient.nimi not ilike 'Notar %'
    AND klient.nimi not ilike '% Notar'
    and not klient.kmdkeeld
GROUP BY 1,2,3,4

),

myyk1000 as (
SELECT 
  regnr, 
  nimi
FROM myyk 
WHERE neto>0 
GROUP BY 1,2 
HAVING SUM(neto)>1000
)

SELECT 
  myyk.kuupaev,
  myyk.maxnimi as nimi, 
  myyk.tasudok,

  myyk.regnr, 
  '20' as kmprotsent, 
  round( SUM(myyk.neto),2)  as neto, 
  round( SUM(myyk.myyk20),2) as myyk, 
  bool_or(erisus01) as erisus01, 
  bool_or(erisus02) as erisus02, 
  bool_or(myyk.kmprotsent=0) as erisus03 

FROM myyk1000 
JOIN myyk ON myyk1000.regnr=myyk.regnr AND myyk1000.nimi=myyk.nimi 
GROUP BY 1,2,3,4 
HAVING SUM(myyk.myyk20)<>0 

UNION ALL 

SELECT 
  myyk.kuupaev,
  myyk.maxnimi as nimi, 
  myyk.tasudok,
  myyk.regnr, 
  '9', 
  round( SUM(myyk.neto),2) , 
  round( SUM(myyk.myyk9),2), 
  false, 
  bool_or(erisus02) as erisus02, 
  bool_or(myyk.kmprotsent=0) as erisus03 

FROM myyk1000 
JOIN myyk ON myyk1000.regnr=myyk.regnr AND myyk1000.nimi=myyk.nimi 
GROUP BY 1,2,3,4 
HAVING SUM(myyk.myyk9)<>0  

UNION ALL 

SELECT 
  myyk.kuupaev,
  myyk.maxnimi as nimi, 
  myyk.tasudok,

  myyk.regnr, 
  'erisus20', 
  round( SUM(myyk.neto),2) , 
  round( SUM(myyk.myyk9),2), 
  false, 
  bool_or(erisus02) as erisus02, 
  false as erisus03 

FROM myyk1000 
JOIN myyk ON myyk1000.regnr=myyk.regnr AND myyk1000.nimi=myyk.nimi 
GROUP BY 1,2,3,4 
HAVING bool_or(erisus02)
ORDER BY nimi, kuupaev, tasudok

$f_infa$ LANGUAGE sql STABLE;

RESET ROLE;

ENDTEXT

cSqlFail = SET('textmerge',2)
SET TEXTMERGE TO 
SQLEXEC(m.g_server.nConnhandle, FILETOSTR(m.cSqlFail) )
connection string used:

cConnString = "DRIVER={PostgreSQL Unicode};"+ ;
     "DATABASE=test";"+ ;
     "SERVER=localhost;" + ;
     "PORT=5432;" + ;
     "UID=user;" + ;
     "Protocol=-0;" + ;
     "B9=0"+ ;
     ";SSLMODE=allow"
Andrus
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform