Jump to content


Photo
- - - - -

Parsing SQLServer SQL/ENDSQL on v6.2


4 replies to this topic

#1 Marlon

Marlon

    Member

  • Members
  • PipPip
  • 29 posts
  • Gender:Male

Posted 17 June 2010 - 03:00 PM

There's a bug in the parser for SQL/ENDSQL in 6.2.41 running on Windows against SQLServer.

This will fall over with an SQL error

SQL
SELECT * FROM <TABLE>
WHERE :$ABC = 'Y'
OR
:$ABC = 'X'
ENDSQL

whereas this won't:

SQL
SELECT * FROM <TABLE>
WHERE :$ABC = 'Y'
OR
(:$ABC = 'X')
ENDSQL

and neither will this:

SQL
SELECT * FROM <TABLE>
WHERE :$ABC = 'Y'
OR :$ABC = 'X'
ENDSQL

The problem is the parser effectively produces
OR:$ABC = 'X'
instead of
OR<space>:$ABC = 'X'
when the OR (same for an AND) ends the line and the next line doesn't start with a bracket.

The version that fails on SQLServer works fine on Oracle.

ProIV accept it's a bug and hopefully it will be fixed.

This is your captain speaking. We may experience some slight turbulance and then...explode.


#2 CurtisZ

CurtisZ

    Member

  • Members
  • PipPip
  • 19 posts
  • Gender:Male
  • Location:Seattle, Washington, USA
  • Interests:Programming
    Family
    Sports
    Auto repair

Posted 18 June 2010 - 04:01 PM

FYI - The following would probably be a good work-around

SQL
SELECT * FROM <TABLE>
WHERE :$ABC = 'Y'
OR :$ABC = 'X'
ENDSQL

Could be done as:

$X_STRING = ' OR ' + $ABC + ' = ' + CHAR(39) + 'X' + CHAR(39) + CHAR(32)

SQL
SELECT *
FROM <TABLE>
WHERE :$ABC = 'Y'
:$X_STRING
ENDSQL


#3 Marlon

Marlon

    Member

  • Members
  • PipPip
  • 29 posts
  • Gender:Male

Posted 19 June 2010 - 10:42 AM

Hi,
There's no need for such an elaborate work-around. Just having the OR on the same line of logic as the :$ABC = 'X' works. The parser screws up when you have OR on one line and :$ABC = 'X' on the next line.

FYI - The following would probably be a good work-around

SQL
SELECT * FROM <TABLE>
WHERE :$ABC = 'Y'
OR :$ABC = 'X'
ENDSQL

Could be done as:

$X_STRING = ' OR ' + $ABC + ' = ' + CHAR(39) + 'X' + CHAR(39) + CHAR(32)

SQL
SELECT *
FROM <TABLE>
WHERE :$ABC = 'Y'
:$X_STRING
ENDSQL


This is your captain speaking. We may experience some slight turbulance and then...explode.


#4 CurtisZ

CurtisZ

    Member

  • Members
  • PipPip
  • 19 posts
  • Gender:Male
  • Location:Seattle, Washington, USA
  • Interests:Programming
    Family
    Sports
    Auto repair

Posted 21 June 2010 - 08:47 PM

Hi,
There's no need for such an elaborate work-around. Just having the OR on the same line of logic as the :$ABC = 'X' works. The parser screws up when you have OR on one line and :$ABC = 'X' on the next line.


:)

Ah, okay, I slightly misunderstood the post then...

#5 DARREN

DARREN

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 396 posts
  • Gender:Male
  • Location:Florida,USA

Posted 21 July 2010 - 02:01 PM

You also do not need to define the * in the SQL statement as the parser will work out the columns that you need. Defining the * tells PROIV to return all the columns for the rows, even if you are not using them in your function which can make it less efficient.

Hi,
There's no need for such an elaborate work-around. Just having the OR on the same line of logic as the :$ABC = 'X' works. The parser screws up when you have OR on one line and :$ABC = 'X' on the next line.


:)

Ah, okay, I slightly misunderstood the post then...


Things should be made as simple as possible, but not simpler



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users