Fastest way to delete records
in [Visual Objects]
|
From: Martin on 5 Feb 2010 09:43 This date format worked, also my cleardown started 7 days before 01/05/08, I forgot that bit!!!! DELETE FROM OPT011D WHERE PL_PDate <= '2008-04-24';select * FROM OPT011D; The select code you showed me did not work "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: Martin on 5 Feb 2010 10:09 Well it is a lot quicker than client side delete. I now want to check the processes, so will need to do a string field as well Thanks Martin "Martin" <spam(a)spam.spam> wrote in message news:R7Wan.190422$NW.103985(a)newsfe08.ams2... > This date format worked, also my cleardown started 7 days before 01/05/08, > I forgot that bit!!!! > > DELETE FROM OPT011D WHERE PL_PDate <= '2008-04-24';select * FROM OPT011D; > > The select code you showed me did not work > > > > > > "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: Martin on 5 Feb 2010 11:13 I am trying some more advanced stuff now and this unfortunately crashes with a 5333 Description: Application Code Error causing Access Violation FileHandle: 0 FileName: FuncPtr: 0x10020014 FuncSym: GenCode: 50 : Access Violation MaxSize: 0 MethodSelf: {(0x0000)0x00000000} CLASS Operation: OsCode: 3221225477 : Unknown error Severity: ERROR SubCode: 5333 SubCodeText: SubstituteType: NIL SubSystem: VO-CODE Tries: 0 DELETE FROM OPT011D WHERE PL_PDate <= '2008-07-25' AND PL_Process IN ('01 ','02 ','10 ','11 ','14 ','15 ','40 ','41 ','45 ','46 ','60 ','61 ','65 ','71 ','72 ','73 ','74 ','75 ','76 ');select * FROM OPT011D; Is there anything wrong with this line? Thanks Martin "Joachim Duerr (ADS)" <jojo.duerr(a)gmx.de> wrote in message news:7t2i62F8ibU1(a)mid.individual.net... > 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: Joachim Duerr (ADS) on 5 Feb 2010 12:52 Martin wrote: >The select code you showed me did not work which ADS version do you have? It might be that that system variable is not supported in your version. -- Joachim Duerr Advantage Presales check out my new ADS book on http://www.jd-engineering.de/adsbuch
From: Willie Moore on 5 Feb 2010 13:27
Ginny, You are correct. Case of copy /paste gone wrong. Regards, Willie "Ginny Caughey" <ginny.caughey.online(a)wasteworks.com> wrote in message news:004b5aaa$0$28419$c3e8da3(a)news.astraweb.com... > 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 >> >> >> > > __________ Information from ESET NOD32 Antivirus, version of virus > signature database 4839 (20100205) __________ > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com > > > __________ Information from ESET NOD32 Antivirus, version of virus signature database 4839 (20100205) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com |