Re: Broken sql string?

-----Original Message-----
From: Mark A. Taff [mailto:mark(at)libertycreek(dot)net]
Sent: 19 February 2002 17:01
To: Dave Page
Cc: pgadmin-hackers(at)postgresql(dot)org
Subject: 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.
No point reinventing the wheel. Steal away :-)
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.

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.

Ahh, I see.
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.

I would be inclined to leave it blank. As the default wouldn't be valid SQL
anyway, it seems pointless having it. I would suggest just building the
string once the required elements have been selected.
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?

Yes, agreed. I'll try to take a look tonight.

Cheers, Dave.