Re: DELETING records using MYSQL Database

From: swdev2 (wsanders.bob_at_bob.efgroup.com)
Date: 11/17/04 

Date: Wed, 17 Nov 2004 13:07:46 -0600

Andy -
if the Perl Guys say it works fine -
then [pick one]:
1. you don't have the rights for a local infile operation for that mysql
database
2. you are missing a 'thing' with the infile - most likely a fully banged
path - ie -
'/usr/tmp/textfile.txt'
3. ask the Perl Guys to run the infile op as a perl script with your file -
then have them give you the perl script back that works, then post it to
here [without the passwords, ah?]

hth - mondo regards [Bill] 

--
William Sanders / Electronic Filing Group  Remove the DOT BOB to reply via
email.
Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
Mondo Cool Satellites -> http://www.efgroup.net/sat
VFP Webhosting? You BET! -> http://efgroup.net/vfpwebhosting
mySql / VFP / MS-SQL
"Andy Trezise" <ANDY@HOME.COM> wrote in message
news:uAA3aeLzEHA.3808@TK2MSFTNGP15.phx.gbl...
> Thanks for that.
>
> I managed to sort out SQLEXEC and 'push' a statement up to the SQL server,
> however this has created another problem. After I've deleted the data on
the
> remote table - DELETE FROM <table>, I want to repopulate it with data from
a
> local table. Since I am now able to delete I thought (instead of using a
> local view) I would have no trouble inserting data by creating a text file
> and uploading it as follows:
>
> x = SQLCONNECT("MYSQL")
>
> y = SQLEXEC(x,"LOAD DATA LOCAL INFILE 'mytext.txt' REPLACE INTO TABLE
> `mytable`")
>
> The problem is SQLEXEC always returns -1 which seems to suggest a
conection
> level error. I've asked one of our web guys to try it under PERL and he
said
> it works fine.
>
> I wondered if anyone had any ideas.
>
>
> "swdev2" <wsanders.bob@bob.efgroup.com> wrote in message
> news:eXsQ%23EFzEHA.1300@TK2MSFTNGP14.phx.gbl...
> > Andy -
> > if you need to delete all of the records back on the back end
> > (and not just the records in the view)
> > then here's some code - things between <> are meta, and you'll need to
> fill
> > in exact values.
> > lnhandle = sqlconnect(<dsn name>,<user name>,<password>
> > =sqlexec(lnhandle ,"delete from <tablename>")
> >
> > going forward - lets take a look at the [where] clause in the remote
view
> > that you initially created.
> > grab the stuff from the where clause [look at the remote view designer,
> ah?]
> >
> > and change the sqlexec() line to read
> > =sqlexec(lnhandle,"delete from <tablename> where <insert where condition
> > here>")
> >
> > hth - you can also look at some things at:
> >
> > http://fox.wikis.com/wc.dll?Wiki~VFPmySQLLinuxP1~VFP
> > and
> > http://groups.yahoo.com/group/vfpmysql
> >
> > also
> > if you do not use a named system dsn, then perhaps you are using a
> > connection string ?
> > you can still create a sqlhandle utilitzing a sql connection string -
the
> > folks at the yahoo group are quite helpful in that regard.
> >
> > also - if you are using a 'named connection' in your database container
in
> > vfp - you can take advantage of that to make yet another sql handle ...
> [its
> > quite nice - no big time nasty coding required]
> >
> > nota bene - I am not a big fan of relying on the odbc stuff created in a
> > remote view from mysql to vfp to automagically handle the deletions - I
> > prefer to sling my own spt [sql pass through] code to accomplish what i
> need
> > to get done.  Remote Views are Nice - but I never rely on one for
> deletion.
> > For Me ? It's a personal choice, made by witnessing lots o failures in
> > MS-SQL, mySql, Oracle, and Sybase - generated VFP remote views ...
Others
> > have different experiences - but I'll stick to spt until someone shows
me
> a
> > metric with another technology that decreases the cycle time from the
> > workstation to the back end server.
> >
> > mondo regards [Bill]
> >
> > --
> > William Sanders / Electronic Filing Group  Remove the DOT BOB to reply
via
> > email.
> > Mondo Cool TeleCom -> http://www.efgroup.net/efgcog.html
> > Mondo Cool WebHosting -> http://www.efgroup.net/efglunar.html
> > Mondo Cool Satellites -> http://www.efgroup.net/sat
> > VFP Webhosting? You BET! -> http://efgroup.net/vfpwebhosting
> > mySql / VFP / MS-SQL
> >
> > "Andy Trezise" <ANDY@HOME.COM> wrote in message
> > news:#gMwMj9yEHA.3976@TK2MSFTNGP09.phx.gbl...
> > > I use a remote view in my database to connect to a MYSQL database on
the
> > > internet. I need to delete ALL the records from the table but have
found
> > > that although the ZAP command seems to clear the local copy of the
table
> > the
> > > remote table still contains all the record once I close and reopen the
> > view
> > > (even after TABLEUPDATE). The only way I have found to do it is by
using
> > the
> > > DELETE FROM "myremotetable" command but this takes absolutely ages to
> > > complete (over 5 minutes for a table with 1000 records). I wondered if
> > there
> > > was a quicker way to empty the table.
> > >
> > > Any help would be much appreciated.
> > >
> > >
> >
> >
>
>

Relevant Pages