Postgresql as Datasource

Go to solution
Madhav9
Kilo Contributor

‎03-06-2019 02:55 AM

Hi All

We are having requirement to get data from postgresql database. We tried to check from the data sources, but failed because servicenow as an OOB doesn't support postgres.


Is there a way to use postgresql as a datasource and run queries on postgresql databases/tables?

0 Helpfuls
  • 7,102 Views
1 ACCEPTED SOLUTION

Go to solution
Duodecimal
Mega Guru

‎03-06-2019 04:41 AM

Yes - you'll need to get the jar file from postgresql that contains the database driver. Navigate to MID Server JAR Files and add a record there for it. (table is ecc_agent_jar), and attach the file there. I got it from https://jdbc.postgresql.org/download.html. ServiceNow will download the driver to your midservers automatically.

Update the data sources form so the choice list for format includes postgresql. It just needs to be an option so you can select it, and also so that you can create business rules / client scripts that define the connection URL. The type will be JDBC. 

I did create a before business rule for when type is JDBC, format is PostgreSQL, and the server, port, or name is changed. The connection URL would be composed like this:

current.connection_url = "jdbc:postgresql://" + current.jdbc_server + ":" + current.database_port + "/" + current.database_name;

Database name, port, table name, server, username, and password are the other fields you'll need to set.

Here's the documentation page for this: JDBC Data Sources

 

View solution in original post

7 REPLIES 7

Go to solution
Diljith
Tera Contributor
In response to Madhav9

‎08-16-2021 05:39 AM

Hi Madhav,

I am also getting the same error.  Could you please help me on how you solved the error?

Regards,

Diljith.D

0 Helpfuls

Go to solution
TP10
Tera Contributor
In response to Madhav9

‎04-17-2021 07:12 AM

Hi Madhav, 

I'm trying the same connection and got the same error. "No suitable driver found for" 

Can you help what exactly the driver that needs to be attached to the jar file and the business rule script as well. 

Can you please provide me with suggestions on achieving this. 

Thanks in advance

0 Helpfuls

Go to solution
jonp
Kilo Explorer

‎10-25-2019 07:31 AM

I have installed the driver, I have created the choice list.  I have added the connection url field and added it. I can connect to the database using another tool.  I get this error; 

MID Server reported error: java.sql.SQLException: org.postgresql.util.PSQLException: FATAL: database "bametadata
" does not exist

Looks like the driver is working, the connection string URL that I am using works in other tools.

jdbc:postgresql://[server]:[port]/bametadata

 

0 Helpfuls