"Select" is different from "browse"?

"Select" is different from "browse"?

• Ashneet

  Thanks,

  I see, then how do I use select sql to insert 2 new records into another cursor

  
  
  

• Justin-M

  Thanks both of you, but I don't think it has a problem with "b" variable, 'coz if I use "XK1063" string instead of b I will get the same result.

  SELECT * from curlcDetailAlias WHERE refno="XK1063"
  or

  BROWSE LAST FOR refno="XK1063"

  Some new records what I haven't saved into a table don't appear in the result of select command but appear in the browse one.

  And if I "select * from curlcDetailAlias " I also get the result without these new records (refno="XK1063").

  The result is always correct with using "Browse last"

  
  
  

• MarissaM

  There is a subtle difference in your code. I assume b is a memory variable (not clear and would lead to unpredicted results when used without m. prefix).

  select * from curDetailAlias where refno == m.b ;
  order by ItemID into cursor curBangDoiTru

  Here you're telling VFP to return records where retno is exactly equal to m.b (regradless of trailing spaces either in retno or m.b might have). Select SQL uses ANSI and thus == means both sides of equation would be the same size. IOW:

  refno m.b
  -------- --------
  John___ matches "John_________" "John" "John___" etc where _ is a placeholder for space character.
  Doesnt match "Johnson"

  Note that left and right of equation might have any number of trailing spaces but still they match.
  

  Browse last for ItemID = m.b

  Unlike SQL, xbase is dependant on "set exact" setting as opposed to "set ansi". With set exact things are a bit different and you should be sure yourslef that trailing space count also matches. IOW:

  "John__" only matches to "John__"

  If set exact is off (default) then:

  Browse last for ItemID = m.b

  means any values that start with m.b ( Johnson matches to J,John,Johnson etc).

  Browse last for ItemID == m.b && or set exact on

  means "Johnson_" only matches to "Johnson_".

  Also note that in SQL I didn't use alltrim(). For 2 reasons:

  1) It's unlikely an index with alltrim() would exist (a useless function for indexes IMHO). When it doesn't that wouldn't use Rushmore optimization.

  2) Since ANSI comparison with == implicitly makes both sides of equation same length then no need to trim trailing spaces. It might be used on purpose to trim from left and in that case would be meaningfull (but then the design and storage of data is questionable - unfortunately something even VFP sample data has).

  Hope taht makes it clear.

  PS: just using b without m. prefix is very dangereous. ie: With testdata!customer:

  use customer
  company = cust_id
  browse for cust_id = company

  and 2nd important reason is perfromance especially in tight loops.

  
  

• cpurick

  Hi,
  As Cetin said, check with Set Exact On or use "==" instead of "=" in the command.

  Regards,
  Markish

  
  
  

• Burgz

  As said in previous messages there are differences in ANSI and EXACT comparison. Now that you tell the problem you are obeserving another difference between select - sql and xbase browse.

  Select SQL works with records that are committed to disk only (except "with buffering" clause in VFP9). xbase commands on the other hand work with records that are on the disk + buffered. In other words:

  use customer

  cursorsetprop("Buffering",5, "customer")

  insert into customer (cust_id) values ('xxxxx')

  insert into customer (cust_id) values ('yyyyy')

  update customer set company = "My Company" where cust_id = "ALFKI"

  select * from customer && you don't see 2 new records and 1 update

  browse && you see new records and update

  
  

• bryanedds

  I didn't say check with "set exact on" or "==". I guess he already had that setting. I pointed out the differences.
  

• ErickBikeman

  I don't understand why you don't buffer the table itself or use plain insert w/o select. Anyway you might have a reason to do so. Here is a sample that uses (buffering = .t.) - supported in VFP9 and up:

  SET MULTILOCKS ON
  SELECT * FROM customer INTO CURSOR xx readwrite
  CURSORSETPROP("Buffering",5,'xx')
  INSERT INTO xx (cust_id,company) VALUES ('xxxxx','New company1')
  INSERT INTO xx (cust_id,company) VALUES ('yyyyy','New company2')
  
  INSERT INTO customer ;
   SELECT t1.* ;
   FROM xx t1 WITH (buffering = .t.) ;
   LEFT JOIN xx t2 ON t1.cust_id = t2.cust_id ;
   WHERE t2.cust_id is null
  
  SELECT customer
  BROWSE
  

  
  
• "Select" is different from "browse"?