Re: DELETING records using MYSQL Database

• Previous message: Andy Trezise: "Re: DELETING records using MYSQL Database"
• In reply to: Andy Trezise: "Re: DELETING records using MYSQL Database"
• Next in thread: swdev2: "Re: DELETING records using MYSQL Database"
• Messages sorted by: [ date ] [ thread ]

Date: Wed, 17 Nov 2004 11:18:39 -0600

Andy -
I've banged on boxes cross the net where the INFILE beast is a problem,
regardless
of the setup at your workstation.

INFILE was buggy on a few mysql releases, and ? you may want to check with
the site admins to see IF the --infile option was used during mySql compile
time [its not set up in the default binary image, alas]

I use a scan..endscan thingie to get my records INTO mySql from vfp ...
here is a code snippet to send a column over from a table via record insert:
*--
lg=SQLCONNECT('efg')
USE lpmail IN 0
GO top
SCAN
lcstring = ALLTRIM(email)
lccmd = "insert into ygsend (addr) values ('" + lcstring + "')"
? lccmd
lha=SQLEXEC(lg,lccmd)
? lha
ENDSCAN
*--
its quite basic, as you can see.
another way is to establish the remote view, append to it, then issue a
tableupdate() at the end of your process for the remote view.

you can verify the INFILE glitch on the remote box usually if you have a
remote phpadmin app on that box. use that, and see if your INFILE fails
miserably [either with a local file or one from your hard disk] - then ? IF
IT FAILS - you are on a site that as the --infile problem ...
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.
> > >
> > >
> >
> >
>
>

• Previous message: Andy Trezise: "Re: DELETING records using MYSQL Database"
• In reply to: Andy Trezise: "Re: DELETING records using MYSQL Database"
• Next in thread: swdev2: "Re: DELETING records using MYSQL Database"
• Messages sorted by: [ date ] [ thread ]