how to pack dbase-file with sql-statement

Lutz Schneider

unread,

Mar 25, 2002, 5:51:08 AM3/25/02

to

Hello, All,

in dts i want to send an sql-statment that deletes physicaly data in
dbase4-file.

'delete * from dbf' deletes all entries but i want to sent something like
pack-statement in codebase to delete the delete-marked entries.

Thanks!

Lutz Schneider!

Allan Mitchell

unread,

Mar 25, 2002, 9:19:59 AM3/25/02

to

All you should need to do is add a WHERE clause

i.e

delete * from dbf where column = value

Need any more help then SHOUT

Allan Mitchell
www.allisonmitchell.com

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Lutz Schneider

unread,

Mar 25, 2002, 9:45:23 AM3/25/02

to

Hello, Allan,

i need no Where-Clause because the entries are marked as deleted in
dbf-File. All Entries I want to delete are deleted, but the dbf-File is not
realy empty. The Entries are marked as deleted in dbf-File.

What I need is a SQL-Satement that kill the delete-marked entries in
dbf-File!!

Thanks Lutz !!!

"Allan Mitchell" <ammit...@cmpinformation.com> schrieb im Newsbeitrag
news:#A8omgA1BHA.1628@tkmsftngp03...

Allan Mitchell

unread,

Mar 25, 2002, 10:00:05 AM3/25/02

to

How are they marked as deleted ?

Can you send a sample row illustrating this ?

Andrew J. Kelly

unread,

Mar 25, 2002, 10:45:26 AM3/25/02

to

Allan,

This is a dbase or FoxPro thing, not SQL Server related.

Lutz,

You can't do this thru SQL Server with any SQL command as far as I know..
The ODBC driver may support the PACK or ZAP commands but I haven't tried it.

--
Andrew J. Kelly SQL MVP
Targitinteractive, Inc.

"Allan Mitchell" <ammit...@cmpinformation.com> wrote in message
news:uYpeA3A1BHA.2856@tkmsftngp05...

Allan Mitchell

unread,

Mar 25, 2002, 10:56:05 AM3/25/02

to

OK

Explain to me the meaning of it so if I encounter it in the future i
will know

also the Execute SQL task is connection specific in the SQL Statement is
it not ? so if you can do it in a SQL Statement in Foxpro dbase then
this shoulsd de doable here no ?

Darren Green

unread,

Mar 25, 2002, 2:48:44 PM3/25/02

to

In article <OYFwGQB1BHA.2068@tkmsftngp05>, Andrew J. Kelly
<ake...@targitinteractive.com> writes

>Allan,
>
>This is a dbase or FoxPro thing, not SQL Server related.
>
>Lutz,
>
>You can't do this thru SQL Server with any SQL command as far as I know..
>The ODBC driver may support the PACK or ZAP commands but I haven't tried it.
>
>
>

As you say the pack command is specific to FoxPro and such like, so when
using an Exec SQL Task against a FoxPro connection it seems that it may
work. Firstly it may fail validation, but using disco edit can avoid
this. It may still fail if unsupported by the ODBC interface.

Failing that you will need to use an Execute Process Task to fire up
FoxPro from a command line, specifying a simple script file containing
the pack command.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

Douglas Laudenschlager [MS]

unread,

Mar 25, 2002, 3:53:22 PM3/25/02

to

Lutz,

Try this syntax over an ADO connection to the DBF file. Apparently it works
with FoxPro files, and I'm hoping that means dBase too:

rs.Open "set exclusive on; select * from table; pack"

-Doug

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology
Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

Andrew J. Kelly

unread,

Mar 25, 2002, 7:28:58 PM3/25/02

to

> Explain to me the meaning of it so if I encounter it in the future i
> will know

In FoxPro and dBase when you delete a row it doesn't actually remove it from
the table, it just sets an internal flag to say it is deleted. Most
commands obey this flag and thus won't see the row if it is "deleted". Once
deleted you can recover the row by swapping this flag back. To totally
remove the row you need to issue a command called PACK that basically pulls
out all the undeleted rows, truncates the table and puts them back (although
a bit more involved than that) behind the scenes. ZAP is essentially the
equivalent of TRUNCATE in SQL Server.

> also the Execute SQL task is connection specific in the SQL Statement is

> it not ? so if you can do it in a SQL Statement in FoxPro dbase then
> this should de doable here no ?

Well the VFP ODBC driver is essentially the FoxPro engine itself and
although I have used ODBC to go from VFP to SQL (and others) I have never
tried it going inwards. So I suspected the ODBC driver was capable of
allowing the command to be executed via a pass thru type method I have never
tried it myself.

--
Andrew J. Kelly, SQL Server MVP
TargitInteractive

Allan Mitchell

unread,

Mar 26, 2002, 1:29:29 PM3/26/02

to

Sounds quite involved all this internally marking the records but not
actually getting rid of them. Thinking about it though it's a not bad
safeguard is it?. When i make a data change I back up the affected table
beforehand so I have something to go back to. In the event of a goof i
replace the data but Foxpro doesn't need to do that you say DELETE and it
removes them visually but not actually.

Hmmmmm

--

Allan Mitchell
www.allisonmitchell.com

"Andrew J. Kelly" <ake...@targitinteractive.com> wrote in message
news:#pIep0F1BHA.2036@tkmsftngp03...