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-tp28262100p28287664.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/
