- Aug
- 2,058
- 83
Below is a batch file that displays the structure of a dBASE database file (.DBF), and displays a record of your choice.
There could be more error checking added, and also to ensure that the .DBF file is indeed a dBASE database. The %dBASEVersion environment variable is declared, and contains the .DBF version, if you wish to do so.
Being able to open and read a dBASE database from TCC has allowed me to eliminate the conversion from .DBF to SDF, and directly access a dBASE database from a batch file.
I know that dBASE is old-school (like me), but hopefully others will find this batch file of use.
Microsoft Excel still allows one to save a spreadsheet as a dBASE .DBF, so there must be others out there who still use dBASE databases.
I was considering writing this as a plugin using Delphi 2, but instead chose to convert my Turbo Pascal code to batch, to show just how powerful the TCC batch language is.
Joe
Code:
::--------------------------------------------------------------------+
:: Open a dBASE database, display the structure, |
:: and a desired record |
:: Joe Caverly |
:: March 2010 |
:: |
::--------------------------------------------------------------------+
@setlocal
@echo off
::--------------------------------------------------------------------+
:: Designate a file to use for this batch |
::--------------------------------------------------------------------+
if not exist %1 goto nofile
set dbfName=%1
::--------------------------------------------------------------------+
:: Get a handle to the file |
::--------------------------------------------------------------------+
set hnd=%@fileopen[%dbfName,read]
if %hnd eq -1 goto abort
::--------------------------------------------------------------------+
:: Move to offset one of the file |
::--------------------------------------------------------------------+
set ptr=%@fileseek[%hnd,0,0]
::--------------------------------------------------------------------+
:: Get the dBASE version (3, or &H83 if there's a memo file) |
::--------------------------------------------------------------------+
set dBASEVersion=%@filereadb[%hnd,1]
::--------------------------------------------------------------------+
:: Move to offset two of the file |
::--------------------------------------------------------------------+
set ptr=%@fileseek[%hnd,1,0]
::--------------------------------------------------------------------+
:: Get the Year of last update |
:: NOTE: 109 = 2009, 110 = 2010 |
::--------------------------------------------------------------------+
set YLU=%@filereadb[%hnd,1]
set YLU=%@eval[1900+%YLU]
::--------------------------------------------------------------------+
:: Move to offset three of the file |
::--------------------------------------------------------------------+
set ptr=%@fileseek[%hnd,2,0]
::--------------------------------------------------------------------+
:: Get The Month of last update |
::--------------------------------------------------------------------+
set MLU=%@filereadb[%hnd,1]
::--------------------------------------------------------------------+
:: Move to offset four of the file |
::--------------------------------------------------------------------+
set ptr=%@fileseek[%hnd,3,0]
::--------------------------------------------------------------------+
:: Get The Day of last update |
::--------------------------------------------------------------------+
set DLU=%@filereadb[%hnd,1]
::--------------------------------------------------------------------+
:: Move to offset five of the file |
::--------------------------------------------------------------------+
set ptr=%@fileseek[%hnd,4,0]
::--------------------------------------------------------------------+
:: Total Number of Records in the File (long integer) |
::--------------------------------------------------------------------+
set TRecords=%@filereadb[%hnd,4]
set b1=%@word[0,%TRecords]
set b2=%@word[1,%TRecords]
set b3=%@word[2,%TRecords]
set b4=%@word[3,%TRecords]
set TRecords=%@eval[%b1 + (%b2 * 256) + (%b3 * 65536) + (%b4 * 16777216)]
::--------------------------------------------------------------------+
:: Move to offset nine of the file |
::--------------------------------------------------------------------+
set ptr=%@fileseek[%hnd,8,0]
::--------------------------------------------------------------------+
:: Number of bytes in the Header |
::--------------------------------------------------------------------+
set BytesInHeader=%@filereadb[%hnd,2]
set b1=%@word[0,%BytesInHeader]
set b2=%@word[1,%BytesInHeader]
set BytesInHeader=%@eval[%b1 + (%b2 * 256)]
::--------------------------------------------------------------------+
:: Move to offset eleven of the file |
::--------------------------------------------------------------------+
set ptr=%@fileseek[%hnd,10,0]
::--------------------------------------------------------------------+
:: Length Of Records |
::--------------------------------------------------------------------+
set LOR=%@filereadb[%hnd,2]
set b1=%@word[0,%LOR]
set b2=%@word[1,%LOR]
set LOR=%@eval[%b1 + (%b2 * 256)]
::--------------------------------------------------------------------+
:: Calculate number of fields in the database |
::--------------------------------------------------------------------+
set FieldCount=%@eval[(%BytesInHeader - 33) / 32]
set FieldPtr=32
::--------------------------------------------------------------------+
:: Display Structure |
::--------------------------------------------------------------------+
echo Structure for database: %dbfName
echo Number of data records: %TRecords
echo Date of last update : %MLU/%DLU/%YLU
echo Field Field Name Type Width Dec
set TotalWidth=0
do FieldNumber=1 to %FieldCount
screen %_row 0 %@format[5,%FieldNumber]
::--------------------------------------------------------------------+
:: Move to offset of field definition |
::--------------------------------------------------------------------+
set ptr=%@fileseek[%hnd,%FieldPtr,0]
::--------------------------------------------------------------------+
:: Field Name |
::--------------------------------------------------------------------+
set FieldName=%@fileread[%hnd,10]
screen %_row 7 %FieldName
::--------------------------------------------------------------------+
:: Move to offset of field type |
::--------------------------------------------------------------------+
set ptr=%@fileseek[%hnd,%@eval[%FieldPtr+11],0]
::--------------------------------------------------------------------+
:: Field Type (C, D, L, M, N) |
::--------------------------------------------------------------------+
set FieldType=%@fileread[%hnd,1]
switch %FieldType
case C
set TheType=Character
case D
set TheType=Date
case L
set TheType=Logical
case M
set TheType=Memo
case N
set TheType=Numberic
endswitch
screen %_row 19 %TheType
::--------------------------------------------------------------------+
:: Move to offset of field width |
::--------------------------------------------------------------------+
set ptr=%@fileseek[%hnd,%@eval[%FieldPtr+16],0]
::--------------------------------------------------------------------+
:: Field Width |
::--------------------------------------------------------------------+
set FieldWidth=%@filereadb[%hnd,1]
screen %_row 33 %@format[3,%FieldWidth]
set TotalWidth=%@eval[%TotalWidth+%FieldWidth]
::--------------------------------------------------------------------+
:: Move to offset of decimal places |
::--------------------------------------------------------------------+
set ptr=%@fileseek[%hnd,%@eval[%FieldPtr+17],0]
::--------------------------------------------------------------------+
:: Number of decimal places (Numeric fields only) |
::--------------------------------------------------------------------+
iff %FieldType eq N then
set DecimalPlaces=%@filereadb[%hnd,1]
iff %DecimalPlaces gt 0 then
screen %_row 40 %@format[2,%DecimalPlaces]
endiff
endiff
::--------------------------------------------------------------------+
:: Increment FieldPtr to next field |
::--------------------------------------------------------------------+
set FieldPtr=%@eval[%FieldPtr+32]
echo.
enddo
::--------------------------------------------------------------------+
:: Add 1 to account for the delete flag |
::--------------------------------------------------------------------+
set TotalWidth=%@inc[%TotalWidth]
screen %_row 0 ** Total **
screen %_row 31 %@format[5,%TotalWidth]
echo.
::--------------------------------------------------------------------+
:: Determine the offset of the first record in the database |
::--------------------------------------------------------------------+
set Record1Off=%@eval[%BytesInHeader+1]
::--------------------------------------------------------------------+
:: Record Number to get |
::--------------------------------------------------------------------+
set Record2Get=1
if %Record2Get gt %TRecords goto ROOR
echo.
echo Record Number: %Record2Get
::--------------------------------------------------------------------+
:: Calculate the offset of the record in the database |
::--------------------------------------------------------------------+
set RecOff=%@eval[((%Record2Get - 1) * %TotalWidth) + %Record1Off]
::--------------------------------------------------------------------+
:: Display the record |
::--------------------------------------------------------------------+
set ptr=%@fileseek[%hnd,%RecOff,0]
::--------------------------------------------------------------------+
:: Move to the offset of the record in the database |
::--------------------------------------------------------------------+
set TheRecord=%@fileread[%hnd,%TotalWidth]
::--------------------------------------------------------------------+
:: Display the record |
::--------------------------------------------------------------------+
echo %TheRecord
::--------------------------------------------------------------------+
:: Close the file |
::--------------------------------------------------------------------+
set rc=%@fileclose[%hnd]
::if %rc eq 0 echo File Closed
::if %rc eq -1 echo Error Occured Closing File
goto eoj
::--------------------------------------------------------------------+
:: Problem opening the database |
::--------------------------------------------------------------------+
:abort
echo Database is in use, or another error occured opening the database.
goto eoj
::--------------------------------------------------------------------+
:: File does not exist |
::--------------------------------------------------------------------+
:nofile
echo File does not exist.
echo USAGE: dbstruct x:\data\mydb.dbf
goto eoj
::--------------------------------------------------------------------+
:: Record Out Of Range |
::--------------------------------------------------------------------+
:ROOR
echo Record Out Of Range (Max. %TRecords)
::--------------------------------------------------------------------+
:: End Of Job |
::--------------------------------------------------------------------+
:eoj
endlocalBeing able to open and read a dBASE database from TCC has allowed me to eliminate the conversion from .DBF to SDF, and directly access a dBASE database from a batch file.
I know that dBASE is old-school (like me), but hopefully others will find this batch file of use.
Microsoft Excel still allows one to save a spreadsheet as a dBASE .DBF, so there must be others out there who still use dBASE databases.
I was considering writing this as a plugin using Delphi 2, but instead chose to convert my Turbo Pascal code to batch, to show just how powerful the TCC batch language is.
Joe