AI Zone Admin Forum Add your forum

NEWS: Chatbots.org survey on 3000 US and UK consumers shows it is time for chatbot integration in customer service!read more..

Unable to connect to the remote Postgres database
 
 

 

Hi
I want to develop chatbot using Chatscript.
Local Postgres database connection succeeded.

However, I do not know how to use the remote postgres database.


Check the ChatScript-PostgresSQL.pdf file

chatscriptpg port = 1024 userlog pguser = “dbname = mydb host = 129.22.22.24 port = 5432 user = postgres password = somepassword

I have successfully run the above script to connect to the Postgres database. (I do not know if it works normally)

After connection, I confirmed the following message.

*** Server WIDE OPEN to: command use. Postgres enabled. FileSystem routed postgress

However, you do not know how to execute a query from a .top file.


u: (test) pguserread = SELECT userdata FROM randomusertable WHERE username = $ 1 :: varchar;


If you run it like this, only text will be displayed.

I would like to see a .top file that loads a query as a sample

plz help

 

 

 
  [ # 1 ]

You dont need postgres to create a CS chatbot.  You can build and deploy it without this. I suggest you build without it. 
CS has database connectivity implemented, but the primary use case is to move the end user files to a shared instance, so you can implement multiple app servers and scale. 
Although it includes database connectivity, this is not the primary focus of the framework.
It looks like you have it working with a remote file system. You dont need this fornit to work. 
Whatever you are working to implement with a database call is probably possible within CS itself.
If it is not, that database call is largely out of scope for CS.
If you need to call a database with SQL, it will return data and you will have to write a routine to work with it, within CS.
Your SQL command will return data, which is what your post shows. 
This data would have to be placed into a variable and then processed by CS.

I suggest you post your use case, so we can be more helpful.  And use CS without Postgres.
Cheers

 

 
  [ # 2 ]

Thank you for your reply.

I have to develop chatbot using remote database.

I want provided for users by real-time data

I was successful when I installed the Postgres database on the same PC and installed CS chatbot.

However, the remote Postgres database connection appears to be successful, but the result can not be retrieved

If you have a sample .top file, please share it.


=================================================================================================

Using Postgres as file server
Aside from using Postgres to store data for a chatbot to look up, one can also use
Postgres as a replacement for the local file system of the USERS directory. This
allows you to scale CS horizontally by having multiple CS servers all connecting
to a common Postgres DB machine so a user can be routed to any server. To do
this, on the ChatScript command line use:
pguser=“host = 129.22.22.24 port = 5432 user = postgres password = somepassword “
If host is omitted, it defaults to localhost. Localhost might not work as the name
of the host, in which case use 127.0.0.1 .
You do not specify the dbname. The system assumes you have a db named
users. If it doesn’t find it, it will try to open a root database named postgres.
If it can find that, it will then create the users db.
Obviously put the correct data for your postgres machine. CS will automatically
create a users database and a userfiles tables. Each user will get an entry in
the userfiles table. If Postgres is not available, the CS server will quit. Hopefully
you have an automatic restart cron job and it will be able to connect to Postgres
the next time.
Update 2017-10-04
You can now specify the name of the postgres db:
1
pguser=“dbname = mydb host = 129.22.22.24 port = 5432 user = postgres password = somepassword If the dbname is specified, the postgres module will not try to create a new
database or a userfiles table. If dbname is not specified, the postgres module
will provide its original behavior.
The postgres code uses the following parameter queries to read, insert, and
update a user record:
—read a user
SELECT file FROM userfiles WHERE userid = $1::varchar ;
—insert a user
INSERT INTO userfiles (file, userid) VALUES ($1::bytea, $2::varchar) ;
—update a user
UPDATE userfiles SET file = $1::bytea WHERE userid = $2::varchar ;
You can override these queries to support alternate schemas. However, the
postgres module assumes that any sql used to override the default queries will
use the same sequence of arguments. For example, assume you want to store
user data in a table named ‘randomusertable.’ The following parameters can be
used to override the default postgres SQL:
pguserread=SELECT userdata FROM randomusertable WHERE username=$1::varchar ;
pguserinsert=INSERT INTO randomusertable (userdata,username) VALUES ($1::bytea, $2::varchar) pguserupdate=UPDATE randomusertable SET userdata = $1::bytea WHERE username = $2::varchar ;
Note that the default and override queries use the same arguments in the same
order.


=================================================================================================

I would like to have a .top file that implements the above description.

 

 
  [ # 3 ]

OK. i just tried this and it worked.

Backup everything to zip in your linux server.  If you don’t do this, I am not responsible.

in postgres directory, open up the simpletopic.top file, modify the line
u: (open)  if (^dbinit( dbname
to reflect your remote database

working example:
u: (open)  if (^dbinit( dbname = users host = 172.31.XX.XX port = 5432 user = XX password = XXX )) {db opened}
    else {dbinit failed - $$db_error}
save
~~~~~~~
at the linux command line, kill the PIDs related to the CS service on your server
pstree -p to get the list
kill -9 # for the chatscript related processes #
example   kill -9 2333

from the SRC directory
rm *.o
then make ChatScriptpgDebug
if this does not work, try
make clean ChatScriptpgDebug

~~~~~~~~~~~

then from the BINARIES directory

./ChatScriptpgDebug pguser=“dbname = users host = xx.xx.xx.xx port = 5432 user = xxx password = xxx” local
hit enter
type and enter
:build 0
type and enter
:build postgres
from here you should have the commands available
start with typing
open
response
POSTGRES:  Db opened
this indicates you are connected to a database

then type in
list
response
POSTGRES:  1\n 1\n 1\n 1\n Completed
this indicates you got data back

so, this will prove you can open a database.
Quit this. Kill the processes you just created.
You can update simpletopic.top file with what you need to get data.

Go back and modify crontab to autostart this process.
Use google to understand how to edit crontab.

Hope this helps!

 

 
  [ # 4 ]

Thanks for your reply.

I did the same thing as you told me, so I succeeded in connecting.

Really thankful.

 

 
  login or register to react