A peer suggested using
concat(alias,CHAR(37)) instead of concat(alias,'%') and it works
Documenting here for posterity.
Scott
ScottC wrote:
>
> Hi Jan,
> Thanks for your response. I posted here instead of a SQL forum, because I
> figured someone had already needed to do a longest match....and therefore
> I wouldn't have to explain the full requirements of why I needed to NOT do
> it 10 other ways.
>
> I still have a problem tho. When try to make the query (from GNU-GK),
> the % in the concat doesn't get interpreted right. I think it's waiting
> for one of [s,c,p,r,i,m]. I tried to escape it several ways, different
> quotes, etc. The sql statement works fine if I do it by hand, but I
> think GNU-GK isn't.
> Here's the error I get.
> Routing.cxx(1681) SqlPolicy: query failed (1) - You have an error in
> your SQL syntax; check the manual that corresponds to your MySQL server
> version for the right syntax to use near '') order by length(alias)
> desc,pref' at line 1
> Here is the line from my gk.ini
> Query=select hostIP from h323Routes where '%c' like concat(alias,'%')
> order by length(alias) desc,pref;
>
> Any thoughts?
> Scott
>
> Willamowius wrote:
>>
>> Hi Scott,
>>
>> I think this is more of an SQL question that depends on what database
>> and table structure you are using, than a GnuGk question.
>>
>> Anyway, the simple form of such a query could be
>>
>> select gwip from routes where '%c' like concat(prefix,"%") order by
>> length(prefix) desc;
>>
>> This assumes a table routes with a prefix and a gateway ip field.
>> The query would produce a list of gateways that match the called number
>> sorted by longest prefix match.
>>
>> To avoid all the ugly IF handling if nothing matches, I would add a
>> route with an empty prefix and 'REJECT' in the gateway IP.
>>
>> Regards,
>> Jan
>>
>>
>> ScottC wrote:
>>>
>>> I'm trying to use a SQL table to hold my routes and want to search for
>>> longest match.
>>> This works using this query.
>>> select if((select count(*) from h323Routes where alias='%c'),(select
>>> hostIP
>>> from h323Routes where alias='%c'),SUBSTR('%c',1,(LENGTH('%c')-1)))
>>>
>>> Now...I want to try to add a second route to be used for failover. When
>>> I
>>> do this, I get "ERROR 1242 (21000): Subquery returns more than 1 row".
>>> I read on this and it seems to suggest using the keyword ANY, but I
>>> can't
>>> find any examples of this in an IF statement.
>>>
>>> Any help would be greatly appreciated.
>>> Scott
>>
>> --
>> Jan Willamowius, jan@xxxxxxxxxxxxxx, http://www.gnugk.org/
>>
>> ------------------------------------------------------------------------------
>> Download Intel® Parallel Studio Eval
>> Try the new software tools for yourself. Speed compiling, find bugs
>> proactively, and fine-tune applications for parallel performance.
>> See why Intel Parallel Studio got high marks during beta.
>> http://p.sf.net/sfu/intel-sw-dev
>> _______________________________________________________
>>
>> Posting: mailto:Openh323gk-users@xxxxxxxxxxxxxxxxxxxxx
>> Archive:
>> http://sourceforge.net/mailarchive/forum.php?forum_name=openh323gk-users
>> Unsubscribe: http://lists.sourceforge.net/lists/listinfo/openh323gk-users
>> Homepage: http://www.gnugk.org/
>>
>>
>
>
--
View this message in context: http://old.nabble.com/SQL-query-for-longest-match-%28multiple-results%29...SQL-error-tp28262100p28287752.html
Sent from the GNU Gatekeeper Users mailing list archive at Nabble.com.
------------------------------------------------------------------------------
Download Intel® Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev
_______________________________________________________
Posting: mailto:Openh323gk-users@xxxxxxxxxxxxxxxxxxxxx
Archive: http://sourceforge.net/mailarchive/forum.php?forum_name=openh323gk-users
Unsubscribe: http://lists.sourceforge.net/lists/listinfo/openh323gk-users
Homepage: http://www.gnugk.org/
