• V20: 3CX Re-engineered. Get V20 for increased security, better call management, a new admin console and Windows softphone. Learn More.

postgreSQL db access fail

Status
Not open for further replies.

Trestill

Platinum Distributor
Advanced Certified
Joined
Jan 22, 2015
Messages
61
Reaction score
6
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
 
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:
SELECT *
FROM `CID`
WHERE `ext` = 200
AND `last` = FALSE

The quotes you're using are not the regular ones, and that might cause the issue. I would try with the following query:
SELECT *
FROM 'CID'
WHERE 'ext' = 200
AND 'last' = FALSE

Kind regards,
 
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
 
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.
 
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
 
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.
 
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
 
Use the back slash:
SELECT * FROM \"CIDs\" WHERE ext = {0} AND last = FALSE
 
I tried that:
upload_2017-9-12_15-21-59.png
 
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.
 
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
 
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.
 
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.
 
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.
 
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.
 
Status
Not open for further replies.

Getting Started - Admin

Latest Posts

Forum statistics

Threads
141,635
Messages
748,985
Members
144,753
Latest member
ChristianNetron
Get 3CX - Absolutely Free!

Link up your team and customers Phone System Live Chat Video Conferencing

Hosted or Self-managed. Up to 10 users free forever. No credit card. Try risk free.

3CX
A 3CX Account with that email already exists. You will be redirected to the Customer Portal to sign in or reset your password if you've forgotten it.