Have you tried to escape with %% too?
Query=select hostIP from h323Routes where '%c' like
concat(alias,'%%') order by length(alias) desc,pref;
At 03:51 PM 4/19/2010, 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-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/
------------------------------------------------------------------------------
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/
