I'm setting up a script to do maintenance on a history-type file of
payments due.

The first statement removes any payments (loyerDu) no longer due because
a user has altered the start or end date of a contract (Location).

It works fine in DBSYS, but gives an error in a DBISAMQuery.ExecSQL.

Here's the code:

DELETE FROM LoyersDus
WHERE loyNum IN
( SELECT loyNum
 FROM LoyersDus, Locations
 WHERE (loyLocation = locNum)
 AND (
       ((locDateFin <> NULL) AND (loyDate > CAST(CAST(EXTRACT
(YEAR,locDateFin) AS CHAR(4))+'-'+CAST(EXTRACT(MONTH,locDateFin) AS CHAR
(2))+'-01' AS DATE)))
       OR (loyDate < CAST(CAST(EXTRACT(YEAR,locDateDebut) AS CHAR(4))
+'-'+CAST(EXTRACT(MONTH,locDateDebut) AS CHAR(2))+'-01' AS DATE))
     )
)
;

And the error is:

Project project1.exe raised an exception class EConvertError with message
''' is not a valid integer value. Process stopped...

Pressing OK and F7 gives a new error box saying:

Project project1.exe raised an exception class EConvertError with message
''--01

These two messages are repeated apparently ad infinitum until I  hit
Ctrl-F2.

I don't understand where the error is coming from, particularly as DBSYS
executes it just fine. I wondered if an empty set for the IN clause might
be the problem, but that seems unlikely...

Here are the table definitions:

/* SQL-92 Table Creation Script with DBISAM Extensions */

CREATE TABLE IF NOT EXISTS "Locations"
(
  "locNum" AUTOINC NOT NULL,
  "locClient" INTEGER NOT NULL,
  "locInstrument" INTEGER NOT NULL,
  "locArchet" BOOLEAN DESCRIPTION 'Archet fourni avec l''instrument'
DEFAULT TRUE,
  "locEtui" BOOLEAN DESCRIPTION 'Etui fourni avec l''instrument' DEFAULT
TRUE,
  "locHousse" BOOLEAN DESCRIPTION 'Housse fournie avec l''instrument'
DEFAULT FALSE,
  "locTarifMensuel" DECIMAL(0,4) DESCRIPTION 'Montant en euros du loyer
mensuel',
  "locCaution" DECIMAL(0,4) DESCRIPTION 'Montant en euros de la
caution',
  "locDateDebut" DATE DESCRIPTION 'Date début de ce contrât pour ce
client' NOT NULL DEFAULT CURRENT_DATE,
  "locDateRenouvellement" DATE DESCRIPTION 'Date du dernier
renouvellement du contrât' DEFAULT CURRENT_DATE,
  "locDuréeMois" INTEGER DESCRIPTION 'Nombre mois entre renouvellements
(0=indéfini)' DEFAULT 12,
  "locDateFin" DATE,
  "locAchat" BOOLEAN DESCRIPTION 'Instrument acheté par client après
location',
  "locNotes" MEMO,
  "locRemisePremierMois" DECIMAL(0,0) NOT NULL DEFAULT 0,
  "locRemiseDernierMois" DECIMAL(0,0) NOT NULL DEFAULT 0,
PRIMARY KEY ("locNum") COMPRESS NONE
DESCRIPTION 'Contrat de location'
LOCALE CODE 1036
USER MAJOR VERSION 1
USER MINOR VERSION 2
);

CREATE INDEX IF NOT EXISTS "ixLocClient" ON "Locations" ("locClient")
COMPRESS DUPBYTE;
CREATE INDEX IF NOT EXISTS "ixLocInstrument" ON "Locations"
("locInstrument") COMPRESS DUPBYTE;
CREATE INDEX IF NOT EXISTS "ixLocDateDebut" ON "Locations"
("locDateRenouvellement") COMPRESS DUPBYTE;

CREATE TABLE IF NOT EXISTS "LoyersDus"
(
  "loyNum" AUTOINC NOT NULL,
  "loyLocation" INTEGER NOT NULL,
  "loyDate" DATE NOT NULL,
  "loyMontantTTC" DECIMAL(0,0) NOT NULL,
  "loyTVA" INTEGER NOT NULL,
  "loyAnneeMois" INTEGER,
PRIMARY KEY ("loyNum") COMPRESS NONE
DESCRIPTION 'Montants dus pour chaque contrat de location'
LOCALE CODE 0
USER MAJOR VERSION 1
);

CREATE INDEX IF NOT EXISTS "ByloyLocation" ON "LoyersDus" ("loyLocation")
COMPRESS FULL;
CREATE INDEX IF NOT EXISTS "ByloyAnneeMois" ON "LoyersDus"
("loyAnneeMois") COMPRESS FULL;

Thanks for any comments

--
Chris

Chris Erdal <chris@No-Spam-erdal.net> wrote in
news:Xns97EF7165FE9F714torcatis@64.65.248.118:

Forgot to mention:
XP Pro SP2 + D7 Architect + DBISAM 4.24 build 1

a few more points:

1/ The problem disappears when executing the program (and hence the script)
outside the IDE

2/ inside the IDE, replacing the DELETE FROM with a SELECT * I can open the
Query with no errors and see the empty result set in a DBGrid, but
compiling and running it I get the same error messages.

perhaps I need to re-install Delphi?

--
Chris

Chris


Since DBSys is simply another app using DBISAM either the DBISAM version differs between DBSys and your app or its a data problem.

I'd check your data. Try running each of the bits in your WHERE clause to try an pin it down.

Roy Lambert

Chris



What happens with


DELETE FROM LoyersDus
WHERE loyNum IN
( SELECT loyNum
 FROM LoyersDus, Locations
 WHERE (loyLocation = locNum)
 AND (
       ((locDateFin <> NULL) AND (loyDate > CAST(CAST(EXTRACT
(YEAR,locDateFin) AS CHAR(4))+'-'+CAST(EXTRACT(MONTH,locDateFin) AS CHAR
(2))+'-01' AS DATE)))
       OR
((locDateDebut <> NULL) AND    <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
(loyDate < CAST(CAST(EXTRACT(YEAR,locDateDebut) AS CHAR(4))
+'-'+CAST(EXTRACT(MONTH,locDateDebut) AS CHAR(2))+'-01' AS DATE))
     )
)
)
;


Roy Lambert

Roy Lambert <roy.lambert@skynet.co.uk> wrote in
news:964FD841-26CD-4AB5-B2D2-5D6B751563F6@news.elevatesoft.com:

> Since DBSys is simply another app using DBISAM either the DBISAM
> version differs between DBSys and your app or its a data problem.

Thanks for replying so fast, Roy.

As you see from my followup, my app works fine, just like DBSYS, when
run outside the IDE.

> I'd check your data.

I'm using the same data for each test.

I verified the 2 tables involved, and got no errors.

> Try running each of the bits in your WHERE clause
> to try an pin it down.

I tried that in DBSYS, and all the bits worked.

:-/
--
Chris

Roy Lambert <roy.lambert@skynet.co.uk> wrote in news:63B7F994-C95C-4397-
8C21-977E6935CF6B@news.elevatesoft.com:

> What happens with
>
>
> DELETE FROM LoyersDus
> ...
> ((locDateDebut <> NULL) AND    <<<<<<<<<<<<<<<<<<<<<<<<<<<<<

I was about to reply "but I know locDateDebut isn't NULL"
when I thought "never count on user data conforming to your
expectations"

But, unfortunately, your SQL gives the same errors (running from inside the
IDE).

Could you possibly install my 2 tables and run the SQL from a default
project with Button opening a DBISAMQuery just to see if it's my IDE setup?
I've eliminated everything I can think of from the packages list, but you
never can tell.

--
Chris

Chris Erdal <chris@No-Spam-erdal.net> wrote in
news:Xns97EF8D40AD48014torcatis@64.65.248.118:

> I tried that in DBSYS, and all the bits worked.
>

Hang on a minute - rereading that I realised I hadn't tried that in the
IDE.

If I remove the WHERE IN part I still get the error (but still just in
the IDE):

SELECT loyNum
 FROM LoyersDus, Locations
 WHERE (loyLocation = locNum)
 AND (
       ((locDateFin <> NULL) AND (loyDate > CAST(CAST(EXTRACT
(YEAR,locDateFin) AS CHAR(4))+'-'+CAST(EXTRACT(MONTH,locDateFin) AS CHAR
(2))+'-01' AS DATE)))
       OR
((locDateDebut <> NULL) AND   
(loyDate < CAST(CAST(EXTRACT(YEAR,locDateDebut) AS CHAR(4))
+'-'+CAST(EXTRACT(MONTH,locDateDebut) AS CHAR(2))+'-01' AS DATE))
     )
)
;


--
Chris

Chris Erdal <chris@No-Spam-erdal.net> wrote in
news:Xns97EF8EA2CC58214torcatis@64.65.248.118:

> Could you possibly install my 2 tables and run the SQL from a default
> project with Button opening a DBISAMQuery just to see if it's my IDE
> setup? I've eliminated everything I can think of from the packages
> list, but you never can tell.
>

I've attached a test project. Just unzip it into a temp folder and open it
in Delphi. It should run OK.

--
Chris

=ybegin line=128 size=4051 name=TestScript.zip
zu-=n>*,***¦œ^************=n***ž—šYzu-=n>*,***œœ^************3***ž—šYn‹ž‹Yzu-=n>*,*2*Wœ^£®I****,**@***ž—šYn‹ž‹Yv™�‹ž“™˜�XŒ–
Œ�Žª*àا§»ôSÑG–5Yg½œ«;Nò2ïÍŠ,*zu-=n>*,*2*Wœ^+–K-î,***\**@***ž—šYn‹ž‹Yv™�‹ž“™˜�XŽ‹žÄy•=}k>êQPw•”5(+¯=@“�’uËÞ5°Noë=n³ë½v¡Qž
Š¡Qøø~=nYˆfR ¡;;f¥zþ9J¤[‰ë½]åƒ�’ÔŽÝX›¨ÎÅP¥fò™°ÁaÅ-+#A™yH¾Ö!á H§$(ð$Â{™�êJ‰ë¼¤Y¥ã=nêÕIè&Vçå~ËX‘²m5mÅIJ=} ¬³}^~·©)ÍV\JYiÙ°
š¼XYÁo¨þ
x3Óû¶q@ös)U`ëXyäÀÝòÕµ©o%Ù³ÃCÚJ'é=M¤Æ3QÏ•JéÎ'
î¶)��HR=I2L‹Å4<²¸Û3Ÿ<œ\þo ãEÛŽu5pÕ£zuä²)ÈF¿(
âJ»ìÁŽ%é?}èž
Ê'y-£©)éw¯2Z²×*fÃO:$éGqÜþºY=IçÙAjìö¬o©5[üØ�
=}HS¦ßx‡¸†8Û5ÛŠþ­Àª`§¬CžLÎExk(B•ê·KÁpQ/ZÙirnÅ‚ÙÆ ImEk]”ì_0(ásÑû&�8>Ï)î›
?I´»¿Ÿ?3×Ø6ÅË8¬è›»G»)¤&ûÔ©êMÅÊñ}_)S€8Ä÷„ÍÚ„˜`RSë¿È)w|›¶—E¹úá™0(5Sé )CŽ-ƒþ‰¤5Œ¶Dm)ɸ»M§’)ýꯊ)AÖµ¾¢Í°™aÒ�òZžŽÖ«â&Ÿš/¢š
�‘¹â€áw†ÜC×ðš()V„¢tóvä=n7R$'éï!ócÛ<³š)©ÈžKÄÓ�+>þ#Á£®LŸR…'½�²þs‚A :_':Ÿ9™&%ëì#õòC^Ć'™z¸q§ø·ÃÕêI,ÇN‡²� ‚=nÁÏ)w:9=I�3Ý
R=I-P´Ä©¬_*ˆwÿ&Qݽž/DŸ9¥=I)”Zís×)Ùß)ÄÂ#/zu-=n>*,*2*Wœ^0·uÌ‚+***œ**@***ž—šYn‹ž‹Yv™�‹ž“™˜�X“Ž¢wtíj>êÎÛ¨¬äœGF¬ÛssJNú=nlÁ”ÝR’oÅ
l/ëÏ=Jì=}³AšMˆë•²›�LÌúwÚZÂ&©Z\Ù½Zvˆ“�ý_TÕ!y9í'ûë=Mï¾ó¸’ÀûŒ�íléiìþééA*§àðÞ¡`øQoÅaÀÚ*}¥#ŸžY*$Ò$9Pý” ˆ!eú¶$™iÆà€
HAãÅÉö”(=n^Í(aŸY*******************************÷ßȆy^É
i&ƒšÁÚ*xÉÿw%+Öd×&FCÿ˜bršQÜ~ôë»Q1'¢J­I‘\AnÔy~ÉôhÕˆ=M«¦ž eÓÏÌ�7Õ“V½
ýòŽP~ûµäT0g‰§9h2m…î¹ÜV÷¡=Mp†X¤‘ !“³ã¡Ì׿éX�ù*****"™h+zu-=n>*,*2*Wœ^�³èƒ,+***>**@***ž—šYn‹ž‹Yv™£�œ�nŸ�XŽ‹ž�‹ª*àا§»ôSÑG–5
Yg½œ«;Nòòª4LBj>=}¾Uêêšáì#Ué¾9 "¿N¿>Uξ@U>Á@SN‘N@ÀÐTN-O´<u>||?œ½=}u\BÒ3úb4r,¶6–c#¿¨Ïã-:fªw@rÚZZr¸p RÊ\ŠŒ=J0ЩI„¾v~-=J
lAÂ(pý)RÊ\Š�Š1ЩÁî¼þ« 3˜*˜™*ýé’$I/~0V6æê9—ç°®b9Þ¥Ú+8:ËîêÊ[Ä(q+¿+Uâ'I<bú8ë-ŠIûi4Ò6B¢ª=I[YY_ÿaiÝ¢ÊÇ­?êü©êjeŽ>6e**zu-=n>*,
*2*Wœ^5¾:n+***œ**@***ž—šYn‹ž‹Yv™£�œ�nŸ�X“Ž¢ëtíZBê=@–}9´Qù¯g±Šd„ÒWÖ¯ÜÍâH0üLë¬=J{=J=}³Y=Jo¦/‰mÖwdn‹Áãlš')J=I
¦ulòl¹Bßí=M
Îi§cg㨥©ÄèÄÜâÝ–×�XE€ ˜è®Ó)¦ß™*$F6´ÕæõµïVù=@Ææ=Iê0® 
a¦GÔ%™‘ƒÄÈÝ#¤½á=M(!ž™********************************àûˆ~ö×bçƒ&�Ú«7²
0=@êPÞ©ª‚ÑøùøÛ{É°Wi3˜1lïË6FC¯�ÃZ»_Yz[|[À[FÓÒ ‚XGå V~  ˜gf8�[ÛMƒvÔ[Ÿ ¬ØôÃÕ¨¡QÔ#èùÜV÷9=Mà¤f„Ƀ=IyÏÒð�—á&Ø»]-****=Jé#,zu
-=n>*,*2*Í}^X=@g±***æ***:***ž—šYzœ™”��ž[XŽšœ�·é4ìZ:ð!j°ltJ�Q;=naS+Œgôs¥3ÁžÛ—\  'ã=I¡k&R˜°å"a8óà„“ßn¶„+†çöÛó ë¾V:m‡¾3X=@Ú
˜/ƒÕ8xpZ˜g›Nøc®³0ÁòÝãNã³h(€AkÁZùG$\¾=I "¡Wæ‘òYÍÿAzu-=n>*,*2*Ëš^aä¨71-**·1**7***ž—šY˜“ž[XŽ�—·~ƒ™Åj:¨Ùþ)Ú|I*ºKšÄpìª=M~
h<JwÕdÔ=@¢•™­¡W8OÙ85¢ÛEßfêÆ=Iö¦öXÉ'L›¸$f‡ÃÁRTé=IK^Ni�3Ç xjµ"„R€!,¾qdùÁlße`Ò-lAu²ßyuáµ
cvBÞ<wU·fÓZïsY‘§F=}Z cõ¹Ox],#È=I¡H°
û9¡=J=nËI—MÄqô&OLÙ£=M–ÔIÃ��F=M?«$Í:¯Â�RNS'Ó_Z=IN=J)(=nÐs\¡}¬�\s=n ›¼¿'£NÍ5@ü=I:Ö­/˽Ã>–õ`jäÍÙNór¤õŒŠo$ËñÐ_ݺuÐ¥oÈ�PÒÀ¬=n
ÕàYÎTàe|Õ#hi¿¤­•0¯›3ÔZ¢€ù~kwÂÅú"ƒBŽŠÓ«µÚãüÍ
åsF kt×Pe”¹Ð—ßÅ–" £{¯b¬JÈ�jf¨0Úm®É/6¦,20Ëño‰ÕQÃF±ær�"+…�™dÝÜúfK)ˆÔ€=JEåÏðØÂ
¥¥ýþÒ ìœ§EdÍ%¬ÎżpÕrUœû–Ç=J÷:ùîÐÓ54”çÃZ‘ç@Ê—9ú_íCÓe¶YÑ\ÏþeÉ´Vtº˜C’xº%cJVñó>\Ðh…zpèº^Õ@õhà–Û^^ÃMÝ´C
ªéâ押F“7±Œ¾Üoàg_
2“Ëi2O¦]X€„�@q\¶úº™nWÙò8¯B�‚v7¤F"«¹ž»‡å/â®»Zï,:cKäGq(·IB°Ä¶gÎÕ0#ºQ0P§ôúÿ_Di6±°6@_ê«ØO¨#ép«=Mp7x'?²$a=I38á’0Þ,а›Ê§b”q
%joüq½›^zý^žÊÊ=@Jøï=IÔd�#¯Bù³¦ÍgÛ¦P””gÖq�oH�âá›�%ÍáðV›„­(·Ã)F¨1[6÷ÖB>=M€þ6S>ÙŠcÙØ…AÅß Ù=IÇíkš€¡XÎ|ÁÁøSØUà„ë\ùÄ
<9zu-=n>*,*2*v›^ÿüA–+**Ô,**7***ž—šY˜“ž[Xš‹�¯|ù•ìZ>èA$iZÚ8ÓÔZW¸[ïã  O—ÈÄ×wt¼TL(õuÔ�Jüè§i¼!pšm€ »6íJ6âZÊ=@Þ*=n·0G0®¦š
ëˆ!ò6ÞÐEÚ úUíu…ç}ïÓZàDÁ~•NÉ>×áæêÀ>ps¾wÎÔž9ý|xuãë6íðpKø¤®†ýô’ö_‡Ø�8_Ê¡³[3£J/È?coìð¸ÈÝñÌ+ŸBº?D¤¨þBM¯Ï¦û }”’PE*ÄX=JöäUNX
9Õà‡Uƒ*•>ÞË=M¼A=I{0¬«Ü£(/¯d›×"¸DèM£ºZÚ-R”â>ÄÆÆÚó—ôoú7ÃgíWŽq?lá¸kå@)+Õä®4®›XFò§ö=@ÿ3ûçS(‘®F`ð%©`F‘µ“f¿¾w¾ÖPæ®Ò�eÉÕ“äv
º"ÙGD&üÝ×ái|þÃ麸uÓ=}Ʊßb6¨*zu+,>*>*,***¦œ^************=n*********Z*******ž—šYzu+,>*>*,***œœ^************3*********Z***
L***ž—šYn‹ž‹Yzu+,>*>*,*2*Wœ^£®I****,**@*********J***s***ž—šYn‹ž‹Yv™�‹ž“™˜�XŒ–Œzu+,>*>*,*2*Wœ^+–K-î,***\**@*********J***Æ***ž
—šYn‹ž‹Yv™�‹ž“™˜�XŽ‹žzu+,>*>*,*2*Wœ^0·uÌ‚+***œ**@*********J***¾-**ž—šYn‹ž‹Yv™�‹ž“™˜�X“Ž¢zu+,>*>*,*2*Wœ^�³èƒ,+***>**@*********J
***J/**ž—šYn‹ž‹Yv™£�œ�nŸ�XŽ‹žzu+,>*>*,*2*Wœ^5¾:n+***œ**@*********J***€0**ž—šYn‹ž‹Yv™£�œ�nŸ�X“Ž¢zu+,>*>*,*2*Í}^X=@g±***æ***:*
********J***ø1**ž—šYzœ™”��ž[XŽšœzu+,>*>*,*2*Ëš^aä¨71-**·1**7*********J***­2**ž—šY˜“ž[XŽ�—zu+,>*>*,*2*v›^ÿüA–+**Ô,**7********
*J***ß5**ž—šY˜“ž[Xš‹�zu/0****4*4*›,**v7****
=yend size=4051 crc32=c0bd866f

Chris


You attach it - I'll try it

Roy Lambert

Chris Erdal <chris@No-Spam-erdal.net> wrote on Tue, 27 Jun 2006 08:26:38 -0400

>Chris Erdal <chris@No-Spam-erdal.net> wrote in
>news:Xns97EF8EA2CC58214torcatis@64.65.248.118:
>
>> Could you possibly install my 2 tables and run the SQL from a default
>> project with Button opening a DBISAMQuery just to see if it's my IDE
>> setup? I've eliminated everything I can think of from the packages
>> list, but you never can tell.
>>
>
>I've attached a test project. Just unzip it into a temp folder and open it
>in Delphi. It should run OK.
>
>--
>Chris