CODEWITHSUNDEEP
visual-foxpro
Lorem Ipsum
Lorem Ipsum

Reputation: 4524

VFP8: Check if query returned a result

I am importing a bunch of tables and have found data errors in some of them. These errors were introduced when the tables were created, years ago. I want to create a simple alert to notify me that I should manually check the table.

The following works, but it pops up the query results, which I don't want. 

procedure checkForBadRecord
  select * ;
  from table_x ;
  where field_x = 'thing used to determine it's bad'

  if _tally > 0 then 
    messagebox("Check the table for errors!")
  endif
endproc

Is there a way to check if a table has any rows that meet a condition without showing the actual rows?

I am using Visual FoxPro 8.

Upvotes: 1

Views: 821

Answers (2)

Cetin Basoz
Cetin Basoz

Reputation: 23797

To prevent the result to be shown just specify a target for the result. "into array" or "into cursor" would do.

According to your current code, you are not interested with the row(s) returned so you could simply get the count instead (you also had typo in the code). ie:

procedure checkForBadRecord
  local array laBadCount[1]
  select count(*) ;
  from table_x ;
  where field_x = "thing used to determine it's bad" ;
  into array laBadCount
  use in (select('table_x'))

  if laBadCount[1] > 0 then 
    messagebox("Check the table for errors!")
  endif
endproc

Probably instead of writing such a procedure you would want to write this procedure for a more generic use:

  if checkForBadRecord('table_x', 'field_x', "thing used to determine it's bad") 
    messagebox("Check the table table_x for errors!")
  endif



procedure checkForBadRecord(tcTableName, tcFieldToCheck, tuValueToCheck)
  local array laBadCount[1]
  select count(*) ;
  from &tcTableName ;
  where &tcFieldToCheck = m.tuValueToCheck ;
  into array laBadCount
  use in (select(m.tcTableName))

  return laBadCount[1] > 0
endproc

Note: You could use "To Screen" as well to suppress the results and get the count via _Tally. ie:

procedure checkForBadRecord
  set console OFF
  select * ;
  from table_x ;
  where field_x = "thing used to determine it's bad" ;
  to SCREEN
  set console ON
  use in (select('table_x'))

  if _Tally > 0 then 
    messagebox("Check the table for errors!")
  endif
endproc

Upvotes: 1

Steve
Steve

Reputation: 506

You could add "INTO ARRAY dummyCursorName" after there WHERE clause:

   select * ;
      from table_x ;
      where field_x = 'thing used to determine it's bad' ;
      INTO ARRAY dummyCursorName

_TALLY will still report the statistic and no annoying browse window to deal with.

Upvotes: 3

Related Questions