VFP 9 Odd String Search Problem

lcLookForProd = "Product #"

lnProducts_ID = pidu.product_id
*!*            lnProducts_ID = 59569

If I run this with a Product ID value acquired from a table lookup (pidu), the statement below doesnt work.
Yet if I manually assign the exact same number without the lookup it does work.
I have checked the value returned from the db query and it is numeric.
      
IF VARTYPE(lnProducts_ID) = "N"
      WAIT WINDOW "its numeric"
ENDIF


IF ("Product #" $ lcDescr) : works with both Product ID pulled from db and assigned number.

IF (lcLookForProd $ lcDescr) : this works when I manually assign the number, but not when it's pulled from the db

And none of the below work in either case. Can anybody tell me why? I really would like to be able to use a variable, instead of "Product #".

IF ((lcLookForProd) $ lcDescr)
IF ('lcLookForProd' $ lcDescr)
IF (('lcLookForProd') $ lcDescr)
formadmirerAsked:
Who is Participating?
 
pcelbaConnect With a Mentor Commented:
Interesting and unbelievable... :-)

But lets try something:
These two lines:
            lcQuery="SELECT product_description FROM product " + ;
            "WHERE products_id = ?lcProducts_ID"  
are suspicious.
1) products_id  should not be in product table (if it is the same table as the one from the first query).  The first query  asks for product_id so one of these two column names is not correct.

2) It is not necessary to convert number into string for the second query. You may simply pass the numeric variable as a parameter:
            lcQuery="SELECT product_description FROM product " + ;
            "WHERE product_id = ?lnProducts_ID"  
If you would like to use string then concatenate it to the query string directly:
            lcQuery="SELECT product_description FROM product " + ;
            "WHERE product_id = " + lcProducts_ID  

3) To test the query result by the output cursor existence is not good practice! You should test the SQLEXEC() function result:
            lnResult = SQLEXEC(pnConnection,lcQuery,'DESC')
            IF lnResult < 0
              * We are in troubles
              * store the detail error info into an array:
              = AERROR(laSQLError)
              * display the error
              DISPLAY MEMORY LIKE laSQLError   && possible in VFP IDE
              LIST MEMORY LIKE laSQLError TO FILE c:\temp\SQLerror.err ADDITIVE
              * You should use more sophisticated error reporting !
            ENDIF
0
 
pcelbaCommented:
It is hard to answer because above lines of code are separated from some bigger code snippet and we don't know all necessary dependences...

You should also distinguish between "Ite does not work" and "It causes an error".
If "it does not work" then you should explain how it should behave, e.g. "it returns .F. but I would expect .T."
If "it causes an error" then you have to write the error number and/or error message.

You did not even disclose the value in lcDescr...

Following commands must work under all obvious circumstances:
lcLookForProd = "Product #"
lcDescr = "Some longer text containing the Product # string"
IF (m.lcLookForProd $ m.lcDescr)
  ? "1. Correct result of the above expression"
ELSE
  ? "1. Unexpected program continuation"
ENDIF
IF ("Product #" $ m.lcDescr)
  ? "2. Correct result of the above expression"
ELSE
  ? "2. Unexpected program continuation"
ENDIF

Open in new window

Note "m." prefixes in above code which tell to interpreter "use memory variables if the field of the same name exists".

And now what can I say to your questions:
"IF ("Product #" $ lcDescr) : works with both Product ID pulled from db and assigned number." ... I don't see any relation between Product ID and this command.

"IF (lcLookForProd $ lcDescr) : this works when I manually assign the number, but not when it's pulled from the db" ... Again, the number assignement is not visible here.

IF ((lcLookForProd) $ lcDescr)  ... This will "work" if both variables(?) will contain a text string. The result will depend on the string value.
IF ('lcLookForProd' $ lcDescr)  ... This command will search the "lcLookForProd" string inside the lcDescr variable or inside the lcDescr field in a table open in current workarea
IF (('lcLookForProd') $ lcDescr) ... same behavior as the previous command (extra parentheses are not necessary).

It will be better if you describe what you want to achieve and we may help to define the right FoxPro command.

Another option is to place SET STEP ON command to your code and then you may test various expressions and commands in VFP Command windows or in the Debug wwindow. It is fast and easy and you'll better understand what values are you working with.
0
 
jharkinsCommented:
> IF (lcLookForProd $ lcDescr) : this works when I manually assign the number, but not when it's pulled from the db

As pcelba mentioned, this has absolutely nothing to do with a numeric value, either from a lookup or manual assignment. Both are string variables, correct?

If not, there's your first clue. I suspect you have a variable assigned a string sometimes and a numeric other times or something of that ilk.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Olaf DoschkeSoftware DeveloperCommented:
From what you show, the lnProducts_ID is not used at all. So what do you expect?

And if you want to add lnProducts_ID to lcLookForProd, you need to convert it to a string, you can't add a number to a string.

Or another question: Your expression looks for lcLookForProd in lcDescr. How is lnProducts_ID involved in it?

What error do you get?

Bye, Olaf.
0
 
formadmirerAuthor Commented:
Sorry, I was trying to make it short as I can't expect you to weed through all my code to find the problem. But I realize now that without it you can't possibly see what's going on.

Here's the code and the problem:
*** Note: I distinguish between a character variable and a numeric variable by prefixing the character with 'lc' and the numeric with 'ln'

lcLookForProd = "Product #"

lcQuery="SELECT product_id FROM product"
SQLEXEC(pnConnection,lcQuery,'PIDU')
IF !USED('PIDU')
	DO SQLQueryError IN error_messages WITH lcQuery
ENDIF
IF RECCOUNT('PIDU') > 0 
	SELECT ('PIDU')
	SCAN
	llProdURL = .F.
	
		lnProducts_ID = pidu.product_id && this returns the only product id in the table, 59569.
		
		&& When run either the line directly above this or directly below this is commented out.
		
		lnProducts_ID = 59569 && the same number but directly assigned for testing purposes.
		
	lcProducts_Id = ALLTRIM(STR(lnProducts_ID))

		IF VARTYPE(lnProducts_ID) = "N"
			WAIT WINDOW "its numeric"
			&& fires in both cases showing the value is numeric
		ENDIF
		WAIT WINDOW "the value is " + lcProducts_ID && both show the correct '59569'
		
		lcQuery="SELECT product_description FROM product " + ;
		"WHERE products_id = ?lcProducts_ID"  
		SQLEXEC(pnConnection,lcQuery,'DESC')
		IF !USED('DESC')
			DO SQLQueryError IN error_messages WITH lcQuery
		ENDIF
		
			WAIT WINDOW "reccount desc = " + lcProducts_ID + "-" + ALLTRIM(STR(RECCOUNT('DESC'))) && both return '1'
		
		IF RECCOUNT('DESC') > 0 
		
			lcDescr = desc.product_description
			lcDescr = ALLTRIM(m.lcDescr)
			IF USED('DESC')
				USE IN ('DESC')
			ENDIF
			
				WAIT WINDOW "lcdescr = " + SUBSTR(m.lcDescr,1,165) && both display the exact same content in the wait window. The content includes 'Product #'
			
			IF (m.lcLookForProd $ m.lcDescr)
				llProdURL = .T.
				WAIT WINDOW "1- llprodurl = T"
				&& directly assigned number returns .T.
				&& same exact number pulled from db returns .F. THIS IS THE PROBLEM.
			ELSE
				WAIT WINDOW "1- llprodurl = F"
			ENDIF

Open in new window


The absolutely only difference is that in one instance the value of '59569' is pulled from a database, and in the other I directly assign it to the variable. Yet the one pulled from the db returns .F. in the last statement, while the one directly assigned returns .T. as it should.

Hopefully this is clearer and you can see my dilemma.
0
 
formadmirerAuthor Commented:
Just thought I'd try a little more comparison on lnProducts_ID, so I did:

lcQuery="SELECT product_id FROM product"
SQLEXEC(pnConnection,lcQuery,'PIDU')
IF !USED('PIDU')
	DO SQLQueryError IN error_messages WITH lcQuery
ENDIF
IF RECCOUNT('PIDU') > 0 
	SELECT ('PIDU')
	SCAN
	llProdURL = .F.
	
		lnProducts_ID1 = pidu.product_id && this returns the only product id in the table, 59569.
		
	&& both are uncommented for this test
		
		lnProducts_ID2 = 59569 && the same number but directly assigned for testing purposes.
		
		
	lcProducts_Id1 = ALLTRIM(STR(lnProducts_ID1))
	lcProducts_Id2 = ALLTRIM(STR(lnProducts_ID2))

	IF lnProducts_ID1 == lnProducts_ID2
		WAIT WINDOW "they're equal numerically"
		&& both fire this
	ENDIF
	IF lcProducts_ID1 == lcProducts_ID2
		WAIT WINDOW "they're equal as characters"
		&& both fire this
	ENDIF

Open in new window


In every way I know how to test they are exactly the same, yet this is the one and only difference and must ultimately be the cause of why the
IF (m.lcLookForProd $ m.lcDescr)
returns .F. when it should return .T.
0
 
Olaf DoschkeConnect With a Mentor Software DeveloperCommented:
What error do you get? And at what line?

Besides this, pavel already pointed you towards some errors I also spotted, eg about "?lcProducts_ID" If you use ?variable in SQL you send to SQL Server, the variable type must be the same as the field you compare to, not string!

eg
lnUserID=1
SQLEXEC(lnH, "Select name from users where userid = ?lnUserID","curUser")

You'd only need to convert lnUserID to lcUserID = ALLTRIM(STR(lnUserID)), if you'd NOT use a parameterized query, but would want to insert the number into the query string like this:

SQLEXEC(lnH, "Select name from users where userid = "+lcUserID,"curUser")

SQL Parameterisation is meant to make it easy, not hard. And what's easier than being able to use the original data type? Also it's avoiding problems you can get, if you think wrong:

lcName="Olaf"
SQLEXEC(lnH, "Select userid from users where name = "+lcName,"curUser")

Why is that not working? Think about it.

The solution is as easy, as with the userID:
SQLEXEC(lnH, "Select userid from users where name = ?lcName","curUser")

This works.

Bye, Olaf.
0
 
formadmirerAuthor Commented:
pcelba - Thanks.
You are correct about the query. I accidentally altered the table name from product_description to product in the second query only on paste here though. It is correct in the program and the product_id field in the second table is in fact products_id (don't ask me why, I didn't design it).

In the program the query is correct and the testing showes that the values are exactly the same.

I also attempted the query using lnProducts_ID as opposed to lcProducts_ID as you pointed out in (2) above but with the exact same results.

One more funny bit to add to the puzzle though. If I substitute "Product #" for every occurrence of lcLookForProd, then it returns .T. for both as it should.
0
 
formadmirerAuthor Commented:
I've narrowed it down to it's simplest form. I pulled out anything unnecessary along with the second mysql lookup and replaced it in this code with the actual sting value from the db assigned to lcDescr.

lcLookForProd = "Product #"

lcQuery="SELECT product_id FROM product"
SQLEXEC(pnConnection,lcQuery,'PIDU')
IF !USED('PIDU')
	DO SQLQueryError IN error_messages WITH lcQuery
ENDIF
IF RECCOUNT('PIDU') > 0 
	SELECT ('PIDU')
	SCAN
	llProdURL = .F.
	lnProducts_ID = pidu.product_id
*!*		lnProducts_ID = 59569
				
	lcProducts_Id = ALLTRIM(STR(lnProducts_ID))
		
	lcDescr = "Terminates Unused F Ports On Splitters, Amplifiers, Combiners, etc. May Help To Reduce Signal Interference. Also great with Product #59563 and Category #10385."
			
	IF (lcLookForProd $ lcDescr)
		llProdURL = .T.
		IF lcProducts_ID = "59569"
			WAIT WINDOW "llprodurl = T"
		ELSE
			WAIT WINDOW "llprodurl = F"
		ENDIF
	ENDIF
	
		
		IF lcProducts_ID != "59569"
			LOOP
		ENDIF
		SELECT ('PIDU')
		
ENDSCAN
ENDIF

Open in new window


The results are the same and still make no sense. As before I swap between pulling lnProducts_ID from the db and directly assigning it.

IF (lcLookForProd $ lcDescr)
Returns .T. when directly assigned.
Returns .F. when number is pulled from db.
0
 
pcelbaCommented:
* If:
IF ("Product #" $ m.lcDescr)  && returns .T.
* BUT:
lcLookForProd = "Product #"
IF (m.lcLookForProd $ m.lcDescr)  && returns .F.

*then you have to compare:
IF (m.lcLookForProd == "Product #")

The lcLookForProd variable could be overwritten or it can contain hard space instead of space etc. Everything is possible today... but I have never seen string modified this way without the appropriate command in VFP program.

Do you use this variable on some other place in your code?
0
 
pcelbaCommented:
IF (lcLookForProd $ lcDescr)
Returns .T. when directly assigned.
Returns .F. when number is pulled from db.

How do you recognize  .F.  in your code?  Did you see the WAIT WINDOW "llprodurl = F" ?
How many records is in the PIDU cursor? Did you check the  pidu.product_id  value?

Are you testing in VFP development version?  SET STEP ON is your best friend...
0
 
formadmirerAuthor Commented:
I trashed the whole thing, re-wrote it incorporating some query suggestions by pcelba and a few other suggestions here and now it works.

I'm not sure why and as much as I'd like to know, I have to move on.

I appreciate all the help here and I'm on to the next step which I'm likely to post a question about here shortly.
0
 
formadmirerAuthor Commented:
As always thanks to all for the help
0
 
Olaf DoschkeSoftware DeveloperCommented:
Ok, problem solved by rewrite...

But you don't see the wood for the trees.

You say: As before I swap between pulling lnProducts_ID from the db and directly assigning it.

It has nothing to do with (lcLookForProd $ lcDescr), has it? How is the numeric value involved in that statement at all?

Just one thought: Doing (lcLookFor $ cDescr) in a WHERE clause or FOR clause does check this for all records. Just doing ? (lcLookFor $ lcDescr) at the command line does this for the current record only, and if you' at EOF you get .F., because at EOF every field is taken as empty value, eg you check an empty cDescr field.

You bark up the wrong tree. Do you have an error handler telling you error and line of error at all?

Bye, Olaf.
0
All Courses

From novice to tech pro — start learning today.