Re: Broken sql string?

[Dave Page]

�
<BLOCKQUOTE dir=ltr 
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px solid; 
MARGIN-RIGHT: 0px">
  
  <FONT 
  face=Tahoma size=2>-----Original Message-----From: Mark A. Taff 
  [mailto:[EMAIL PROTECTED]] Sent: 19 February 2002 
  17:01To: Dave PageCc: 
  [EMAIL PROTECTED]: RE: Broken sql 
  string?
  
  <FONT face=Arial color=navy 
  size=2><SPAN 
  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">My 
  apologies for not sending this to the list originally. <SPAN 
  style="mso-spacerun: yes">�Allow me to elaborate.<SPAN 
  style="mso-spacerun: yes">� I am working on a `View Designer` 
  similar to the interface that SQL Server uses to create new views.<SPAN 
  style="mso-spacerun: yes">� 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).<SPAN 
  style="mso-spacerun: yes">� 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.
<SPAN 
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">�<SPAN 
class=087510617-19022002>No point reinventing the wheel. Steal away 
:-)
<BLOCKQUOTE dir=ltr 
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px solid; 
MARGIN-RIGHT: 0px">
  <FONT face=Arial color=navy 
  size=2><SPAN 
  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">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.
  <FONT face=Arial color=navy 
  size=2><SPAN 
  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">�<SPAN 
  class=EmailStyle17><SPAN 
  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">�
  <FONT face=Arial color=navy 
  size=2><SPAN 
  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">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.<SPAN 
  style="mso-spacerun: yes">� The HBX is coloring 
  properly.
  <FONT face=Arial color=navy 
  size=2><SPAN 
  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">�
<SPAN 
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt"><SPAN 
class=087510617-19022002>Ahh, I see.
<BLOCKQUOTE dir=ltr 
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px solid; 
MARGIN-RIGHT: 0px">
  <FONT face=Arial color=navy 
  size=2><SPAN 
  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">The 
  default text in the HBX is set to <SPAN 
  class=EmailStyle16><SPAN 
  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">"SELECT 
  *" & vbCRLF & "FROM" in the Form_Load event of my form.<SPAN 
  style="mso-spacerun: yes">� As we are dealing with queries here 
  and not arbitrary SQL, this seems an appropriate default value.<SPAN 
  style="mso-spacerun: yes">� 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.
  <SPAN 
  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">�
<SPAN 
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt"><SPAN 
class=087510617-19022002>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.
<BLOCKQUOTE dir=ltr 
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px solid; 
MARGIN-RIGHT: 0px">
  <SPAN 
  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">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?
  <SPAN 
  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">�
<SPAN 
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt"><SPAN 
class=087510617-19022002>Yes, agreed. I'll try to take a look 
tonight.
<SPAN 
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt"><SPAN 
class=087510617-19022002>�
<SPAN 
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt"><SPAN 
class=087510617-19022002>Cheers, Dave.