Set Exact

[audiopro]

I have this query which works OK unless the vars; website and loaded site are similar.

SELECT WEBSITE,FILENAME,LOCAL,REMOTE FROM FILES WHERE ALLTRIM(WEBSITE) = ALLTRIM(LOADEDSITE) ORDER BY FILENAME INTO ARRAY RESULTS

In the 'Website' field of the 'Files' table, I have similar entries for 2 distinct websites, one is 'webforms' and the other is 'webforms2u'.
The problem is, the files for both are selected when I search for 'webforms.
I have tried SET EXACT both on and off but it makes no difference - what am I doing wrong?


Keith

http://www.studiosoft.co.uk

 

[JRB-Bldr]

Have you tried:

WHERE ALLTRIM(WEBSITE) == ALLTRIM(LOADEDSITE)  && Note double equal sign

Also, depending on who and how the Website field was populated, you might also need to make it Not case-sensitive

ALLTRIM(UPPER(WEBSITE)) == ALLTRIM(UPPER(LOADEDSITE))

Good Luck,
JRB-Bldr

 

[vgulielmus]

As an alternative to ==, you may use SET ANSI ON. SET EXACT is ignored by SQL SELECT

http://msdn.microsoft.com/en-us/library/kf2c7kaz(v=vs.80).aspx

My respects,
Vilhelm-Ion Praisach
Resita, Romania

 

[Mike Lewis]

Keith,

SET EXACT does not apply to SQL statements. Instead, you should use SET ANSI.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Looks like a table for your FTP syncing.

Why keep it so flat? Have a websites table and store a websiteid in the files table, then determine the websiteid of the loadedsite as loadedsiteid and you'd also have no string comparison.

I'm not saying you can circumvent any string comparison in queries by normalizing them into a separate table, but as a side effect here this would solve that problem, too. Overall with normalized data you more often filter or join by ids, which have no problem with string comparison by vfp, ansi or any other standard or encoding or whatever else might effect the comparsion.

Bye, Olaf.

 

[craigber]

One other thing here that most people miss that could cause performance issues with a large table.

WHERE ALLTRIM(WEBSITE) = ALLTRIM(LOADEDSITE) will not optimized unless ALLTRIM(WebSite) is indexed, which is not advised because it results in variable length keys. A better solution is to index the table on WebSite, then use

WHERE WebSite == PADR(LoadedSite, LEN(WebSite))

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[audiopro]

Thanks for the replies.
SET ANSI was the answer.

Olaf, not sure I understand what you mean.
I have a WEBSITES table which contains a field for website name and fields for FTP log in details.
When I select a site for use in the program - var. LOADEDSITE is set to website name.
The search above pulls out the pages and other files from the FILES table.
I could set relations but this has worked OK until I created 2 websites with similar names.

Keith

http://www.studiosoft.co.uk

 

[Olaf Doschke]

Well, then you're using the website name as primary and foreign key. Using numeric keys will mean you would filter for a number, which hasn't got the string comparison problem.

Selecting a site (eg from a dropdown combobox) you'd see the website name, but select the primary key - an ID, a number, an integer, typically. You would then lookup that integer elsewhere, not the name. There is no problem with similar numbers, as there is with similar names. the where clause would be WHERE WEBSITEID = LOADEDSITEID, with both being integers, and there is no inexact comparison, then, which could bring down files from a seperate website into the result.

It's just a suggestion, as many times currently, about using surrogate keys rather than natural keys. So I'm just adding another advantage of surrogate keys.

Bye, Olaf.

 

[audiopro]

Thanks for the explanation - yes it makes perfect sense.
The reason I do it 'my way' is that I often manually edit the file list and it is easier referencing the website name rather than a number.
It all works well, now that I have got the ANSI thing set right.

Keith

http://www.studiosoft.co.uk