# Issues with using ODBC to query json response from MySQL

**URL:** <https://community.asterisk.org/t/issues-with-using-odbc-to-query-json-response-from-mysql/100193>\
**Category:** Asterisk Support\
**Created:** [December 22, 2023, 3:05pm UTC](https://community.asterisk.org/t/issues-with-using-odbc-to-query-json-response-from-mysql/100193 "2023-12-22T15:05:46Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![dovi5988](https://sea1.discourse-cdn.com/asterisk/user_avatar/community.asterisk.org/dovi5988/32/7320_2.png) [@dovi5988](https://community.asterisk.org/u/dovi5988)\
**Post date:** [December 22, 2023, 3:05pm UTC](https://community.asterisk.org/t/issues-with-using-odbc-to-query-json-response-from-mysql/100193/1 "2023-12-22T15:05:46Z")

</div>

I am using func\_odbc to query a table and get values out of a JSON field.

My Dialplan:

```auto
Set(RESULT=${ODBC_CHARIDY_GET_TEAM_RAISED_BY_ID(23277,749616,eur)})

```

If I have in func\_odbc.conf

```auto
[CHARIDY_GET_TEAM_RAISED_BY_ID]
writehandle=charidy
synopsis=Get amount raised by actual team ID
readsql=select campaign_id FROM teams where campaign_id = '${SQL_ESC(${ARG1})}' and team_id = '${SQL_ESC(${ARG2})}';

```

Then everything is fine. However if I change that to

```auto
[CHARIDY_GET_TEAM_RAISED_BY_ID]
writehandle=charidy
synopsis=Get amount raised by actual team ID
readsql=select campaign_id, JSON_EXTRACT(team_data, '$.goal') AS goal FROM teams where campaign_id = '${SQL_ESC(${ARG1})}' and team_id = '${SQL_ESC(${ARG2})}';

```

I see in the CLI

```auto
[2023-12-22 10:02:06] WARNING[78835][C-00000008]: func_odbc.c:1009 acf_odbc_read: SQL Get Data error!
[select campaign_id, JSON_EXTRACT(team_data, '$.goal') AS goal FROM teams where campaign_id = '23277' and team_id = '749616']

```

The query is a perfectly valid query. Running ngrep on port 3306 shows the qeury going and response from MySQL. Also running the query directly in MYSQL works as well. My hunch is the $ is causing it but I can’t explain why that would be. Could this be a bug?

---

<div class="post-metadata">

**Author:** ![penguinpbx](https://sea1.discourse-cdn.com/asterisk/user_avatar/community.asterisk.org/penguinpbx/32/8856_2.png) [@penguinpbx](https://community.asterisk.org/u/penguinpbx)\
**Post date:** [January 5, 2024, 11:02pm UTC](https://community.asterisk.org/t/issues-with-using-odbc-to-query-json-response-from-mysql/100193/2 "2024-01-05T23:02:05Z")

</div>

You might want to turn up logging to include DEBUG messages. On the Asterisk CLI, try “core set debug 10”.

> [@dovi5988](#):
>
> Could this be a bug?

Maybe, but there are workarounds, such as modifying your SQL to wrap a VIEW around the TABLE, then in your func\_odbc.conf you would SELECT from the VIEW instead of the TABLE.

---

<div class="post-metadata">

**Author:** ![PitzKey](https://sea1.discourse-cdn.com/asterisk/user_avatar/community.asterisk.org/pitzkey/32/5827_2.png) [@PitzKey](https://community.asterisk.org/u/PitzKey)\
**Post date:** [January 7, 2024, 10:41pm UTC](https://community.asterisk.org/t/issues-with-using-odbc-to-query-json-response-from-mysql/100193/3 "2024-01-07T22:41:15Z")

</div>

What happens when you escape it?

---

<div class="post-metadata">

**Author:** ![dovi5988](https://sea1.discourse-cdn.com/asterisk/user_avatar/community.asterisk.org/dovi5988/32/7320_2.png) [@dovi5988](https://community.asterisk.org/u/dovi5988)\
**Post date:** [January 8, 2024, 9:45am UTC](https://community.asterisk.org/t/issues-with-using-odbc-to-query-json-response-from-mysql/100193/4 "2024-01-08T09:45:30Z")

</div>

I opened a bug report on GitHub ([[bug]: func\_odbc: Large octet length results in crash · Issue #510 · asterisk/asterisk · GitHub](https://github.com/asterisk/asterisk/issues/510)) as some in some scenarios it was causing Asterisk to crash. @jcolp mentioned that he saw a lot of memory being used. A simple way to fix the issue was to create a stored procedure where the max value for the fields were defined which fixed the problem.

---

<div class="post-metadata">

**Author:** ![system](https://global.discourse-cdn.com/asterisk/original/2X/4/475d0c889911cfb84ac4881ad6df8b523e3f9105.png) [@system](https://community.asterisk.org/u/system)\
**Post date:** [February 7, 2024, 9:45am UTC](https://community.asterisk.org/t/issues-with-using-odbc-to-query-json-response-from-mysql/100193/5 "2024-02-07T09:45:43Z")

</div>

Closed on February 7, 2024, 9:45am UTC

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.