Uploaded image for project: 'FreeSWITCH'
  1. FreeSWITCH
  2. FS-9727

Fields extracted from the DB are only limited to 255

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 1.6.12
    • Fix Version/s: 1.8
    • Component/s: mod_lua
    • Security Level: public
    • Labels:
      None
    • Environment:
      debian 8.4
      postgresql 9.4
      python 3.4
      django 1.10
    • CPU Architecture:
      x86-64
    • Kernel:
      Linux
    • uname:
      Linux at173 3.16.0-4-amd64 #1 SMP Debian 3.16.36-1+deb8u2 (2016-10-19) x86_64 GNU/Linux
    • Userland:
      GNU/Linux
    • Distribution:
      Debian
    • Distribution Version:
      Debian 8 jessie
    • lsb_release:
      Hide
      root@at173:/usr/share/freeswitch/scripts# lsb_release -a
      No LSB modules are available.
      Distributor ID: Debian
      Description: Debian GNU/Linux 8.6 (jessie)
      Release: 8.6
      Codename: jessie
      Show
      root@at173 :/usr/share/freeswitch/scripts# lsb_release -a No LSB modules are available. Distributor ID: Debian Description: Debian GNU/Linux 8.6 (jessie) Release: 8.6 Codename: jessie
    • Compiler:
      gcc
    • FreeSWITCH GIT Revision:
      master
    • GIT Master Revision hash::
      instal from debian repositories

      Description

                assert(dbh:query(sql_rol_default, function(row)
                  for key, val in pairs(row) do
                    data_rol_default[key] = val
                    freeswitch.consoleLog("debug", "ROL " .. key .. " / " .. val )
                  end
                end))

      row::STRING is limited to 255 characters, but if fields in postgresql is JsonFIELD should be a row::TEXT.

      If the field of postgresql is a jsonfield, to make it a lua to a json object, but it is not possible because the change is limited to 255 characters.

        Activity

        Hide
        mikej Mike Jerris added a comment -
        please attach full working sample script
        Show
        mikej Mike Jerris added a comment - please attach full working sample script
        Hide
        aztrock Jorge Cadena Argote added a comment - - edited
        DB:
                                                    Tabla «public.at_atresult»
              Columna | Tipo | Modificadores
        -------------------+--------------------------+--------------------------------------------------------------------
         id | integer | not null valor por omisión nextval('at_atresult_id_seq'::regclass)
         rol_1 | character varying(255) | not null
         number | character varying(255) | not null
         name_list | character varying(255) | not null
         last_modification | timestamp with time zone | not null
         data | jsonb | not null
        Índices:
            "at_atresult_pkey" PRIMARY KEY, btree (id)
            "at_atresult_0a9a9f9f" btree (name_list)
            "at_atresult_4d5128a0" btree (numero)
            "at_atresult_66dcfb33" btree (rol_1)
            "at_atresult_name_list_1290b956_like" btree (name_list varchar_pattern_ops)
            "at_atresult_numero_3fbe1c6f_like" btree (numero varchar_pattern_ops)
            "at_atresult_rol_1_d379ad8e_like" btree (rol_1 varchar_pattern_ops)


         SELECT * FROM number_at WHERE number='57XXXXXXXXXX);
          id | rol_1 | number | name_list | last_modification | data
        -------+-------+--------------+-----------+-------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         46551 | | 57XXXXXXXXXX | Name List | 2016-11-09 09:17:08.119248-05 | {"stats": {"acd": 185, "asr": 96.77, "billsec": 5556, "total_calls": 31, "total_fails": 1, "total_answer": 30}, "causes": {"16": 31}, "causes_sip": {"sip:200": 16, "sip:487": 1}, "portability": false, "causes_custom": {}, "last_cause_sip": "sip:200", "last_cause_q850": "16"}
        (1 fila)



        LUA:
        --
        -- DATABASE CONNECTION
        --
        if session:ready() then
          dbh = freeswitch.Dbh("odbc://PostgreSQL")
          if dbh:connected() == false then
            set_variable("kibox_hangup_cause", "DB_ERROR")
            freeswitch.consoleLog("ERROR", "dbh Database error - Hangup Call")
            session:hangup("NORMAL_TEMPORARY_FAILURE")
            return
          else
            freeswitch.consoleLog("debug", "Database Connected")
          end
        end

        if session:ready() then
                local sql_rol_result = [[
                  SELECT
                    name_list,
                    rol_1,
                    data
                  FROM
                    at_atresult
                  WHERE
                    number = ']] .. number_at .. [['
                ]]

                assert(dbh:query(sql_rol_result, function(row)
                  session:consoleLog("WARNING", row["data"])
                  for key, val in pairs(row) do
                    data_rol[key] = val
                    freeswitch.consoleLog("debug", "result: " .. key .. " / " .. val )
                  end
                end))
        end

        DIALPLAN:
                        <extension name="all_with_prefix" continue="true">
                                <condition field="destination_number" expression="^(\d+\.*\d+)$">
                                        <action application="set" data="TARIFICADOR=true"/>
                                        <action application="lua" data="kibox_dp.lua"/>
                                </condition>
                        </extension>

        FS_CLI:

        2016-11-11 08:46:56.740259 [DEBUG] switch_cpp.cpp:1360 result:: name_list / Name List
        2016-11-11 08:46:56.740259 [DEBUG] switch_cpp.cpp:1360 result: rol_1 /
        2016-11-11 08:46:56.740259 [DEBUG] switch_cpp.cpp:1360 result: data / {"stats": {"acd": 185, "asr": 96.77, "billsec": 5556, "total_calls": 31, "total_fails": 1, "total_answer": 30}, "causes": {"16": 31}, "causes_sip": {"sip:200": 16, "sip:487": 1}, "portability": false, "causes_custom": {}, "last_cause_sip": "sip:200", "las



        Show
        aztrock Jorge Cadena Argote added a comment - - edited DB:                                             Tabla «public.at_atresult»       Columna | Tipo | Modificadores -------------------+--------------------------+--------------------------------------------------------------------  id | integer | not null valor por omisión nextval('at_atresult_id_seq'::regclass)  rol_1 | character varying(255) | not null  number | character varying(255) | not null  name_list | character varying(255) | not null  last_modification | timestamp with time zone | not null  data | jsonb | not null Índices:     "at_atresult_pkey" PRIMARY KEY, btree (id)     "at_atresult_0a9a9f9f" btree (name_list)     "at_atresult_4d5128a0" btree (numero)     "at_atresult_66dcfb33" btree (rol_1)     "at_atresult_name_list_1290b956_like" btree (name_list varchar_pattern_ops)     "at_atresult_numero_3fbe1c6f_like" btree (numero varchar_pattern_ops)     "at_atresult_rol_1_d379ad8e_like" btree (rol_1 varchar_pattern_ops)  SELECT * FROM number_at WHERE number='57XXXXXXXXXX);   id | rol_1 | number | name_list | last_modification | data -------+-------+--------------+-----------+-------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  46551 | | 57XXXXXXXXXX | Name List | 2016-11-09 09:17:08.119248-05 | {"stats": {"acd": 185, "asr": 96.77, "billsec": 5556, "total_calls": 31, "total_fails": 1, "total_answer": 30}, "causes": {"16": 31}, "causes_sip": {"sip:200": 16, "sip:487": 1}, "portability": false, "causes_custom": {}, "last_cause_sip": "sip:200", "last_cause_q850": "16"} (1 fila) LUA: -- -- DATABASE CONNECTION -- if session:ready() then   dbh = freeswitch.Dbh(" odbc://PostgreSQL ")   if dbh:connected() == false then     set_variable("kibox_hangup_cause", "DB_ERROR")     freeswitch.consoleLog("ERROR", "dbh Database error - Hangup Call")     session:hangup("NORMAL_TEMPORARY_FAILURE")     return   else     freeswitch.consoleLog("debug", "Database Connected")   end end if session:ready() then         local sql_rol_result = [[           SELECT             name_list,             rol_1,             data           FROM             at_atresult           WHERE             number = ']] .. number_at .. [['         ]]         assert(dbh:query(sql_rol_result, function(row)           session:consoleLog("WARNING", row["data"])           for key, val in pairs(row) do             data_rol[key] = val             freeswitch.consoleLog("debug", "result: " .. key .. " / " .. val )           end         end)) end DIALPLAN:                 <extension name="all_with_prefix" continue="true">                         <condition field="destination_number" expression="^(\d+\.*\d+)$">                                 <action application="set" data="TARIFICADOR=true"/>                                 <action application="lua" data="kibox_dp.lua"/>                         </condition>                 </extension> FS_CLI: 2016-11-11 08:46:56.740259 [DEBUG] switch_cpp.cpp:1360 result:: name_list / Name List 2016-11-11 08:46:56.740259 [DEBUG] switch_cpp.cpp:1360 result: rol_1 / 2016-11-11 08:46:56.740259 [DEBUG] switch_cpp.cpp:1360 result: data / {"stats": {"acd": 185, "asr": 96.77, "billsec": 5556, "total_calls": 31, "total_fails": 1, "total_answer": 30}, "causes": {"16": 31}, "causes_sip": {"sip:200": 16, "sip:487": 1}, "portability": false, "causes_custom": {}, "last_cause_sip": "sip:200", "las
        Hide
        mikej Mike Jerris added a comment -
        Looking in the function switch_odbc_handle_callback_exec_detailed in switch_odbc. We perform a SQLDescribeCol to get the column info including size, if it doesn't return, we use a default of 255. Can you please look into the details of the odbc drive to see why it doesn't return a proper length in this case? Code in question is:

                    SQLDescribeCol(stmt, x, (SQLCHAR *) names[y], (SQLSMALLINT) name_len, &NameLength, &DataType, &ColumnSize, &DecimalDigits, &Nullable);
                                                                                                                                                                        
                    if (!ColumnSize) {
                        ColumnSize = 255;
                    }
                    ColumnSize++;
                                                                                                                                                                        
                    vals[y] = malloc(ColumnSize);
                    memset(vals[y], 0, ColumnSize);
                    SQLGetData(stmt, x, SQL_C_CHAR, (SQLCHAR *) vals[y], ColumnSize, NULL);
                    y++;
                }
        Show
        mikej Mike Jerris added a comment - Looking in the function switch_odbc_handle_callback_exec_detailed in switch_odbc. We perform a SQLDescribeCol to get the column info including size, if it doesn't return, we use a default of 255. Can you please look into the details of the odbc drive to see why it doesn't return a proper length in this case? Code in question is:             SQLDescribeCol(stmt, x, (SQLCHAR *) names[y], (SQLSMALLINT) name_len, &NameLength, &DataType, &ColumnSize, &DecimalDigits, &Nullable);                                                                                                                                                                              if (!ColumnSize) {                 ColumnSize = 255;             }             ColumnSize++;                                                                                                                                                                              vals[y] = malloc(ColumnSize);             memset(vals[y], 0, ColumnSize);             SQLGetData(stmt, x, SQL_C_CHAR, (SQLCHAR *) vals[y], ColumnSize, NULL);             y++;         }
        Hide
        aztrock Jorge Cadena Argote added a comment - - edited
        I think, but I'm not 100% sure but that jsonb field has no length, it would be more convenient to treat it as a text field and not as a varying (255)

        https://www.postgresql.org/docs/9.4/static/datatype-json.html

        or limit field to 512 and describe in the wiki explained

        It is possible that I expand the field to 512 and compile, as would be the steps to follow if I perform the installation from the FreeSWITCH repositories for debian 8?
        Show
        aztrock Jorge Cadena Argote added a comment - - edited I think, but I'm not 100% sure but that jsonb field has no length, it would be more convenient to treat it as a text field and not as a varying (255) https://www.postgresql.org/docs/9.4/static/datatype-json.html or limit field to 512 and describe in the wiki explained It is possible that I expand the field to 512 and compile, as would be the steps to follow if I perform the installation from the FreeSWITCH repositories for debian 8?
        Hide
        mikej Mike Jerris added a comment -
        pushed a fix to master. Please test and confirm for me. Its raised to support up to 16k data size now. it will only allocate the size of the actual string
        Show
        mikej Mike Jerris added a comment - pushed a fix to master. Please test and confirm for me. Its raised to support up to 16k data size now. it will only allocate the size of the actual string

          People

          • Assignee:
            mikej Mike Jerris
            Reporter:
            aztrock Jorge Cadena Argote
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development