Connecting to ServiceNow ODBC with PHP

[slandi]

Hello folks,

I've been looking in vain on the Internet for several hours now on how to connect to our ServiceNow instance using PHP.

I am in the very early stages of trying to develop a dashboard in PHP/iquery that will run a series of SQL statements against the Incident table primarily and pull some KPIs and place them on a Piechart. I realize that this could be done within the reporting module of ServiceNow, however I would like to be able to run this independantly and produce user-specific views and various mappings for some of the fields and CI categories.

I must admit I'm not as well versed in PHP as I would like, but so far everything I've tried with the ServiceNow ODBC connection returns absolutely nothing, yet when I've tried this with a connection to an MS Access DB it works.

Can anybody perhaps point me to an article or give me a few hints to get me on my way, I would appreciate anything you can share.

thanks,

Stefano

<?

# connect to a DSN "ServiceNow" with a user and password

$connect = odbc_connect("ServiceNow","username","password");

# query the incident table for number

$query = "SELECT number FROM incident";

# perform the query

$result = odbc_exec($connect, $query);

# fetch the data from the database

odbc_fetch_row($result))

$incnum = odbc_result($result);

print("$incnum\n");

# close the connection

odbc_close($connect);

?>

[tony_barratt]

Hi Stephano,

Some hints do spring to mind when reading your post, but bear in mind I have not actually used php and ServiceNow ODBC.

I would try to get it together on the php command line initially, just to make testing a bit easier.

php > $conn = odbc_connect(..

Check the value of $result, is it null perhaps?

You might have more luck if you use the connection string mentioned here:

Configuring the ODBC Driver - ServiceNow Wiki

..

2.3 Using a Connection String

You can specify a connection string instead of defining a DSN. The connection string must follow this format:

Driver=ServiceNow ODBC Driver 32-bit;ServiceName=ServiceNow_ODBC;UID=youruser;PWD=yourpassword;ServerDataSource=ServiceNow;CustomProperties=url=https://<instance>.service-now.com 

The driver name varies depending on if you use the 32-bit or 64-bit version of the ODBC driver. To determine your driver version:

1. In Windows, navigate to Start > Programs > Service-now ODBC > ODBC Administrator.
2. Select the System DSN tab.
3. Note value in the Driver column for the ServiceNow data source.

Best Regards

Tony

[tony_barratt]

Also can   you confirm that you have installed ServiceNow ODBC driver on the same Windows machine you are running the php code on, and have checked with ISQL you can carry out a select * from incident?

Using Interactive SQL With ODBC - ServiceNow Wiki

[pavelmuller]

Hi,

the question might be if the whole PHP-ODBC architecture is correct or if there is a better solution.

We implemented several integrations using PHP and our experience is that it is much better to call the SOAP or REST API directly. It works better to prepare import sets and views and use them directly and with better control. ODBC driver is using the SOAP API anyway.

The second perspective would be the idea of performing reports on your live instance. It might easily happen that every request to your PHP application becomes a set of complicated aggregation queries to your ServiceNow instance. Unnecessary load. The proven idea is to mirror selected data on premise and connect your PHP application to this data source. E.g. having a MySQL database with replicated tables every hour. PHP-MySQL combination works fine.

The replication can be done using open-source tools or a commercial SnowMirror. See https://community.servicenow.com/message/793067.

Regards,

Pavel