Fastest way to delete records
in [Visual Objects]
|
From: Joachim Duerr (ADS) on 5 Feb 2010 03:07 Martin wrote: > >I have never done SQL from VO, have you got any samples please? there's a ADS SQL RDD available, which is the easiest way. But this one requires a resultset (where a delete doesn't return one). So I'd suggest to pass in a script: AdsConnect60( "X:\DATA", ADS_REMOTE_SERVER,"", "", ADS_DEFAULT, @hConnect ) AX_SetConnectionHandle( hConnect ) sql:="DELETE FROM mytable WHERE condition; select ::stmt.UpdateCount from system.iota;" DBServer{ sql, , , �AXSQLCDX� } -- Joachim Duerr Advantage Presales check out my new ADS book on http://www.jd-engineering.de/adsbuch
From: Martin on 5 Feb 2010 05:54 I am trying this but am getting Description: Error 7200: AQE Error: State = 42000; NativeError = 2115; [Extended Systems][Advantage SQL Engine]Expected lexical element not found: Your statement must start with CREATE, DROP, SELECT, INSERT, UPDATE, ALTER, EXECUTE, DELETE, GRANT, REVOKE, BEGIN, CO FileHandle: 0 FileName: FuncPtr: 0x00000000 FuncSym: INIT GenCode: 27 : Undefined class MaxSize: 0 MethodSelf: 08') Operation: OsCode: 0 : Successful completion Severity: ERROR SubCode: 7200 SubCodeText: SubstituteType: NIL SubSystem: AXSQLCDX "DELETE FROM OPT011D WHERE PL_PDATE <= CToD('01/05/08')" HELP Please Thanks Martin "Joachim Duerr (ADS)" <jojo.duerr(a)gmx.de> wrote in message news:7t21ujFfo6U1(a)mid.individual.net... > Martin wrote: > >> >>I have never done SQL from VO, have you got any samples please? > > there's a ADS SQL RDD available, which is the easiest way. But this one > requires a resultset (where a delete doesn't return one). > So I'd suggest to pass in a script: > > > AdsConnect60( "X:\DATA", ADS_REMOTE_SERVER,"", "", ADS_DEFAULT, > @hConnect ) > AX_SetConnectionHandle( hConnect ) > sql:="DELETE FROM mytable WHERE condition; select ::stmt.UpdateCount > from system.iota;" > DBServer{ sql, , , "AXSQLCDX" } > > -- > Joachim Duerr > Advantage Presales > check out my new ADS book on http://www.jd-engineering.de/adsbuch
From: Joachim Duerr (ADS) on 5 Feb 2010 07:44 Martin wrote: >"DELETE FROM OPT011D WHERE PL_PDATE <= CToD('01/05/08')" CTOD is not a SQL function "DELETE FROM OPT011D WHERE PL_PDATE <= '2008/05/01'" -- Joachim Duerr Advantage Presales check out my new ADS book on http://www.jd-engineering.de/adsbuch
From: Willie Moore on 5 Feb 2010 08:24 Martin, Build your SQL like this: LOCAL cSQL as String cSQL := "DELETE FROM OPT011D WHERE PL_PDATE <= '" + CToD('01/05/08') + "'" DBServer{ cSql, , , "AXSQLCDX" } This also give you the advantage of being able to log your SQL statements. In my code, I use the __DEBUG to send the SQL statements to my log file but only when the debug flag is set. Regards, Willie "Martin" <spam(a)spam.spam> wrote in message news:uNSan.218550$cU2.119762(a)newsfe22.ams2... > I am trying this but am getting > > Description: Error 7200: AQE Error: State = 42000; NativeError = > 2115; [Extended Systems][Advantage SQL Engine]Expected lexical element > not found: Your statement must start with CREATE, DROP, SELECT, INSERT, > UPDATE, ALTER, EXECUTE, DELETE, GRANT, REVOKE, BEGIN, CO > FileHandle: 0 > FileName: > FuncPtr: 0x00000000 > FuncSym: INIT > GenCode: 27 : Undefined class > MaxSize: 0 > MethodSelf: 08') > Operation: > OsCode: 0 : Successful completion > Severity: ERROR > SubCode: 7200 > SubCodeText: > SubstituteType: NIL > SubSystem: AXSQLCDX > > > "DELETE FROM OPT011D WHERE PL_PDATE <= CToD('01/05/08')" > > HELP > > Please > > Thanks > > Martin > > > "Joachim Duerr (ADS)" <jojo.duerr(a)gmx.de> wrote in message > news:7t21ujFfo6U1(a)mid.individual.net... >> Martin wrote: >> >>> >>>I have never done SQL from VO, have you got any samples please? >> >> there's a ADS SQL RDD available, which is the easiest way. But this one >> requires a resultset (where a delete doesn't return one). >> So I'd suggest to pass in a script: >> >> >> AdsConnect60( "X:\DATA", ADS_REMOTE_SERVER,"", "", ADS_DEFAULT, >> @hConnect ) >> AX_SetConnectionHandle( hConnect ) >> sql:="DELETE FROM mytable WHERE condition; select ::stmt.UpdateCount >> from system.iota;" >> DBServer{ sql, , , "AXSQLCDX" } >> >> -- >> Joachim Duerr >> Advantage Presales >> check out my new ADS book on http://www.jd-engineering.de/adsbuch > > > > __________ Information from ESET NOD32 Antivirus, version of virus > signature database 4837 (20100205) __________ > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com > > > __________ Information from ESET NOD32 Antivirus, version of virus signature database 4837 (20100205) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com
From: Ginny Caughey on 5 Feb 2010 09:10
Hi Willie, I may be showing my ignorance of Advantage here, but for SQL Server I always use DTOS instead of CtoD. That way in works outside the US too. -- Ginny Caughey www.wasteworks.com "Willie Moore" <williem(a)wmconsulting.com> wrote in message news:hkh676$9tc$1(a)speranza.aioe.org... > Martin, > Build your SQL like this: > LOCAL cSQL as String > cSQL := "DELETE FROM OPT011D WHERE PL_PDATE <= '" + CToD('01/05/08') + > "'" > DBServer{ cSql, , , "AXSQLCDX" } > > This also give you the advantage of being able to log your SQL statements. > In my code, I use the __DEBUG to send the SQL statements to my log file > but only when the debug flag is set. > > Regards, > Willie > > "Martin" <spam(a)spam.spam> wrote in message > news:uNSan.218550$cU2.119762(a)newsfe22.ams2... >> I am trying this but am getting >> >> Description: Error 7200: AQE Error: State = 42000; NativeError = >> 2115; [Extended Systems][Advantage SQL Engine]Expected lexical element >> not found: Your statement must start with CREATE, DROP, SELECT, INSERT, >> UPDATE, ALTER, EXECUTE, DELETE, GRANT, REVOKE, BEGIN, CO >> FileHandle: 0 >> FileName: >> FuncPtr: 0x00000000 >> FuncSym: INIT >> GenCode: 27 : Undefined class >> MaxSize: 0 >> MethodSelf: 08') >> Operation: >> OsCode: 0 : Successful completion >> Severity: ERROR >> SubCode: 7200 >> SubCodeText: >> SubstituteType: NIL >> SubSystem: AXSQLCDX >> >> >> "DELETE FROM OPT011D WHERE PL_PDATE <= CToD('01/05/08')" >> >> HELP >> >> Please >> >> Thanks >> >> Martin >> >> >> "Joachim Duerr (ADS)" <jojo.duerr(a)gmx.de> wrote in message >> news:7t21ujFfo6U1(a)mid.individual.net... >>> Martin wrote: >>> >>>> >>>>I have never done SQL from VO, have you got any samples please? >>> >>> there's a ADS SQL RDD available, which is the easiest way. But this one >>> requires a resultset (where a delete doesn't return one). >>> So I'd suggest to pass in a script: >>> >>> >>> AdsConnect60( "X:\DATA", ADS_REMOTE_SERVER,"", "", ADS_DEFAULT, >>> @hConnect ) >>> AX_SetConnectionHandle( hConnect ) >>> sql:="DELETE FROM mytable WHERE condition; select ::stmt.UpdateCount >>> from system.iota;" >>> DBServer{ sql, , , "AXSQLCDX" } >>> >>> -- >>> Joachim Duerr >>> Advantage Presales >>> check out my new ADS book on http://www.jd-engineering.de/adsbuch >> >> >> >> __________ Information from ESET NOD32 Antivirus, version of virus >> signature database 4837 (20100205) __________ >> >> The message was checked by ESET NOD32 Antivirus. >> >> http://www.eset.com >> >> >> > > __________ Information from ESET NOD32 Antivirus, version of virus > signature database 4837 (20100205) __________ > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com > > > |