Re: Broken sql string?

My apologies for not sending this to the list originally. Allow me to
elaborate. I am working on a `View Designer` similar to the interface that
SQL Server uses to create new views. There are a great many unknowns right
now, and I need to get it working enough to find out what works and what won
’t (the relationship diagram for example, is a big mystery right now). So,
to speed up this process, I am only writing the code that I have to; if I
can recycle code from an existing section, I am. So one piece of code I
grabbed was the Display sub from frmSQLOutput.

The code in the sub that parses the query to find the FROM clause of the
statement the user entered in the HBX is looking for “ FROM “ (with leading
and trailing space). So if there is a CRLF immediately before the `F`
instead of a space, the routine fails to find the `FROM` clause, and hence
fails to find anything else, like the table involved.

***CODE***
'Find the FROM clause. If it is inside single quotes then we
'should try again - it won't in doubles as there are no spaces
'in doubles anymore.
iStart = 0
bFlag = False
bInQuotes = False
While bFlag = False
iStart = InStr(iStart + 1, UCase(szQuery), " FROM ")
If iStart = 0 Then 'No FROMs found
bFlag = True
Else 'Found a FROM, check it's not in quotes
For X = 1 To iStart
If Mid(szQuery, X, 1) = "'" Then bInQuotes = Not bInQuotes
Next X
If Not bInQuotes Then bFlag = True
End If
Wend
***END***

The problem isn’t in the HBX itself, as far as I can tell, but rather in how
we parse the SQL string in the process of executing it. The HBX is coloring
properly.

The default text in the HBX is set to "SELECT *" & vbCRLF & "FROM” in the
Form_Load event of my form. As we are dealing with queries here and not
arbitrary SQL, this seems an appropriate default value. The only instances
I can think of when you wouldn’t need a FROM clause is when you are
selecting a function or a static value. This is where the CRLF is coming
from.

If changing the user’s input is not an option, then it seems to me maybe we
ought to rewrite the code to find the `FROM` clause even if there isn’t a
space before it. What do you think?

Regards,

Mark

-----Original Message-----
From: Dave Page [mailto:dpage(at)vale-housing(dot)co(dot)uk]
Sent: Tuesday, February 19, 2002 4:38 AM
To: 'Mark A. Taff'
Cc: 'pgadmin-hackers(at)postgresql(dot)org'
Subject: RE: Broken sql string?

-----Original Message-----
From: Mark A. Taff [mailto:mark(at)libertycreek(dot)net]
Sent: 19 February 2002 11:26
To: Dave Page
Subject: Broken sql string?
Dave,
Query sql strings taken from my new sql hbx cause the display sub
(from the sql output form, now cannibalized) to break if the string contains
vbCRLF. The string in question might be "SELECT *" & vbCRLF & "FROM
addresses"

The code searches for " FROM " to locate the from clause. Should it be
rewritten to allow for CRLF before any of the tokens, or do we need to first
parse the query string for CRLF and replace them with single spaces?

Hi Mark,

Please post this sort of message to the pgadmin-hackers list so everyone
knows what's going on :)

I guess you're talking about the behaviour of the HBX which won't colour the
FROM if it follows a crlf (sorry, the talk of cannibalized frmSQLOutput
threw me a bit)? If so, then yes, this is a bug. The problem with the HBX
though, is that *many* hours of work and testing have been put into it to
make it work as well as it does. We did have code that coloured better,
however it's very slow. This was the major problem - if you pasted in 20K of
SQL, then you had to wait quite some time for it to colour.

In answer to you question though, no, we should not modify the user's input
in any way. We used to replace crlf with spaces before sending queries to
the server, which was all fine and dandy until someone complained that the
PL/Perl function they wrote wouldn't work properly - because it had lost all
it's crlfs!!! If you can fix the HBX, or write a better version though, that
would be great.

Regards, Dave.