[ezRETS-users] SQL error: Invalid Query Syntax, SQL state 42000 in SQLExecDirect.

[Keith T. Garner]

Tony Russo wrote:
> See logs at ezRETS-logs
> 
> This query ‘SELECT UID FROM data:AGENT:AGENT WHERE (UID>=0) LIMIT 0’
> 
> Returns 68,671 UID’s.
> 
> The returned UID’s are used to build a batch for a known number of
> records, 500 per query.
> 
> All’s fine until record number 18,096 where the UID returned = ‘EF029]’

This is one problem, which is actually separate from the problem in your
next sentence.  The real question is "was that valid data returned?"

I can't believe that an MLSID has a ] in it, but its certainly possible.
Looking at the logs you sent, that is clearly what was passed along.  Its
out of line with ever other id sent, so to me it looks like it is a bug on
the server.  I was thinking that it might be the start of an ANSI escape
sequence, but its just a plain old ] in the data stream.  Since its in the
HTTP log, it had to come from the server.

This doesn't seem right to me, but its not outside the realm of possibility.
 Its wacky since there is also an EF029.  I wonder if EF029 is related to
EF029] in any way.

> When this UID in but in a batch query ezRETS returns SQL error: Invalid
> Query Syntax, SQL state 42000 in SQLExecDirect.

This is an interesting problem.  What is returned from a RETS query seems to
have greater freedom then what can be in a query.

Looking at the RETS spec 1.5 section 7.7.1, a string must be made up of an
ALPHANUM which, when consulting section 2.4, shows us:

   UPALPHA ::= <any US-ASCII uppercase letter "A".."Z">
   LOALPHA ::= <any US-ASCII lowercase letter "a".."z">
   ALPHA ::= UPALPHA | LOALPHA
   DIGIT ::= <any US-ASCII digit "0".."9">
   ALPHANUM ::= ALPHA | DIGIT

That's a long way to go for me to say that ']' is out as a character to
query on.  Except, section 7.7.1 also tells us that we can do a
string-literal which is defined as

  string-literal ::= " 1*( *( PLAINTEXT except " ) *( 2 * " ) *( PLAINTEXT
except " ) ) "

To make it easier to read means you can do some PLAINTEXT in quotes where
2.4 defines PLAINTEXT as
  PLAINTEXT ::= <any OCTET except CTLs>

So...  This is an edge case our SQL to DMQL translator doesn't really
handle.  In doing a quick test against the translator, I think you could
sneak it through by doing a query similar to:

  SELECT * FROM table WHERE UID = '"EF029]"'

This passes through ezRETS just fine.  Against my Variman test server, the
server vomits on this, but I think we just identified a bug in Variman
rather than proved the quoted query isn't valid.

In any case, I think its extremely bad practice on the part of the MLS or
MLS vendor to have a ] in an ID field like that.  Knowing who the vendor is
(thanks to the logs) I'm surprised its there at all.

> The problem is this UID is being sent by the server, so I have no
> control over what the server sends as the UID unique key. 

Yep, that's a problem.

> I’ve tried encoding it but that doesn’t work.
> Would you say this is a server issue, bad UID code?

I'd say "its allowed by the spec, but its extremely bad practice."

Keith

-- 
  Keith T. Garner - Managing Director - Center for REALTOR® Technology
   kgarner at realtors.org - 312-329-3294 - http://blog.realtors.org/crt