postgreSQL db access fail

Discussion in 'Call Flow Designer' started by Trestill, Sep 8, 2017.

Thread Status:
Not open for further replies.
  1. Trestill

    Joined:
    Jan 22, 2015
    Messages:
    39
    Likes Received:
    1
    Hi,

    I'm trying to get some info from database with CFD component but I'm unable to even connect to it. Any suggestions? I checked the credentials and the query with pgadmin installed on the same server as 3cx and it's working.
    Code:
    PlugIn[my_plugin - DatabaseAccessComponent - CallID PLGEWSATALBQ] Trace: Start executing component with server='########.###' - port='3306' - database='######_0000003' - userName='#####_0000003' - password='!qo1ShvLEO!L' - statementType='Query'
    17-09-08 14:57:26.149|100049| Trc|75|0038|: PlugIn[my_plugin - DatabaseAccessComponent - CallID PLGEWSATALBQ] Trace: Command to execute: SELECT *
    FROM  `CID`
    WHERE  `ext` =  200
    AND `last` = FALSE
    17-09-08 14:57:26.555|100030| Err|10|0038|: PlugIn[my_plugin - Callflow - MainFlow - CallID PLGEWSATALBQ] ERROR: Error executing last component: System.InvalidOperationException: Internal connection fatal error. Error state: 18
       at System.Data.SqlClient.TdsParserStateObject.TryProcessHeader()
       at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(SqlAuthenticationMethod authType, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean& marsCapable, Boolean& fedAuthRequired)
       at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, Boolean isFirstTransparentAttempt, SqlAuthenticationMethod authType)
       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isFirstTransparentAttempt)
       at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
       at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
       at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.Open()
       at my_plugin.DatabaseAccessComponent.prepareCommand(IDbConnection conn, QMExtendAPI iface, String id)
       at my_plugin.DatabaseAccessComponent.executeReader(IDbConnection conn, QMExtendAPI iface, String id)
       at my_plugin.DatabaseAccessComponent.executeStatement(IDbConnection conn, QMExtendAPI iface, String id)
       at my_plugin.DatabaseAccessComponent.executeStart(QMExtendAPI iface, String id)
       at my_plugin.DatabaseAccessComponent.Start(QMExtendAPI iface, CallQueue callQueue, QueueCall queueCall, ActiveConnection activeConnection, TimerManager timerManager, Dictionary`2 variableMap, TempWavFileManager tempWavFileManager, PromptQueue promptQueue)
       at my_plugin.Callflow.ProcessStart()
    Please help
     
  2. edossantos

    edossantos Support Team
    Staff Member 3CX Support

    Joined:
    Jun 27, 2007
    Messages:
    1,178
    Likes Received:
    89
    Hi @Trestill,

    Investigating the error code you're getting, it seems that the server is having problems processing your query, and then it suddently disconnects. I think that the issue might be related to the characters used in your query. From the logs I see that the query is:
    The quotes you're using are not the regular ones, and that might cause the issue. I would try with the following query:
    Kind regards,
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. Trestill

    Joined:
    Jan 22, 2015
    Messages:
    39
    Likes Received:
    1
    Hi Ernesto,

    I changed quotes as you suggested but result is the same. The same query run on pgadmin connected to this database works fine.
    Code:
    17-09-11 10:03:44.244|100049| Trc|75|0013|: PlugIn[random_cid - DatabaseAccessComponent - CallID KTKJMUIHSYYL] Trace: Command to execute: SELECT *
    FROM 'CID'
    WHERE 'ext' = 200
    AND 'last' = FALSE
    17-09-11 10:03:44.336|100030| Err|10|0013|: PlugIn[random_cid - Callflow - MainFlow - CallID KTKJMUIHSYYL] ERROR: Error executing last component: System.InvalidOperationException: Internal connection fatal error. Error state: 18
       at System.Data.SqlClient.TdsParserStateObject.TryProcessHeader()
       at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(SqlAuthenticationMethod authType, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean& marsCapable, Boolean& fedAuthRequired)
       at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, Boolean isFirstTransparentAttempt, SqlAuthenticationMethod authType)
       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isFirstTransparentAttempt)
       at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
       at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
       at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
       at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
       at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
       at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
       at System.Data.SqlClient.SqlConnection.Open()
       at random_cid.DatabaseAccessComponent.prepareCommand(IDbConnection conn, QMExtendAPI iface, String id)
       at random_cid.DatabaseAccessComponent.executeReader(IDbConnection conn, QMExtendAPI iface, String id)
       at random_cid.DatabaseAccessComponent.executeStatement(IDbConnection conn, QMExtendAPI iface, String id)
       at random_cid.DatabaseAccessComponent.executeStart(QMExtendAPI iface, String id)
       at random_cid.DatabaseAccessComponent.Start(QMExtendAPI iface, CallQueue callQueue, QueueCall queueCall, ActiveConnection activeConnection, TimerManager timerManager, Dictionary`2 variableMap, TempWavFileManager tempWavFileManager, PromptQueue promptQueue)
       at random_cid.Callflow.ProcessStart()
    Best Regards,

    Trestill
     
  4. edossantos

    edossantos Support Team
    Staff Member 3CX Support

    Joined:
    Jun 27, 2007
    Messages:
    1,178
    Likes Received:
    89
    Hi @Trestill,

    You say you're using PoatgreSQL, but the stack trace shows the SQL server client library. Have you selected the proper database type?

    Kind regards.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. Trestill

    Joined:
    Jan 22, 2015
    Messages:
    39
    Likes Received:
    1
    Ernesto,

    as always you are great help. I corrected setup for postgresql but now I have some trouble with query.
    Code:
    17-09-11 15:06:15.091|100049| Trc|75|0014|: PlugIn[random_cid - DatabaseAccessComponent - CallID WBNHIGYTMQRF] Trace: Command to execute: SELECT * FROM 'CIDs' WHERE ext = 200 AND last = FALSE
    17-09-11 15:06:15.410|100030| Err|10|0014|: PlugIn[random_cid - Callflow - MainFlow - CallID WBNHIGYTMQRF] ERROR: Error executing last component: Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "'CIDs'"
    I tried using " or not using quotes at all but there is always some errors. Any suggestions?

    Thank you
     
  6. edossantos

    edossantos Support Team
    Staff Member 3CX Support

    Joined:
    Jun 27, 2007
    Messages:
    1,178
    Likes Received:
    89
    Well, that's a syntax error, so maybe CIDs is not a valid table name... Can you check that? I would try without quotes as well.

    Regards.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  7. Trestill

    Joined:
    Jan 22, 2015
    Messages:
    39
    Likes Received:
    1
    from pgadmin:
    upload_2017-9-12_13-42-19.png
    from cfd without quotes:
    Code:
    17-09-12 13:38:37.580|100049| Trc|75|0014|: PlugIn[random_cid - DatabaseAccessComponent - CallID ZXIODDHPJNYS] Trace: Command to execute: SELECT * FROM CIDs WHERE ext = 200 AND last = FALSE
    17-09-12 13:38:37.581|100046| Trc|75|0006|: DBG: CMNotify(ZXIODDHPJNYS): LegStateChanged, legId 539
    17-09-12 13:38:40.352|100030| Err|10|0014|: PlugIn[random_cid - Callflow - MainFlow - CallID ZXIODDHPJNYS] ERROR: Error executing last component: Npgsql.PostgresException (0x80004005): 42P01: relation "cids" does not exist
       at Npgsql.NpgsqlConnector.DoReadMessage(DataRowLoadingMode dataRowLoadingMode, Boolean isPrependedMessage)
       at Npgsql.NpgsqlConnector.ReadMessageWithPrepended(DataRowLoadingMode dataRowLoadingMode)
       at Npgsql.NpgsqlConnector.ReadExpecting[T]()
       at Npgsql.NpgsqlDataReader.NextResultInternal()
       at Npgsql.NpgsqlDataReader.NextResult()
       at Npgsql.NpgsqlCommand.Execute(CommandBehavior behavior)
       at Npgsql.NpgsqlCommand.ExecuteDbDataReaderInternal(CommandBehavior behavior)
       at random_cid.DatabaseAccessComponent.executeReader(IDbConnection conn, QMExtendAPI iface, String id)
       at random_cid.DatabaseAccessComponent.executeStatement(IDbConnection conn, QMExtendAPI iface, String id)
       at random_cid.DatabaseAccessComponent.executeStart(QMExtendAPI iface, String id)
       at random_cid.DatabaseAccessComponent.Start(QMExtendAPI iface, CallQueue callQueue, QueueCall queueCall, ActiveConnection activeConnection, TimerManager timerManager, Dictionary`2 variableMap, TempWavFileManager tempWavFileManager, PromptQueue promptQueue)
       at random_cid.Callflow.ProcessStart()

    with " project can't be build:
    SQL statement: SELECT * FROM "CIDs" WHERE ext = {0} AND last = FALSE
    upload_2017-9-12_13-43-50.png

    So.... How to escape " characters?

    Regards
     
  8. edossantos

    edossantos Support Team
    Staff Member 3CX Support

    Joined:
    Jun 27, 2007
    Messages:
    1,178
    Likes Received:
    89
    Use the back slash:
    SELECT * FROM \"CIDs\" WHERE ext = {0} AND last = FALSE
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  9. Trestill

    Joined:
    Jan 22, 2015
    Messages:
    39
    Likes Received:
    1
    I tried that:
    upload_2017-9-12_15-21-59.png
     
  10. edossantos

    edossantos Support Team
    Staff Member 3CX Support

    Joined:
    Jun 27, 2007
    Messages:
    1,178
    Likes Received:
    89
    Checked the compiler code, and it seems there is a bug in the way the Database component is escaping double quotes. We'll be fixing this in the next release, which will be very shortly.

    Meanwhile, try with single quotes, which should be the same from the database perspective.

    Kind regards.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  11. Trestill

    Joined:
    Jan 22, 2015
    Messages:
    39
    Likes Received:
    1
    Bad luck. Tried with escaped single quotes and without escape character:
    Code:
    17-09-12 22:00:11.124|100049| Trc|75|0015|: PlugIn[random_cid - DatabaseAccessComponent - CallID JQTHGFMRHEMR] Trace: Command to execute: SELECT * FROM \'CIDs\' WHERE ext = 200 AND last = FALSE;
    17-09-12 22:00:11.516|100030| Err|10|0015|: PlugIn[random_cid - Callflow - MainFlow - CallID JQTHGFMRHEMR] ERROR: Error executing last component: Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "\"
       at Npgsql.NpgsqlConnector.DoReadMessage(DataRowLoadingMode dataRowLoadingMode, Boolean isPrependedMessage)
       at Npgsql.NpgsqlConnector.ReadMessageWithPrepended(DataRowLoadingMode dataRowLoadingMode)
       at Npgsql.NpgsqlConnector.ReadExpecting[T]()
       at Npgsql.NpgsqlDataReader.NextResultInternal()
       at Npgsql.NpgsqlDataReader.NextResult()
       at Npgsql.NpgsqlCommand.Execute(CommandBehavior behavior)
       at Npgsql.NpgsqlCommand.ExecuteDbDataReaderInternal(CommandBehavior behavior)
       at random_cid.DatabaseAccessComponent.executeReader(IDbConnection conn, QMExtendAPI iface, String id)
       at random_cid.DatabaseAccessComponent.executeStatement(IDbConnection conn, QMExtendAPI iface, String id)
       at random_cid.DatabaseAccessComponent.executeStart(QMExtendAPI iface, String id)
       at random_cid.DatabaseAccessComponent.Start(QMExtendAPI iface, CallQueue callQueue, QueueCall queueCall, ActiveConnection activeConnection, TimerManager timerManager, Dictionary`2 variableMap, TempWavFileManager tempWavFileManager, PromptQueue promptQueue)
       at random_cid.Callflow.ProcessStart()
      
    -------------
    
    17-09-12 22:05:00.003|100049| Trc|75|0014|: PlugIn[random_cid - DatabaseAccessComponent - CallID ZPBIOYAUURWH] Trace: Command to execute: SELECT * FROM 'CIDs' WHERE ext = 200 AND last = FALSE
    17-09-12 22:05:00.251|100030| Err|10|0014|: PlugIn[random_cid - Callflow - MainFlow - CallID ZPBIOYAUURWH] ERROR: Error executing last component: Npgsql.PostgresException (0x80004005): 42601: syntax error at or near "'CIDs'"
       at Npgsql.NpgsqlConnector.DoReadMessage(DataRowLoadingMode dataRowLoadingMode, Boolean isPrependedMessage)
       at Npgsql.NpgsqlConnector.ReadMessageWithPrepended(DataRowLoadingMode dataRowLoadingMode)
       at Npgsql.NpgsqlConnector.ReadExpecting[T]()
       at Npgsql.NpgsqlDataReader.NextResultInternal()
       at Npgsql.NpgsqlDataReader.NextResult()
       at Npgsql.NpgsqlCommand.Execute(CommandBehavior behavior)
       at Npgsql.NpgsqlCommand.ExecuteDbDataReaderInternal(CommandBehavior behavior)
       at random_cid.DatabaseAccessComponent.executeReader(IDbConnection conn, QMExtendAPI iface, String id)
       at random_cid.DatabaseAccessComponent.executeStatement(IDbConnection conn, QMExtendAPI iface, String id)
       at random_cid.DatabaseAccessComponent.executeStart(QMExtendAPI iface, String id)
       at random_cid.DatabaseAccessComponent.Start(QMExtendAPI iface, CallQueue callQueue, QueueCall queueCall, ActiveConnection activeConnection, TimerManager timerManager, Dictionary`2 variableMap, TempWavFileManager tempWavFileManager, PromptQueue promptQueue)
       at random_cid.Callflow.ProcessStart()
    I'll try with regular SQL server and let you know
     
  12. edossantos

    edossantos Support Team
    Staff Member 3CX Support

    Joined:
    Jun 27, 2007
    Messages:
    1,178
    Likes Received:
    89
    Single quotes must not be escaped, so the second query is the correct one. It's weird that it's being considered a syntax error... Please let me know what happens with SQL Server.

    Kind regards.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  13. edossantos

    edossantos Support Team
    Staff Member 3CX Support

    Joined:
    Jun 27, 2007
    Messages:
    1,178
    Likes Received:
    89
    Single quotes must not be escaped, so the second query is the correct one. It's weird that it's being considered a syntax error... Please let me know what happens with SQL Server.

    Kind regards.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  14. Trestill

    Joined:
    Jan 22, 2015
    Messages:
    39
    Likes Received:
    1
    Hi, on SQL server (MSSQL to be precise) it works only when I don't use quotes. So my query looks like this:
    Code:
    SELECT * FROM CIDs WHERE ext=200 AND last=0;
    Thanks for your help and I hope for working postgresql version soon.
     
  15. edossantos

    edossantos Support Team
    Staff Member 3CX Support

    Joined:
    Jun 27, 2007
    Messages:
    1,178
    Likes Received:
    89
    Thanks for your feedback. Please keep an eye on the 3CX blog to know when we release a new version of the CFD including the fix to the escaping of double quotes. It will be very soon.

    Kind regards.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Thread Status:
Not open for further replies.