I'm trying to use func_odbc to write to a MS SQL db.
Here's my func_odbc conf:
[OPTIN]
dsn=MSSQL-Optin
write=INSERT into OptIn (orgID) values (${VAL1})
Dial Plan
exten => +18665551212,n,Set(ODBC_OPTIN()=dave)
When I do an odbc show, it shows that I am connected to the db. If I use isql, I
can write to the db, however, when I use func_odbc, a record will not write.
I'm using asterisk 1.4.9. Any idea what might be wrong?
Thanks
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://lists.digium.com/pipermail/asterisk-users/attachments/20090811/2878331d/attachment.htm
On Tuesday 11 August 2009 11:33:30 David Budny wrote:> I'm trying to use func_odbc to write to a MS SQL db. > > Here's my func_odbc conf: > > [OPTIN] > dsn=MSSQL-Optin > write=INSERT into OptIn (orgID) values (${VAL1}) > > Dial Plan > > exten => +18665551212,n,Set(ODBC_OPTIN()=dave) > > When I do an odbc show, it shows that I am connected to the db. If I use > isql, I can write to the db, however, when I use func_odbc, a record will > not write. I'm using asterisk 1.4.9. Any idea what might be wrong?I suspect the problem is that you're missing the necessary quotes for the string value of "dave". Try, instead: write=INSERT INTO OptIn (orgID) values ('${SQL_ESC(${VAL1})}') MS SQL Server only permits you to leave the quotes off when you're inserting a value into a numeric field. -- Tilghman & Teryl with Peter, Cottontail, Midnight, Thumper, & Johnny (bunnies) and Harry, BB, & George (dogs)
David wrote:> When I do an odbc show, it shows that I am connected to the db. If I use isql, I can write to the db, however, when I use func_odbc, a record will not write. I'm using asterisk 1.4.9. Any idea what might be wrong?Sounds sneakingly like a permissions problem in your database for the login account that you are using through ODBC. But if not, the INSERT SQL statement may not be correctly established for your database. Personally I don't use ODBC for this sort of thing. Its not really a 'Linux' type thing, and relies on a lot of middle-ware layers between the Asterisk server and the database. Not that you have too many choices, but what we do that works brilliantly well here is to use PHP-AGI to construct a PHP script that does all the DB connection, inserting, etc. and just have the Asterisk dialplan call that. This way I have total visibility to the SQL and I have apply some transforms to the data in PHP if needed. It also means that if I want to swap out the DB to another brand, I can do that with only one place to change the code. FWIW... Myles -- ======================Myles Wakeham Director of Engineering Tech Solutions USA, Inc. Scottsdale, Arizona USA http://www.techsolusa.com Phone +1-480-451-7440
Date: Tue, 11 Aug 2009 12:19:15 -0500 From: Tilghman Lesher <tilghman at mail.jeffandtilghman.com> Subject: Re: [asterisk-users] func_odbc insert with mssql To: "Asterisk Users Mailing List - Non-Commercial Discussion" <asterisk-users at lists.digium.com> Message-ID: <200908111219.15094.tilghman at mail.jeffandtilghman.com> Content-Type: text/plain; charset="iso-8859-1" On Tuesday 11 August 2009 11:33:30 David Budny wrote:> I'm trying to use func_odbc to write to a MS SQL db. > > Here's my func_odbc conf: > > [OPTIN] > dsn=MSSQL-Optin > write=INSERT into OptIn (orgID) values (${VAL1}) > > Dial Plan > > exten => +18665551212,n,Set(ODBC_OPTIN()=dave) > > When I do an odbc show, it shows that I am connected to the db. If I use > isql, I can write to the db, however, when I use func_odbc, a record will > not write. I'm using asterisk 1.4.9. Any idea what might be wrong?I suspect the problem is that you're missing the necessary quotes for the string value of "dave". Try, instead: write=INSERT INTO OptIn (orgID) values ('${SQL_ESC(${VAL1})}') MS SQL Server only permits you to leave the quotes off when you're inserting a value into a numeric field. -- Tilghman & Teryl with Peter, Cottontail, Midnight, Thumper, & Johnny (bunnies) and Harry, BB, & George (dogs) That did the trick. Thank you.
Tilghman Lesher wrote: >You've just stated the primary reason for folks to use ODBC. Perhaps >you've >written off the technology too soon and for the wrong reasons. You maybe right as I've been a SQL developer for about 25 years (actually before ODBC was ever around) and I might be tainted by spending countless hours of pain & suffering dealing with it on a variety of non-Microsoft platforms. Off memory, and this is from a LONG time ago, I believe ODBC was originally created by Microsoft & SIMBRA back in the early 1990s, hence my comments about it not being a 'Linux type' technology. In its early incarnation it had a reputation of being bloated and lacked in performance. I'm sure that 15 yrs or so later they must have gotten that under control. My biggest negative to ODBC is that the calls that the client application has to make to the database are hard to visualize and debug, particularly if you are crafting a SQL QUERY statement that goes on for pages. Its for this reason that I'd want to have something where I could get more visibility to the DML. It sounds like this might be the exact problem that the original poster was having - that their DML had errors in it, and if it could be visualized before it hit the database for debugging, it might have been quicker to debug. Myles -- ======================Myles Wakeham Director of Engineering Tech Solutions USA, Inc. Scottsdale, Arizona USA http://www.techsolusa.com Phone +1-480-451-7440