SQL: ORDER BY clause is invalid.

[nmpetkov]

ℹ Computer information

• VFP version: VFP9 SP2
• GoFish version: 6.0 (last)
• Last GoFish version w/o bug (if available): bug is in everyone version I know

📝 Provide detailed reproduction steps (if any)

1. Start GoFish
2. Search

✔️ Expected result

See the result witout errors

❌ Actual result

Error:
SQL: ORDER BY clause is invalid.

I fix the error by finding the command in the gf_resultsform form:
Select *;
From (m.lcSearchEngineResultsAlias);
Into Cursor gf_temp_csr Readwrite;
Order By &lcOrderBy

and before it I put these 2 lines:
lcOrderBy = STRTRAN(m.lcOrderBy, 'PROCCODE ASC,', '', 1, 1)
lcOrderBy = STRTRAN(m.lcOrderBy, 'PROCCODE DESC,', '', 1, 1)
`
The reason for the error is that VFP does not allow sorting by memo field. I guess there is a better way to eliminate the problem, but this works for me anyway.

Hope this helps to fix this nasty bug.

📷 Screenshots

[lscheffler]

Could you give me the method?

Try to right click the grid header, there is set order to default or the like, this should set the order to normal.

Do you have ordered by this column?

I need to think about to filter this out / catch the error.

[nmpetkov]

Method is:
fillsearchresultscursor
Just search:
Into Cursor gf_temp_csr Readwrite

I guess this is some kind of holdover from past versions, somewhere there is an arrangement that is no longer relevant.
When the error appears, there is no way to continue without putting the mentioned 2 lines.
I guess for most users the error doesn't appear.
I should say that I have also tried deleting the files related to GoFish in:
...AppData\Roaming\Microsoft\Visual FoxPro

[lscheffler]

File GF_Results_Form_Settings.xml, set all values of <Setting Name="cSort* to (Default) like

		<Setting Name="cSortOrder" Type="System.String" Scope="User">
			<Value Profile="(Default)"></Value>
		</Setting>

this should reset sort order. There are 6 settings to watch.

[nmpetkov]

Now the problem is completely clear. The cardinal solution seems to be after GoFish reads the sortorders before applying them to validate that they are among those it supports.

[Jimrnelson]

You don't have to manually change the GF settings file for this.

Just use the right-click context menu.

[nmpetkov]

@Jimrnelson If the error occurs, this menu cannot be accessed at all.

[Jimrnelson]

I believe that is not true; you can ignore the error (possibly multiply times, as I recall) until the grid comes up. Matt added that menu option specifically to handle the case where the sort order got muddled.

…

On Sat, Mar 11, 2023 at 5:00 AM N. Petkov ***@***.***> wrote: @Jimrnelson <https://github.com/Jimrnelson> If the error occurs, this menu cannot be accessed at all. — Reply to this email directly, view it on GitHub <#57 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ATLLFR6BABXUKJ4WE7ZA463W3RSN3ANCNFSM6AAAAAAVXK4CTE> . You are receiving this because you were mentioned.Message ID: ***@***.***>

-- Jim Nelson (805) 498-9195 (preferred) (720) 837-3536 (cell)

[lscheffler]

@Jimrnelson I think that's what the option is for too. Only ignoring will bring GoFish into an odd state. it will not create cursors later used, this might be a bit of havoc. So, I rather would alter the settings file.

I need to catch the error there but, I'm on a problem with files duplicated on migrating first.

[Jimrnelson]

The workaround for this is to reset the sort order, close and then re-open GF, then you're OK. Catching the error is nice, but then the error persists every time until you reset it.

…

On Sat, Mar 11, 2023 at 5:08 AM Lutz Scheffler ***@***.***> wrote: @Jimrnelson <https://github.com/Jimrnelson> I think that's what the option is for too. Only ignoring will bring GoFish into an odd state. it will not create cursors later used, this might be a bit of havoc. So, I rather would alter the settings file. I need to catch the error there but, I'm on a problem with files duplicated on migrating first. — Reply to this email directly, view it on GitHub <#57 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ATLLFR7PMZPBTQ7P5MIZVIDW3RTNLANCNFSM6AAAAAAVXK4CTE> . You are receiving this because you were mentioned.Message ID: ***@***.***>

-- Jim Nelson (805) 498-9195 (preferred) (720) 837-3536 (cell)

[lscheffler]

Catch the error, and set the sort fields (properties) to default if the error occurs, would be my idea. Why bothering the user. I know something is wrong anyway. ;)
And prohibit sorting for memo columns in the UI, so this will only happen for stuff inherited.

[Jimrnelson]

I would not prohibit searching on memo fields. The user does not know which fields are Memo fields and would be annoyed if sorting on them did nothing. Instead I suggest you use Padr(Field, 100).

…

On Sat, Mar 11, 2023 at 5:25 AM Lutz Scheffler ***@***.***> wrote: Catch the error, and set the sort fields (properties) to default if the error occurs, would be my idea. Why bothering the user. I know something is wrong anyway. ;) And prohibit sorting for memo columns in the UI, so this will only happen for stuff inherited. — Reply to this email directly, view it on GitHub <#57 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ATLLFRYJUD3MXVF7YI6SUO3W3RVNFANCNFSM6AAAAAAVXK4CTE> . You are receiving this because you were mentioned.Message ID: ***@***.***>

-- Jim Nelson (805) 498-9195 (preferred) (720) 837-3536 (cell)

[lscheffler]

Not searching, sorting. Just not offering (disabling ...) the option to sort by a certain column on right click on this columns header will do. You can't do, so why offer (If the memo is in the grid at all, I skipped a lot of fields from view)

[lscheffler]

@Jimrnelson I have checked the options, all memo and a lot of the control fields will be invisible and unreachable by default. You can turn them on, see here

[Jimrnelson]

Not searching, sorting. Just not offering (disabling ...) the option to sort by a certain column on right click on this columns header will do. You can't do, so why offer (If the memo is in the grid at all, I skipped a lot of fields from view)

Well, you can sort on any column by clicking the column header, so skipping an option in the right click menu doesn't mean much.

[lscheffler]

It's not that tricky, isn't it. If I can LEFT() I can just ignore.

But let's talk about this. The memos are

• MatchLine this we have as TrimmedMatchLine in char
• Code The whole code snippet.
• ProcCode The procedural code snippet.
• Statement The whole statement

Do you really think there is value in sorting by this? For example ProcCode or ProcCode would mean to sort for the first whatever chars of a distinct prg or procedure, That's what FilePath (FileName is odd, because of files with same name in different folders, got some headache last week on this by default sort) and MethodName are good for.