dbSort Issue

Question:

If setting dbGetEntireRecord flag to use fixed record numbers, the dbSort function does not work. It only sorts ascending. If setting dbGetEntireRecord flag to use variable record numbers, the dbSort function works - kind of! First, it increments the number of rows by 1 on each sort pass. It also has trouble grabbing the first row, or last row, depending on which sort method used.
SORT_CSV.WBT
Addextender("laffd34i.dll") ; ################################################################################### #DefineSubroutine UDS_Get_Row_Data() dbData="" rowCount= dbGetRecordCount(handle) message("","row count is %rowcount%") ; testing For n = 1 to rowCount rowData=dbGetEntireRecord(handle, n, 2); 1 = fixed record number, 2 = variable colData=UDS_Get_Column_Data() ; we have row data, now extract column entries dbData=StrCat(dbData, colData, @TAB) Next dbData=StrTrim(dbData) Return(dbData) #EndSubroutine ; ################################################################################### ; ################################################################################### #DefineSubroutine UDS_Get_Column_Data() colData="" For Z = 1 to count colItem=ItemExtract(Z, columnList, ",") colData=StrCat(colData,%colItem%, " ") Next Return (colData) #EndSubroutine ; ################################################################################### ; ################################################################################### #DefineSubroutine MyDialogCallbackProc(MyDialog_Handle,MyDialog_Message,MyDialog_ID,rsvd1,rsvd2) ;** DialogprocOptions Constants MSG_INIT=0 ; The one-time initialization MSG_BUTTONPUSHED=2 ; Pushbutton or Picturebutton MSG_ITEMSELECT=7 ; Itembox MSG_CLOSEVIA49=11 ; Close clicked (Enabled via Intcontrol 49) Switch MyDialog_Message Case MSG_INIT ; 0 DialogProcOptions(MyDialog_Handle,MSG_BUTTONPUSHED,@TRUE) DialogProcOptions(MyDialog_Handle,MSG_CLOSEVIA49,@TRUE) Break Case MSG_BUTTONPUSHED ; 2 btn_ctrl=DialogControlState(MyDialog_Handle, 0, 5, "") ; find out which button was pushed. If btn_ctrl==1 then sort = 1 ; Ascending If btn_ctrl==2 then sort = 0 ; Descending ;** Resort the db dbSort(handle, 1, sort) ; Sort on column 1 ;** Call UDS to rebuild data dbData=UDS_Get_Row_Data() ;** Refresh the sorted data into the ItemBox DialogControlSet(MyDialog_Handle, 3, 5, dbData) Break Case MSG_CLOSEVIA49 ; 11 dbClose(handle) Exit Endswitch ; MyDialog_Message Return(-2) ; Make sure this is -2 to prevent the dialog from closing #EndSubroutine ; End of Dialog Callback MyDialogCallbackProc ; ################################################################################### ; /////////////////////////////////////// ; Beginning of main script ; \\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ ; *** Establish if this script is running in debug (testing) mode or is a compiled exe state=rtStatus() If state==1 then home=dirhome() ; compiled EXE If state==10 then home=FilePath(IntControl(1004,0,0,0,0)) ; debug mode ;** add system menus to the dialog IntControl(49, 1, 99, 0, 0) dataFile=StrCat(home, "Sample.csv") ; <--- IMPORTANT! Put your source file here. ; ************************************************************************************ ; *** Open the database file. ; ************************************************************************************ bCreateOK = 0 ; file must exist. model = 2 ; traditional. numcols = -1 ; will use the number of columns in the first line of the text file, ; name the columns according to the values on the first line ; and will start reading actual data on the second line. format = 0 ; delimiter-based format. delimiter = "," ; the delimiter. optionstring = "" ; this is only used if columns are of fixed length. handle = dbOpen(dataFile, bCreateOK, model, numcols, format, delimiter, optionstring) ;** bind the columns columnList="" count = dbGetColumnCount(handle) For n = 1 to count name = dbGetColumnName (handle, n) ; get column names (from first row) name=StrReplace(name, " ", "_") ; can't handle spaces, and quotes doesn't help columnList=StrCat(columnList,name,",") ; add the name to the list name=StrReplace(name, " ", "_") ; can't handle spaces, and quotes doesn't help bindIt = dbBindCol(handle, n, name) ; bind the column Next len=StrLen(columnList) columnList=strSub(columnList, 1, len-1) ;** Call UDS to build a list from the db file dbData=UDS_Get_Row_Data() MyDialogFormat=`WWWDLGED,6.1` MyDialogCaption=`Sort on Column 1` MyDialogX=-01 MyDialogY=-01 MyDialogWidth=164 MyDialogHeight=120 MyDialogNumControls=003 MyDialogProcedure=`MyDialogCallbackProc` MyDialogFont=`DEFAULT` MyDialogTextColor=`DEFAULT` MyDialogBackground=`DEFAULT,DEFAULT` MyDialogConfig=0 MyDialog001=`017,009,050,012,PUSHBUTTON,DEFAULT,"Sort Ascending",1,1,32,DEFAULT,DEFAULT,DEFAULT` MyDialog002=`093,009,050,012,PUSHBUTTON,DEFAULT,"Sort Descending",2,2,0,DEFAULT,DEFAULT,DEFAULT` MyDialog003=`005,027,150,084,ITEMBOX,dbData,DEFAULT,DEFAULT,3,DEFAULT,DEFAULT,DEFAULT,DEFAULT` ButtonPushed=Dialog("MyDialog")
SAMPLE.CSV
CustID,LastName,FirstName,Address,City,State,Zip 1000,Flintstone,Fred,1230 Stone Road,BedRock,AZ,85000 1001,Rubble,Barney,4560 Pebble Street,BedRock,AZ,85000 1002,Flintstone,Wilma,1230 Stone Road,BedRock,AZ,85000 1003,Rubble,Betty,4560 Pebble Street,BedRock,AZ,85000 1004,Ball,Lucille,55 Love Lane,TelevisionCity,CA,90201 1005,Arnez,Desi,55 Love Lane,TelevisionCity,CA,90201 1006,Boop,Betty,1930 Boop-Boop-A-Doop Loop,Hollywood,CA,90068 1007,Dean,James,1960 Desire Street,Hollywood,CA,90068 1008,Monroe,Marilyn,1962 Norma Jean Ave.,Hollywood,CA,90068 1009,Marley,Bob,333 Reggae Road,St. Ann's Parish,Jamaica,10101

Answer:

The variable record numbers start at 0 and go sequentially. Thus it populates a 'normal' Array - first element is [0,0]

So getting row data would look like this:

rowCount= dbGetRecordCount(handle)
   For n = 0 to (rowCount - 1)
      rowData=dbGetEntireRecord(handle, n,  2) ;use variable records
      ..... 
  Next

BUT, the fixed record number approach uses 1 as the first element. Thus you'll need to change the For loop to:

   For n = 1 to rowCount
      rowData=dbGetEntireRecord(handle, n, 1) ;use fixed records
      .....                                                                              
  Next

The Fixed record number is a value that is used for a record in the database, and it will not change, even after a re-sort. This record number should be used if the program plans to hang onto the record number for a duration longer than the scope of the calling procedure. Fixed record numbers are not available for disk-based models.

The Variable record number is simply the index of the item in the array; it could change after any sort. This record number is useful when the program wishes to enumerate the database in sorted order; the code would simply use a FOR loop from 1 to the number of items in the list.

Note:
Whenever you are going to do an operation on the entire database, such as search or iterate, you should consider using variable record numbers, starting at 0. For example, if you have a database with 10 records, iterate from 0-9. This way you will get the records returned to you in the sort order you expect. Also, variable record numbers always start at 0 and go sequentially. Fixed record numbers can have gaps as records are deleted, and that puts the software into quite a quandary.

Fixed record numbers are handy when you want to access a particular record, and hang onto it for a duration of time where the record ordering of database could be modified. For example, if you want to call dbSort, dbDeleteRecord, dbCloneRecord, etc BETWEEN a dbGetEntireRecord and dbSetEntireRecord, then you would want to use a fixed record number so that you can be assured that the same database record is set as the one you get.

Article ID:   W16337

File Created: 2005:02:18:12:19:52

Last Updated: 2005:02:18:12:19:52

Database Search

TechHome

dbSort Issue

Question:

Answer: