Solved

VFP 8 vs. VFP 9 ENGINEBEHAVIOR

Posted on 2013-11-27
2
628 Views
Last Modified: 2013-11-27
I am having a problem where a query produces different results based on whether it's run in VFP 8 or VFP 9. Here is the query :

SELECT DISTINCT gage.gageno, gage.loc, gage.lastused, gage.duedate, gage.outofserv, gage.gagedesc, gage.customer, gagepart.partno
FROM gage LEFT JOIN gagepart ON ALLTRIM(gage.gageno) = ALLTRIM(gagepart.gageno)
WHERE LEFT(gage.gageno,6) == '13-01-' INTO CURSOR fred

The following are settings in both versions :

ANSI = OFF
EXACT = OFF
ENGINEBEHAVIOR = 70

When run in 9, the query includes a record where the GAGEPART table contains a record with a blank gageno field. When run in 8, it does not. I know I could rectify this by replacing the = in the JOIN clause with a ==, but I'm looking for the reason why this happens. Is there some other setting I need to look at ?

Any insight here would be appreciated.
0
Comment
Question by:AlanArons
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 42

Accepted Solution

by:
pcelba earned 500 total points
ID: 39681594
SET EXACT is not important here. Other settings which could affect the result are: SET COLLATE, CPCURRENT(), CPDBF(), and maybe SET DELETED.

The row having empty gagepart.gageno should be on output in all cases because SET ANSI OFF forces the string comparison to the length of the shorter of both strings - so if one of the strings contains just spaces the ALLTRIM converts it to zero length string and the JOIN condition must be True because you are comparing two zero length strings.

I cannot test this behavior in VFP 8 just now but I am sure VFP 9 has many previous bugs  fixed... so let say this was bug in SQL engine prior to VFP 9 (if this is not caused by Collation or Code Page settings).

SET ANSI ON removes blank gagepart.gageno records from output.
0
 

Author Closing Comment

by:AlanArons
ID: 39681689
I was afraid that might be the case.   It occurred to me that the result in 8 given the settings should be incorrect.   I was trying to avoid having to go through all of our code and try and find where this might apply.   Looks like we should turn ANSI on in the programs while still running in 8 (I can do it in the base class for the form.)  and see if it affects anything.  Then when we go to 9 it shouldn't have an effect.  

Thanks for your help.
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question