Database Search

TechHome

Examples from Users

• !!NEWSFLASH!!
• An Empty Record Gets Created
• dbGetEntireRecord Quotes
• dbOpen 10012 Read-Only Error
• dbSort Issue
• LAFFDB Example
• LAFFDB Extender De-emphasized
• LAFFDB Leaving Emtpy Row in Table
• LAFFDB Table Browser
• Limitations of LAFFDB
• Look for Number in Field and Retrieve Line
• Remove Duplicate Records Using Laffdb
• RemoveDups
• Search Addresses by Area Code

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

LAFFDB Table Browser

Keywords:   LAFFDB Table Browser

;*******************************************************
;**     This script is a simple table browser based on 
;**     the LAFFDB Extender.  It is intended to be an 
;**	  instructional model to demonstrate use of the LAFFDB
;**	  Extender.
;**
;**	  New/revised versions may be provided from time to time.
;**	  
;**	  Substantial use is made of 
;**	  code provided in the LAFFDB Extender HELP file,
;**	  but the blame for any misuse of those examples lies
;**	  solely with the author.
;**
;**	  No warranty is expressed or implied as to the 
;**	  suitablilty of this code for any current or future
;**     application.  You may use this code for any purpose
;**	  subject to your own determination of its applicability
;**	  and provided you do not remove the authorship 
;**	  attributions.
;**
;**
;**	  LAFFDB Table Browser, David R. Watson, 2002
;**	  david@davidrwatson.com

;**			USAGE:
;**
;**			The script assumes that tables are ".csv" files (no other extension is permitted)
;**			and that the first line of the table is a list of column names.
;**
;**			A MAX of 10 columns is permitted in a table.  (I know, I know... but it made things
;**			simpler!  A dynamic number of columns is probably in the offing...) No datatypes are
;**			supported -- everything is assumed to be a string.
;**
;**			When you OPEN a table or make a NEW TABLE, the script does a DIRCHANGE() to the 
;**			directory that holds the table.  It won't let you create a New Table over an existing 
;**			one, and it won't let you insert or change column names.  (A MODIFY function will 
;**			probably appear with the dynamic number of columns thingy.)

;**			Also, when you create a New Table, you get an empty dialog returned, so you need to
;**			OPEN the New Table to add records.  (I'll probably fix that as I get around to cleaning
;**			up the code..)

;				The navigation buttons are (I hope) self-explanatory, with a few notes:
;						Grid calls dbDebug()
;						Update writes the currently displayed record to the table
;						Delete deletes the current record
;						New Record appends a new record to the end of the table and 
;								positions to it for entry of column data
;						Sort lets you select a column, then sorts the table in ascending 
;								order on that column.
;						Find lets you select a column, then displays a list of the values
;								in that column and Selects the first record containing that value.

;		There are a few reundant variables resulting from hasty (careless?) code reuse.  I will
;		fix them in future versions -- meanwhile, be careful out there.
 
;**	  I wrote this as an exercise to learn the new extender, and that 
;**	  is the spirit in which this code is presented.  Enjoy!
 
;**			Feel free to email me with any questions or feedback: david@davidrwatson.com
;*******************************************************
AddExtender("Laffd34i.dll")
count=1
currdir=DirGet()
currdrive=StrSub(currdir,1,2)
dbfile="(None Selected)"
handle=""
goto top
;*******************************************************
;**  
;*******************************************************

:opentable
;********************************************************
;**   Example Database
;********************************************************
filename = AskFileName("ASCII Table to Browse", currdir, "ASCII Table|*.csv", "*.csv", 1)
dbfile=filename
delimiter = ","
if !FileExist(filename)
   str = "Make sure the database exists before attempting to run this example."
   str2 = "See the dbOpen example in this help file, it will create the example database for you."
   Message("Database doesn't exist", StrCat(str, @CRLF,str2))
exit
Endif
;********************************************************
;**   dbOpen - open the test database 
;*******************************************************
bCreateOK = 0 ;File MUST exist
model = 2  ;Traditional
numcols = -1  ;Number of columns match first line, names them, starts reading at 2nd line
format = 0
optionstring = ""
handle = dbOpen (filename, bCreateOK, model, numcols, format, delimiter, optionstring)
if handle == @LAFFDBERROR
    err = dbGetLastError()

 Message("Error Opening the database specified", err)
	 exit
Endif

DirChange(FilePath(filename)) ; change working directory to new table
currdir=DirGet()
currdrive=StrSub(currdir,1,2)

currentrecord=1
;********************************************************
;**   Get Record Count  and Column Names
;********************************************************
reccnt = dbGetRecordCount(handle)
cnt=reccnt
; Message("recs", reccnt)
count = dbGetColumnCount(handle)
if count > 10 then
    message("Table has too many columns!", count)
	 handle=""
	 goto top
endif
columnlist = ""
 For column = 1 to count
	 name = dbGetColumnName (handle, column)
	 if name == "" 
		  err = dbGetLastError()
	     Message("Error getting column name", err)
	 endif
	 n%column%=name
	 columnlist =Strcat(columnlist, @tab, name)
Next

gosub getfirst
;*******************************************************
;**  
;*******************************************************

:top
msg="of"
MyDialogFormat=`WWWDLGED,5.0`

MyDialogCaption=`Browsing LAFFDB Table %dbfile%`
MyDialogX=107
MyDialogY=29
MyDialogWidth=294
MyDialogHeight=198
MyDialogNumControls=39

MyDialog01=`218,170,64,DEFAULT,PUSHBUTTON,DEFAULT,"Exit",99`
MyDialog02=`0,12,80,DEFAULT,STATICTEXT,DEFAULT,"%n1%:"`
MyDialog03=`82,12,154,DEFAULT,EDITBOX,v1,""`
MyDialog04=`0,24,80,DEFAULT,STATICTEXT,DEFAULT,"%n2%:"`
MyDialog05=`82,24,154,DEFAULT,EDITBOX,v2,""`
MyDialog06=`0,36,80,DEFAULT,STATICTEXT,DEFAULT,"%n3%:"`
MyDialog07=`82,36,154,DEFAULT,EDITBOX,v3,""`
MyDialog08=`0,48,80,DEFAULT,STATICTEXT,DEFAULT,"%n4%:"`
MyDialog09=`82,48,154,DEFAULT,EDITBOX,v4,""`
MyDialog10=`0,60,80,DEFAULT,STATICTEXT,DEFAULT,"%n5%:"`
MyDialog11=`82,60,154,DEFAULT,EDITBOX,v5,""`
MyDialog12=`0,72,80,DEFAULT,STATICTEXT,DEFAULT,"%n6%:"`
MyDialog13=`82,72,154,DEFAULT,EDITBOX,v6,""`
MyDialog14=`0,84,78,DEFAULT,STATICTEXT,DEFAULT,"%n7%:"`
MyDialog15=`82,84,154,DEFAULT,EDITBOX,v7,""`
MyDialog16=`0,96,80,DEFAULT,STATICTEXT,DEFAULT,"%n8%:"`
MyDialog17=`82,96,154,DEFAULT,EDITBOX,v8,""`
MyDialog18=`0,108,80,DEFAULT,STATICTEXT,DEFAULT,"%n9%:"`
MyDialog19=`82,108,154,DEFAULT,EDITBOX,v9,""`
MyDialog20=`0,120,80,DEFAULT,STATICTEXT,DEFAULT,"%n10%:"`
MyDialog21=`82,120,154,DEFAULT,EDITBOX,v10,""`
MyDialog22=`4,170,36,DEFAULT,PUSHBUTTON,DEFAULT,"Update",1`
MyDialog23=`44,170,36,DEFAULT,PUSHBUTTON,DEFAULT,"Delete",2`
MyDialog24=`86,170,36,DEFAULT,PUSHBUTTON,DEFAULT,"New Record",5`
MyDialog25=`58,154,36,DEFAULT,PUSHBUTTON,DEFAULT,"<<",3`
MyDialog26=`100,154,36,DEFAULT,PUSHBUTTON,DEFAULT,">>",4`
MyDialog27=`86,138,36,DEFAULT,VARYTEXT,msg,""`
MyDialog28=`4,138,36,DEFAULT,STATICTEXT,DEFAULT,"Curr Record:"`
MyDialog29=`40,138,36,DEFAULT,VARYTEXT,recordnum,""`
MyDialog30=`110,138,36,DEFAULT,VARYTEXT,cnt,""`
MyDialog31=`16,154,36,DEFAULT,PUSHBUTTON,DEFAULT,"|<<",80`
MyDialog32=`142,154,36,DEFAULT,PUSHBUTTON,DEFAULT,">>|",81`
MyDialog33=`128,170,36,DEFAULT,PUSHBUTTON,DEFAULT,"Sort",6`
MyDialog34=`252,4,36,DEFAULT,PUSHBUTTON,DEFAULT,"Help",8`
MyDialog35=`6,184,158,DEFAULT,STATICTEXT,DEFAULT,"LAFFDB Table Browser, David R. Watson, 2002"`
MyDialog36=`172,170,36,DEFAULT,PUSHBUTTON,DEFAULT,"Find",7`
MyDialog37=`252,20,36,DEFAULT,PUSHBUTTON,DEFAULT,"Open",9`
MyDialog38=`252,40,36,DEFAULT,PUSHBUTTON,DEFAULT,"Grid",10`
MyDialog39=`252,60,36,DEFAULT,PUSHBUTTON,DEFAULT,"New Table",11`

ButtonPushed=Dialog("MyDialog")

;*******************************************************
;**  
;*******************************************************

if buttonpushed==9 then goto opentable
if buttonpushed==11 then 
   if handle != "" then gosub saveandclose ;close open table
   columnlist = ""
   For column = 1 to count
	  v%column%=""
	  n%column%=""
   Next
     goto newtable
endif
if buttonpushed==99 then
  if handle=="" then exit

  gosub saveandclose

  Exit
endif

if buttonpushed==8 then 
    gosub help
	 goto top
endif

if handle=="" then
   if buttonpushed != 9 then
	   Message("No Table Selected", "Use OPEN to select a table.")
		goto top
	 endif
endif
if buttonpushed==1 then gosub updaterec
if buttonpushed==2 then gosub deleterec
if buttonpushed==3 then gosub getprev
if buttonpushed==4 then gosub getnext
if buttonpushed==5 then gosub newrecord
if buttonpushed==6 then gosub sortit
if buttonpushed==7 then gosub findit
if buttonpushed==10 then
	;*******************************************************
	;**   dbDebug
	;*******************************************************
	ret = dbDebug(handle)
	if ret == @LAFFDBERROR
		 err = dbGetLastError()
		 Message("Error using dbDebug", err)
	endif
endif

if buttonpushed==80 then gosub getfirst
if buttonpushed==81 then gosub getlast


goto top

;*******************************************************
;**  
;*******************************************************
:updaterec

for i=1 to count
   ret=dbSetRecordField(handle, recordnum, i, 2, v%i%)
	if ret == @LAFFDBERROR
	   err = dbGetLastError()
	   Message("Error Setting record field", err)
   endif
Next
gosub saveit
return
;*******************************************************
;**  
;*******************************************************

:deleterec
;********************************************************
;**   Delete chosen record
;********************************************************
answer = AskYesNo( "Delete Record?", "Do you want to delete this record number: %recordnum%")
If answer == @YES
	ret = dbDeleteRecord(handle, recordnum, 2)
	if ret == @LAFFDBERROR
	   err = dbGetLastError()
	   Message("Error deleting record: %ret% ", err)

   endif
Endif
gosub saveit
recordnum=prevrec
if recordnum == reccnt then
   recordnum=reccnt-1
endif
if recordnum > reccnt then
   recordnum=reccnt-1
endif
gosub getprev
return

;*******************************************************
;**  
;*******************************************************

:getprev
flag=64
  prevrec=recordnum
  recordnum = dbGetNextItem(handle, recordnum, flag)
  if recordnum == -1 then 
	  gosub getfirst
     return
  endif ;Reached end of database
  if recordnum == @LAFFDBERROR
		 err = dbGetLastError()
	    Message("Error getting next record", err)
  endif
  gosub getfields
  return
;*******************************************************
;**  
;*******************************************************

:getnext
flag=2
  prevrec=recordnum
  recordnum = dbGetNextItem(handle, recordnum, flag)
  if recordnum == -1 then 
	  gosub getlast
     return
  endif ;Reached end of database
  if recordnum == @LAFFDBERROR
		 err = dbGetLastError()
	    Message("Error getting next record", err)
  endif
  gosub getfields
  return
;*******************************************************
;**  
;*******************************************************

:newrecord
;*******************************************
;**   dbMakeNewItem
;*******************************************
;record = dbMakeNewItem(handle, 1) ;Fixed record number
recordnum = dbMakeNewItem(handle, 2) ;variable record number
if recordnum == @LAFFDBERROR
	 err = dbGetLastError()
	 Message("Error Making a new entry", err)
endif

gosub getlast

return

;*******************************************************
;**  
;*******************************************************

:sortit

While @true ;Make sure the user selects a column
   columnname = AskItemList("Choose column to Sort", columnlist, @tab, @unsorted, @single)
   if columnname != "" then break
endwhile

;*********************************************************
;**   dbSort
;*********************************************************
ret = dbSort(handle, columnname, 1) 
if ret == 0
	 err = dbGetLastError()
	 Message("Error sorting column", err)

endif
gosub getfirst
return

;*******************************************************
;**  
;*******************************************************
:findit

columnname = AskItemList("Choose column to get records from", columnlist, @tab, @unsorted, @single)


;*******************************************************
;**   Get all records in the chosen Column - using dbGetNextItem
;*******************************************************
recordnum = -1 ;get the first item 
flag = 16

list = ""
While @true
  recordnum = dbGetNextItem(handle, recordnum, flag)
  flag = 2 ;variable record number

  if recordnum == -1 then break ;Reached end of database
  if recordnum == @LAFFDBERROR
		 err = dbGetLastError()
	    Message("Error getting next record", err)
  endif

  field = dbGetRecordField(handle, recordnum, columnname, 2)
  list = strcat(list,@tab,field)
EndWhile

list = StrTrim(list)
findvalue=AskItemList("List of records in the %columnname% column", list, @tab, @unsorted, @single)

recordnum = -1 ;search entire database
;flags = 1|4; fixed record number
;column = "LastName"; column nmae to search in
;findvalue = "Marley"
matchcase = 0 ;NOT case sensitive 
flags=2
;*****************************************************
;**   dbFindRecord
;*****************************************************
record  = dbFindRecord(handle,recordnum,flags,columnname,findvalue,matchcase)
if record  == @LAFFDBERROR

    err = dbGetLastError()
	 Message("Error finding record", err)
	 exit
Endif
if record == -1 ;No matching record found
  Message("dbFindRecord", "Record not found")
  exit
endif
gosub getfirst
while recordnum  < record
  gosub getnext
endwhile
return
;*******************************************************
;**  
;*******************************************************

:getfirst
prevrec=0
recordnum = -1 ;get the first item 
flag = 16
list = ""
  recordnum = dbGetNextItem(handle, recordnum, flag)
  flag = 2 ;variable record number
  if recordnum == -1 then 
  gosub getprev
  return ;Reached end of database
  endif
  if recordnum == @LAFFDBERROR
		 err = dbGetLastError()
	    Message("Error getting next record", err)
  endif
  gosub getfields
  return

;*******************************************************
;**  
;*******************************************************

:getlast
flag=32
  prevrec=recordnum
  recordnum = dbGetNextItem(handle, recordnum, flag)
  if recordnum == -1 then 
     recordnum = dbGetNextItem(handle, prevrec, 2)
     return
  endif ;Reached end of database
  if recordnum == @LAFFDBERROR
		 err = dbGetLastError()
	    Message("Error getting next record", err)
  endif
  gosub getfields
  return
 ;*******************************************************
;**  
;*******************************************************

 :getfields 
 for i=1 to count
  v%i% = dbGetRecordField(handle, recordnum, dbGetColumnName (handle, i), 2)
 ; message("v%i%", v%i%)
  if v%i% == @LAFFDBERROR
		 err = dbGetLastError()
	    Message("Error getting record field", err)
  endif

next
return
;*******************************************************
;**  
;*******************************************************
:saveit
;*****************************************************
;**   Saves Changes to database file 
;*****************************************************
ret = dbSave (handle, filename, "")
if ret == @LAFFDBERROR
	 err = dbGetLastError()
	 Message("Error Saving the database", err)

endif
;********************************************************
;**   Get Record Count
;********************************************************
reccnt = dbGetRecordCount(handle)
cnt=reccnt
return
;*******************************************************
;**  
;*******************************************************
:help
helptext="Browses a .csv file.  %@CRLF%First line is assumed to be a list of column names. Use 'Open' to select file."
ver=dbGetVersion()
m=StrCat("%@CRLF%                                LAFFDB Extender v. ", ver)
m=StrCat("%@CRLF%", m)
m=StrCat(m,"%@CRLF%          written by David R. Watson, david@davidrwatson.com")
helptext=StrCat(helptext,m)
Message("LAFFDB Table Browser", helptext)
return
;*******************************************************
;**  
;*******************************************************
:newtable

dbfile=currdir
for i = 1 to 10
  n%i%="~"
next

:newone
dbfile=currdir

MyDialogFormat=`WWWDLGED,5.0`

MyDialogCaption=`New Table`
MyDialogX=113
MyDialogY=58
MyDialogWidth=236
MyDialogHeight=210
MyDialogNumControls=25

MyDialog01=`24,32,36,DEFAULT,STATICTEXT,DEFAULT,"Column 1:"`
MyDialog02=`24,46,36,DEFAULT,STATICTEXT,DEFAULT,"Column 2:"`
MyDialog03=`6,14,36,DEFAULT,STATICTEXT,DEFAULT,"Table Name:"`
MyDialog04=`24,60,36,DEFAULT,STATICTEXT,DEFAULT,"Column 3:"`
MyDialog05=`24,74,36,DEFAULT,STATICTEXT,DEFAULT,"Column 4:"`
MyDialog06=`24,88,36,DEFAULT,STATICTEXT,DEFAULT,"Column 5:"`
MyDialog07=`24,102,36,DEFAULT,STATICTEXT,DEFAULT,"Column 6:"`
MyDialog08=`24,116,36,DEFAULT,STATICTEXT,DEFAULT,"Column 7:"`
MyDialog09=`24,130,36,DEFAULT,STATICTEXT,DEFAULT,"Column 8:"`
MyDialog10=`24,144,36,DEFAULT,STATICTEXT,DEFAULT,"Column 9:"`
MyDialog11=`24,158,36,DEFAULT,STATICTEXT,DEFAULT,"Column 10:"`
MyDialog12=`46,14,112,DEFAULT,EDITBOX,dbfile,"newtablename"`
MyDialog13=`62,32,98,DEFAULT,EDITBOX,n1,""`
MyDialog14=`62,46,98,DEFAULT,EDITBOX,n2,""`
MyDialog15=`62,60,98,DEFAULT,EDITBOX,n3,""`
MyDialog16=`62,74,98,DEFAULT,EDITBOX,n4,""`
MyDialog17=`62,88,98,DEFAULT,EDITBOX,n5,""`
MyDialog18=`62,102,98,DEFAULT,EDITBOX,n6,""`
MyDialog19=`62,116,98,DEFAULT,EDITBOX,n7,""`
MyDialog20=`62,130,98,DEFAULT,EDITBOX,n8,""`
MyDialog21=`62,144,98,DEFAULT,EDITBOX,n9,""`
MyDialog22=`62,158,98,DEFAULT,EDITBOX,n10,""`
MyDialog23=`36,184,38,DEFAULT,PUSHBUTTON,DEFAULT,"OK",1`
MyDialog24=`108,184,36,DEFAULT,PUSHBUTTON,DEFAULT,"Cancel",0`
MyDialog25=`162,14,64,DEFAULT,PUSHBUTTON,DEFAULT,"Change Directory",2`

ButtonPushed=Dialog("MyDialog")

if buttonpushed==2 then
;
	prompt="Select Working Directory LAFFDB Table"
	browseroot=""
	startdir=currdir
	confirmprompt="Are you sure?"
	flags=1|2
	currdir=AskDirectory(prompt, browseroot, startdir, confirmprompt, flags)
  ; Message("Directory Path selected was", currdir)
  DirChange(currdir)
	goto newone
endif


if FIleexist(dbfile) then
   message("Table File Exists -- Try New Name", dbfile)
	goto newone
endif

if strupper(fileextension(dbfile)) != "CSV" then
    m=Strcat("Incorrect File Name: ", dbfile)
    message( m, "Please Specify a .CSV file name")
	 goto newone
endif

numcols=0
while numcols < 10
   numcols=numcols+1
	if n%numcols%=="~" then
	   numcols=numcols-1
		break
	endif
endwhile
;message("Number of columns=", numcols)
bCreateOK = 1 ; if file does not exist, create an empty database.
model = 2     ; traditional
 ;numcols = 7   ; number of columns
format = 0
filename=dbfile
delimiter=","
optionstring = ""
handle = dbOpen (filename, bCreateOK, model, numcols, format, delimiter, optionstring)
if handle == @LAFFDBERROR
    err = dbGetLastError()
 Message("Error Opening the database specified", err)
	 exit
Endif
For columnnumber = 1 to numcols
   ret = dbNameColumn(handle, columnnumber, n%columnnumber%)
	if ret == @LAFFDBERROR
	   err = dbGetLastError()
	   Message("Error Setting record field", err)
   endif	
Next
gosub saveandclose
dbfile=""
handle=""
cnt=0
reccnt=0
for i = 1 to 10
  n%i%=""
next
goto top

:Cancel
dbfile=""
for i = 1 to 10
  n%i%=""
next
goto top

:saveandclose
;***************************************************
;**   Saves Changes to database file 
;***************************************************
ret = dbSave (handle, filename, "")

if ret == @LAFFDBERROR
	 err = dbGetLastError()
	 Message("Error Saving the database", err)
endif

;***************************************************
;**   Closes Database handle
;***************************************************
ret = dbClose(handle)
if ret == @LAFFDBERROR
	 err = dbGetLastError()

 Message("Error Closing the database", err)
endif
 return

Article ID:   W15055

File Created: 2002:09:05:13:49:20

Last Updated: 2002:09:05:13:49:20