Search Database for Number on Outbound Call

[atinker]

I’m working on writing an outbound dialplan, and I got to thinking about how my calls are being routed.

I have about 600 DIDs at the moment and as of right now, if someone calls out a DID like NXXNXXXXX it pushes it out to the SIP trunk.

This happens for all calls, even the ones that are “internal” on the box.

What Im trying to accomplish is a database search (I have ODBC and the Integrated MySQL) to see if the number exists on the sippeers table, if so dial out SIP/{EXTEN} rather than SIP/TRUNK/{EXTEN}

As a phone provider, who offers free long distance and local, but still pays the providers on the other end. It just makes more sense to direct internal calls directly rather than looping though and not only costing money, but tying up call paths.

This is probably a simple thing, but Im a little lost.

Im also aware that searching the database could take a while, so I figured that I can look into the database, only if the area code and city code match. i.e exten => _123456XXXX

Any help would be appreciated!

Thanks!

[ambiorixg12]

@atinker

It just makes more sense to direct internal calls directly rather than looping

absolutely

@atinker

only if the area code and city code match. i.e exten => _123456XXXX

I don’t see the complexity if you already have ODBC working, I prefer AGI instead dialplan and ODBC, but where are you stuck?

[atinker]

Well, tbh Im kinda new to writing dial plans. I’ve made a few simple ones in the past, but I’ve never really gone into querying a database within a dial plan.

I’ve got a system that was written ages ago by one guy, then a bunch of people have been in it since. Now its a garbage dialplan. I mean… Garbage.

So I’m making a new system altogether.

I really just need to know what the strings would be to query the database, and how to direct from there.

I’m also not aganst using AGI, I just need to figure out how to set it up properly. (any links to that are greatly appreciated)

Thanks!

[ambiorixg12]

`same=>n,Set(Id={SHELL(mysql --user=root --password='1234' --skip-column-names conferences -e "select path from recordings where Id='{idnum}’ ")})

same=>n,Noop(${Id:0:-1})`

[johnkiniston]

How do you handle your incoming calls, do you have a context that has your 600 DID’s defined in it?

You may be able to use the DIALPLAN_EXISTS function https://wiki.asterisk.org/wiki/display/AST/Asterisk+13+Function_DIALPLAN_EXISTS to tell if there is a extension to handle your call and if so use a local channel to dial it.

ExecIf(${DIALPLAN_EXISTS(from-provider,${EXTEN:1})}?Dial(LOCAL/${EXTEN:1}@from-provider))

[atinker]

So, call me a noob, but I didnt understand that you put the ODBC commands in func_odbc.conf with something like this:

[COMMAND]
dsn=asterisk
readsql=select name from asterisk.sippeers where name=’{SQL_ESC({ARG1})}’

Then just call them from a dial plan with something like this:

same => n,SET(VAR={ODBC_COMMAND({EXTEN})})

Ive got it to the point now where I check for the number existing, then I check if its enabled.
Then I send the call. For both SIP and IAX2. So Ive got all calls that are internal going direct, then the others going out their respective trunk.

And inbound works the same way now, I check sippeers, then I check IAX to see if it exists, and how to send it.

At this point though, I probably would have been better off using AGI. Im exploring that route.

Thanks for all the input!