Share This:

Yeah we're... Monday

 

I so sad.jpg

 

Hello all, fans of Broncos, Patriots, Seahawks or (sadly) 49ers...

Yesterday was a bitter day with an interception launched by the 49ers in the end zone in the last 30 seconds ending with the victory of the Seahawks

(Yes you can be French and follow a bit NFL...)

So well... As I'm France I went to sleep at 4 AM, not really pleased by the end result, hence this morning I feel... like...

need_more_coffee_by_ramy-d50pmjg.jpg

 

 

Anywayyyyy, as  you maybe know, you can connect to an ARS system using an ODBC driver, bundled with the Remedy User and it's quite useful to connect and fetch / query data from some sources like external software like Excel for example without having to code an interface in C or Java (or other awesome languages Remedy AR System API and Integration Interfaces Overview (C, Java, .NET, Web Services, email, Ruby, Jython, VB, direct SQL)).

 

As you may also know, Remedy User is dying... BMC is trying to slay it from the face of the earth and replace it by 100% Web interface (Mid-Tier).

Let's not really talk here if it's a good idea or not, there are some sad threads crying about it here, but the general idea is to Get Over It - WWRUG12 song.

 

So well, there were some questions about the future of ODBC. Fear not! ODBC is dying too

sad-oh-no-l.jpg

 

 

But BMC is coding a replacement, a JDBC driver (BMC Remedy AR System JDBC Driver 1.0.2).

<troll>

Let's not talk too much about Java since I'm not really in love with Java.. I learned coding with other language (who said real?) like C or C++ (which some consider already not like a real language) and frankly I'm always a bit reluctant coding with Java...

Java is a bit like a teenager language for me... You are messy and so your mom (garbage collector) cleans after your mess

So yeah, she's a cool mom and stuff since you can do amazing things, but sometimes C or C++ is just so damn faster.

</troll>

 

Ok now that the troll is quoted, let's compare the JDBC driver to the ODBC driver and see if it's better, or what is different...

 

 

I°) Does Excel work with JDBC driver?:

No. Excel works with ODBC sadly (at least in the versions I have) or easily at least...

 

But you could use something else, like Libre Office, just follow this tutorial for Oracle, it's kinda the same (http://turingsman.net/my-blog-list/161-part-13-libreoffice-base-to-oracle-database-11gr2-through-jdbc) so well I'll go a bit fast

 

You need to copy the three files in a folder (see why in the pdf delivered with the JDBC driver, the other two jars are from your ARS server):

02.png

 

Launch LibreOffice and go to "Tools->Options->Advanced->Classpath" and add in the classpath the folder AND each file:

05.png

 

Restart LibreOffice.

 

When you click on the "libreoffice database", select a "JDBC" connection:

04.png

 

Enter the connection information and test if the class exists:

jdbc chain:

arserver://192.168.0.23:3500;user=Demo;password=Demo

 

Where:

  • 192.168.0.23=IP of your ars server (or DNS)
  • 3500: TCP port of your ars server,
  • Demo=username you want to connect with
  • Demo=password of the user you want to connect with

 

The jdbc class is: com.bmc.arsys.jdbc.core.Driver

03.png

 

Then repeat again the user that will be used for connection, here "Demo" and it needs a password, I clicked on "test connection" to test the connection:

07.png

 

And it worked:

08.png

 

Save and complete the connection, it'll open the database software and you can access the tables

09.png

 

 

II°) Ok so I can connect, but is JDBC better than ODBC?:

For this, we'll just compare how both behave with the same SQL queries...

I coded a tool where you can access both ODBC and JDBC and then I capture API+SQL on the ARS server.

10.png

 

1°) select "InstanceId" from "HPD:Help Desk" where ("InstanceId" LIKE 'IDGAA%'):

ODBC:

+GLEWF  ARGetListEntryWithFields -- schema HPD:Help Desk from ARODBC (protocol 14) at IP address 192.168.0.14

SELECT T2115.C1,C179 FROM T2115 WHERE (T2115.C179 LIKE 'IDGAA%') ORDER BY C1000000161 DESC, 1 ASC

OK

-GLEWF            OK

 

JDBC:

+GLEWMSF  ARGetListEntryWithMultiSchemaFields -- from Unidentified Client (protocol 18) at IP address 192.168.0.14

SELECT J0.C179, J0.C1 FROM T2115 J0 WHERE (J0.C179 LIKE 'IDGAA%') ORDER BY J0.C1000000161 DESC, J0.C1 ASC

OK

-GLEWMSF           OK

 

Queries are kinda the same, but API calls are different. Results are the same, but JDBC adds an extra order on C1.

 

 

2°) select count("InstanceId") from "HPD:Help Desk" where ("InstanceId" NOT LIKE 'IDGAA%'):

ODBC:

+GLEWF  ARGetListEntryWithFields -- schema HPD:Help Desk from ARODBC (protocol 14) at IP address 192.168.0.14

SELECT T2115.C1,C179 FROM T2115 WHERE NOT ((T2115.C179 LIKE 'IDGAA%')) ORDER BY C1000000161 DESC, 1 ASC

OK

-GLEWF            OK

 

JDBC:

+GLEWMSF  ARGetListEntryWithMultiSchemaFields -- from Unidentified Client (protocol 18) at IP address 192.168.0.14

SELECT J0.C179, J0.C1 FROM T2115 J0 WHERE NOT ((J0.C179 LIKE 'IDGAA%')) ORDER BY J0.C1000000161 DESC, J0.C1 ASC

OK

-GLEWMSF           OK

 

Queries are kinda the same, but API calls are different. Results are the same, but JDBC adds an extra order on C1.

 

Note:

In its version 1.0.0, JDBC driver didn't work with "NOT LIKE", it's working now in 1.0.1

 

 

3°) select "InstanceId" from "HPD:Help Desk" where NOT ("InstanceId" LIKE 'IDGAA%'):

ODBC:

+GLEWF  ARGetListEntryWithFields -- schema HPD:Help Desk from ARODBC (protocol 14) at IP address 192.168.0.14

SELECT T2115.C1,C179 FROM T2115 WHERE NOT ((T2115.C179 LIKE 'IDGAA%')) ORDER BY C1000000161 DESC, 1 ASC

OK

-GLEWF            OK

 

JDBC:

+GLEWMSF  ARGetListEntryWithMultiSchemaFields -- from Unidentified Client (protocol 18) at IP address 192.168.0.14

SELECT J0.C179, J0.C1 FROM T2115 J0 ORDER BY J0.C1000000161 DESC, J0.C1 ASC

OK

-GLEWMSF           OK

 

Here JDBC doesn't do a "NOT" but fetches all the data ODBC generates the right query though.

Results are NOT the same between ODBC and JDBC.

 

 

4°) select count("InstanceId") from "HPD:Help Desk" where ("InstanceId" LIKE 'IDGAA%'):

ODBC:

+GLEWF  ARGetListEntryWithFields -- schema HPD:Help Desk from ARODBC (protocol 14) at IP address 192.168.0.14

SELECT T2115.C1,C179 FROM T2115 WHERE (T2115.C179 LIKE 'IDGAA%') ORDER BY C1000000161 DESC, 1 ASC

OK

-GLEWF            OK

 

JDBC:

+GLEWMSF  ARGetListEntryWithMultiSchemaFields -- from Unidentified Client (protocol 18) at IP address 192.168.0.14

SELECT COUNT(J0.C179) FROM T2115 J0 WHERE (J0.C179 LIKE 'IDGAA%')

OK

-GLEWMSF           OK

 

Here JDBC uses "Count", the ODBC didn't and fetched all records, the ARS server (?) had to do the count itself and I guess it was very bad performance wide...

 

 

5°) select "InstanceId" from "HPD:Help Desk" where "InstanceId"='AG00123F73CF5Eqc4TSQvOExAgYUQB':

ODBC:

+GLEWF  ARGetListEntryWithFields -- schema HPD:Help Desk from ARODBC (protocol 14) at IP address 192.168.0.14

SELECT T2115.C1,C179 FROM T2115 WHERE (T2115.C179 = 'AG00123F73CF5Eqc4TSQvOExAgYUQB') ORDER BY C1000000161 DESC, 1 ASC

OK

-GLEWF            OK

 

JDBC:

+GLEWMSF  ARGetListEntryWithMultiSchemaFields -- from Unidentified Client (protocol 18) at IP address 192.168.0.14

SELECT J0.C179, J0.C1 FROM T2115 J0 WHERE (J0.C179 = 'AG00123F73CF5Eqc4TSQvOExAgYUQB') ORDER BY J0.C1000000161 DESC, J0.C1 ASC

OK

-GLEWMSF           OK

 

Queries are kinda the same and results are the same.

 

 

6°) select count("InstanceId") from "HPD:Help Desk" where "InstanceId"='AG00123F73CF5Eqc4TSQvOExAgYUQB':

ODBC:

+GLEWF  ARGetListEntryWithFields -- schema HPD:Help Desk from ARODBC (protocol 14) at IP address 192.168.0.14

SELECT T2115.C1,C179 FROM T2115 WHERE (T2115.C179 = 'AG00123F73CF5Eqc4TSQvOExAgYUQB') ORDER BY C1000000161 DESC, 1 ASC

OK

-GLEWF            OK

 

JDBC:

+GLEWMSF  ARGetListEntryWithMultiSchemaFields -- from Unidentified Client (protocol 18) at IP address 192.168.0.14

SELECT COUNT(J0.C179) FROM T2115 J0 WHERE (J0.C179 = 'AG00123F73CF5Eqc4TSQvOExAgYUQB')

OK

-GLEWMSF           OK

 

Here JDBC uses "Count", the ODBC didn't and fetched all records, the ARS server (?) had to do the count itself and I guess it was very bad performance wide...

 

 

7°) select "Incident Number" from "HPD:Help Desk" where ("InstanceId" != 'AG00123F73CF5Eqc4TSQvOExAgYUQB') and ("Incident Number" NOT LIKE 'INC_CAL_%'):

ODBC doesn't handle "!=" and so the query had to be different:

select "Incident Number" from "HPD:Help Desk" where (NOT("InstanceId" = 'AG00123F73CF5Eqc4TSQvOExAgYUQB')) and ("Incident Number" NOT LIKE 'INC_CAL_%')






 

ODBC:

+GLEWF  ARGetListEntryWithFields -- schema HPD:Help Desk from ARODBC (protocol 14) at IP address 192.168.0.14

SELECT T2115.C1,C1000000161 FROM T2115 WHERE (NOT ((T2115.C179 = 'AG00123F73CF5Eqc4TSQvOExAgYUQB')) AND NOT ((T2115.C1000000161 LIKE 'INC_CAL_%'))) ORDER BY C1000000161 DESC, 1 ASC

OK

-GLEWF            OK

 

JDBC:

+GLEWMSF  ARGetListEntryWithMultiSchemaFields -- from Unidentified Client (protocol 18) at IP address 192.168.0.14

SELECT J0.C1000000161, J0.C1 FROM T2115 J0 WHERE ((J0.C179 != 'AG00123F73CF5Eqc4TSQvOExAgYUQB') AND NOT ((J0.C1000000161 LIKE 'INC_CAL_%'))) ORDER BY J0.C1000000161 DESC, J0.C1 ASC

OK

-GLEWMSF           OK

 

Queries are not the same since ODBC doesn't handle "!=" but results are the same once the query is adapted.

 

 

8°) select "Incident Number" from "HPD:Help Desk" where ("InstanceId" != 'AG00123F73CF5Eqc4TSQvOExAgYUQB') or ("Incident Number" NOT LIKE 'INC_CAL_%'):

ODBC doesn't handle "!=" and so the query had to be different:

select "Incident Number" from "HPD:Help Desk" where (NOT ("InstanceId" = 'AG00123F73CF5Eqc4TSQvOExAgYUQB')) or ("Incident Number" NOT LIKE 'INC_CAL_%')






 

ODBC:

+GLEWF  ARGetListEntryWithFields -- schema HPD:Help Desk from ARODBC (protocol 14) at IP address 192.168.0.14

SELECT T2115.C1,C1000000161 FROM T2115 WHERE (NOT ((T2115.C179 = 'AG00123F73CF5Eqc4TSQvOExAgYUQB')) OR NOT ((T2115.C1000000161 LIKE 'INC_CAL_%'))) ORDER BY C1000000161 DESC, 1 ASC

OK

-GLEWF            OK

 

JDBC:

+GLEWMSF  ARGetListEntryWithMultiSchemaFields -- from Unidentified Client (protocol 18) at IP address 192.168.0.14

SELECT J0.C1000000161, J0.C1 FROM T2115 J0 WHERE ((J0.C179 != 'AG00123F73CF5Eqc4TSQvOExAgYUQB') OR NOT ((J0.C1000000161 LIKE 'INC_CAL_%'))) ORDER BY J0.C1000000161 DESC, J0.C1 ASC

OK

-GLEWMSF           OK

 

Queries are not the same since ODBC doesn't handle "!=" but results are the same once the query is adapted.

 

 

9°) select "Incident Number" from "HPD:Help Desk" where ("InstanceId" != 'AG00123F73CF5Eqc4TSQvOExAgYUQB') or ("Incident Number"='INC_CAL_%'):

ODBC doesn't handle "!=" and so the query had to be different:

select "Incident Number" from "HPD:Help Desk" where (NOT ("InstanceId" = 'AG00123F73CF5Eqc4TSQvOExAgYUQB')) or ("Incident Number"='INC_CAL_%')

 

ODBC:

+GLEWF  ARGetListEntryWithFields -- schema HPD:Help Desk from ARODBC (protocol 14) at IP address 192.168.0.14

SELECT T2115.C1,C1000000161 FROM T2115 WHERE (NOT ((T2115.C179 = 'AG00123F73CF5Eqc4TSQvOExAgYUQB')) OR (T2115.C1000000161 = 'INC_CAL_%')) ORDER BY C1000000161 DESC, 1 ASC

OK

-GLEWF            OK

 

JDBC:

+GLEWMSF  ARGetListEntryWithMultiSchemaFields -- from Unidentified Client (protocol 18) at IP address 192.168.0.14

SELECT J0.C1000000161, J0.C1 FROM T2115 J0 WHERE ((J0.C179 != 'AG00123F73CF5Eqc4TSQvOExAgYUQB') OR (J0.C1000000161 = 'INC_CAL_%')) ORDER BY J0.C1000000161 DESC, J0.C1 ASC

OK

-GLEWMSF           OK

 

Queries are not the same since ODBC doesn't handle "!=" but results are the same once the query is adapted.

 

 

10°) (JOIN!) select "CTM:People Permission Groups"."Person ID" from "CTM:People Permission Groups", "CTM:People" where "CTM:People Permission Groups"."Remedy Login ID"="CTM:People"."Remedy Login ID" AND "CTM:People"."Remedy Login ID"='amelie':

 

ODBC:

Joins are not supported.

 

JDBC:

This doesn't work "as is", as noted Curtis Gallant in the comment, you need to adapt the query using ANSI joins:

select A."Person_ID" from "CTM:People Permission Groups" as A INNER JOIN "CTM:People" as B ON A."Remedy Login ID" = B."Remedy Login ID" AND B."Remedy Login ID"='amelie'

 

+GLEWMSF  ARGetListEntryWithMultiSchemaFields -- from Unidentified Client (protocol 20) at IP address 192.168.0.14  // :q:0.0s

SELECT J0.C1000000080, J0.C1 FROM T1132 J0 INNER JOIN T1334 J1 ON ((J1.C4 = J0.C4) AND (J1.C4 = N'amelie')) ORDER BY J0.C4 ASC, J0.C1000001578 ASC, J0.C1 ASC

OK

-GLEWMSF           OK

 

The SQL query is correct but you may need to rewrite your previous "lazy" queries.

 

 

11°) (JOIN!) select count("CTM:People Permission Groups"."Person ID") from "CTM:People Permission Groups", "CTM:People" where "CTM:People Permission Groups"."Remedy Login ID"="CTM:People"."Remedy Login ID" AND "CTM:People"."Remedy Login ID"='amelie':

ODBC:

Joins are not supported.

 

JDBC:

This doesn't work "as is", as noted Curtis Gallant in the comment, you need to adapt the query using ANSI joins:

select count(A."Person_ID") from "CTM:People Permission Groups" as A INNER JOIN "CTM:People" as B ON A."Remedy Login ID" = B."Remedy Login ID" AND B."Remedy Login ID"='amelie'

 

+GLEWMSF  ARGetListEntryWithMultiSchemaFields -- from Unidentified Client (protocol 20) at IP address 192.168.0.14  // :q:0.0s

SELECT COUNT(J0.C1000000080) FROM T1132 J0 INNER JOIN T1334 J1 ON ((J1.C4 = J0.C4) AND (J1.C4 = N'amelie'))

OK

-GLEWMSF           OK

 

The SQL query is correct but you may need to rewrite your previous "lazy" queries.

 

 

III°) So differences?:

  • ODBC generates a "GLEWF  ARGetListEntryWithFields " call, JDBC a "GLEWMSF  ARGetListEntryWithMultiSchemaFields" call,
  • Count() is much better on JDBC since it's a real Count() keyword sent to the database,
  • There is still a case (II°) 3°)) where JDBC doesn't generate the correct SQL query,
  • ODBC doesn't handle "!=", JDBC does,
  • You can use easily ODBC in Excel, not JDBC,
  • We still can't do "join" sql queries using ODBC or JDBC , for example: (see the note).
    • select "CTM:People Permission Groups"."Person ID" from "CTM:People Permission Groups", "CTM:People" where "CTM:People Permission Groups"."Remedy Login ID"="CTM:People"."Remedy Login ID"

 

 

so overall JDBC driver is certainly promising, yet you have to be careful since it's still a WIP

Though handling joins is a very big advantage...

 

Note:

It is actually possible to use joins, see Curtis Gallant comment below and new examples 10°) and 11°).

You need to write them the "ANSI" way.

 

 

IV°) Network efficiency:

It also seems that the JDBC is less greedy than ODBC.

To fetch 8516 records using this query:

 

select "Message Identifier" from "AR System Message Catalog"



 

  • JDBC downloaded 170 kB,
  • ODBC ARS downloaded 258 kB,
  • ODBC SQL Server downloaded 300 kB,

 

I quickly looked at the packets:

11.png

 

It seems JDBC doesn't get some field list, view list, permission / group list and some other stuff. I didn't go too much into details...

It seems also that the answer "data packet" itself less important.

 

Was around 8 kB for JDBC, 11.5 kB for ODBC. I wonder if they changed something on this as well.

 

 

 

V°) What would be cool?

Join SQL Queries.

 

Note:

It is actually possible to use joins, see Curtis Gallant comment below and new examples 10°) and 11°).

You need to write them the "ANSI" way.

 

please.jpg