Thread: RE: [Arsperl-users] Remedy Query to SQL
Brought to you by:
jeffmurphy
Menu
▾
▴
arsperl-users
|
From: Covert, J. <Jac...@Mc...> - 2005-04-07 16:04:21
|
How about running the query with SQL logging on? -----Original Message----- From: Bowman, Brent A [mailto:Bow...@st...] Sent: Thursday, April 07, 2005 7:09 AM To: ars...@ar... Subject: [Arsperl-users] Remedy Query to SQL Listers, I am wondering if there is a simple algorithm available to convert a remedy qualification directly to an SQL query that I can use with oracle. Is there something available in arsperl? Thanks, Brent Bowman ------------------------------------------------------- SF email is sponsored by - The IT Product Guide Read honest & candid reviews on hundreds of IT Products from real users. Discover which products truly live up to the hype. Start reading now. http://ads.osdn.com/?ad_ide95&alloc_id396&op=click _______________________________________________ Arsperl-users mailing list Ars...@ar... https://lists.sourceforge.net/lists/listinfo/arsperl-users |
|
From: Bowman, B. A <Bow...@st...> - 2005-04-07 16:56:19
|
I want to get the conversion without ever querying the ARS. I want to = go directly to Oracle, but let the user input a Remedy style query. -----Original Message----- From: ars...@ar... [mailto:ars...@ar...]On Behalf Of Covert, Jack Sent: Thursday, April 07, 2005 12:01 PM To: ars...@ar... Subject: RE: [Arsperl-users] Remedy Query to SQL How about running the query with SQL logging on?=20 -----Original Message----- From: Bowman, Brent A [mailto:Bow...@st...]=20 Sent: Thursday, April 07, 2005 7:09 AM To: ars...@ar... Subject: [Arsperl-users] Remedy Query to SQL Listers, I am wondering if there is a simple algorithm available to convert a = remedy qualification directly to an SQL query that I can use with oracle. Is = there something available in arsperl?=20 Thanks, Brent Bowman ------------------------------------------------------- SF email is sponsored by - The IT Product Guide Read honest & candid reviews on hundreds of IT Products from real users. Discover which products truly live up to the hype. Start reading now. http://ads.osdn.com/?ad_ide95&alloc_id=14396&op=3Dclick _______________________________________________ Arsperl-users mailing list Ars...@ar... https://lists.sourceforge.net/lists/listinfo/arsperl-users ------------------------------------------------------- SF email is sponsored by - The IT Product Guide Read honest & candid reviews on hundreds of IT Products from real users. Discover which products truly live up to the hype. Start reading now. http://ads.osdn.com/?ad_id=3D6595&alloc_id=3D14396&op=3Dclick _______________________________________________ Arsperl-users mailing list Ars...@ar... https://lists.sourceforge.net/lists/listinfo/arsperl-users |
|
From: Michael A. <mic...@gm...> - 2005-04-07 18:11:21
|
On Apr 7, 2005 5:53 PM, Bowman, Brent A <Bow...@st...> wrote: > I want to get the conversion without ever querying the ARS. I want to go= directly to Oracle, but let the user input a Remedy style query. 1.) Turn on SQL Logging on your ARS Server. 2.) Run your query in ARS 3.) Turn SQL Logging off. 4.) Check SQL Log file and you will see the SQL that ARS generated from your ARS Query. Use this for your application. M. > -----Original Message----- > From: ars...@ar... > [mailto:ars...@ar...]On Behalf Of Covert, Jack > Sent: Thursday, April 07, 2005 12:01 PM > To: ars...@ar... > Subject: RE: [Arsperl-users] Remedy Query to SQL >=20 > How about running the query with SQL logging on? >=20 > -----Original Message----- > From: Bowman, Brent A [mailto:Bow...@st...] > Sent: Thursday, April 07, 2005 7:09 AM > To: ars...@ar... > Subject: [Arsperl-users] Remedy Query to SQL >=20 > Listers, >=20 > I am wondering if there is a simple algorithm available to convert a reme= dy > qualification directly to an SQL query that I can use with oracle. Is th= ere > something available in arsperl? >=20 > Thanks, >=20 > Brent Bowman >=20 > ------------------------------------------------------- > SF email is sponsored by - The IT Product Guide > Read honest & candid reviews on hundreds of IT Products from real users. > Discover which products truly live up to the hype. Start reading now. > http://ads.osdn.com/?ad_ide95&alloc_id=14396&op=3Dclick > _______________________________________________ > Arsperl-users mailing list > Ars...@ar... > https://lists.sourceforge.net/lists/listinfo/arsperl-users >=20 > ------------------------------------------------------- > SF email is sponsored by - The IT Product Guide > Read honest & candid reviews on hundreds of IT Products from real users. > Discover which products truly live up to the hype. Start reading now. > http://ads.osdn.com/?ad_id=3D6595&alloc_id=3D14396&op=3Dclick > _______________________________________________ > Arsperl-users mailing list > Ars...@ar... > https://lists.sourceforge.net/lists/listinfo/arsperl-users >=20 > ------------------------------------------------------- > SF email is sponsored by - The IT Product Guide > Read honest & candid reviews on hundreds of IT Products from real users. > Discover which products truly live up to the hype. Start reading now. > http://ads.osdn.com/?ad_ide95&alloc_id=14396&opclick > _______________________________________________ > Arsperl-users mailing list > Ars...@ar... > https://lists.sourceforge.net/lists/listinfo/arsperl-users > |
|
From: Carey M. B. <bla...@gm...> - 2005-04-07 18:37:26
|
Brent, I must say I am a bit confused by what your trying to do.... I would think that you could more simply use the API to do the search (with a Remedy formated query) than to use the API to convert it to an SQL statement then use an SQL connection to do the search. (But maybe you have some reasons for trying to do what you are trying to do?) However, I can imagine a way of converting a Remedy query to an SQL query, but you are not going to like doing it. You have to know a good deal about Remedy's API and it will be error prone. (IMHO.) Your best bet (if you really have to do this) is to use a combination of the following ARSPerl functions: ars_GetFieldTable This will get a hash of Field ID to Field DB name. ars_GetListVUI Get a list of all views for the form in question ars_GetField Get the "displayInstanceList" and further the "dInstanceList" value for the VUI (View) that the query was built for.... You can then map field Label (for the view) to field ID/Field DB name. You will have to build a similar hash to what ars_GetFieldTable returns but based on the Field Labels for the View instead of the Fields DB Name. Then.... Parse the query to translate: * the English field names to field ID's (which nicely map to the Column names on the DB tables.) * The values might have wild cards or other differences in the notation between double quotes and single quotes for your RDBMS syntax. * And likely other things.... But maybe that would help you along your path? -- Carey Matthew Black Remedy Skilled Professional (RSP) ARS = Action Request System(Remedy) Solution = People + Process + Tools Fast, Accurate, Cheap.... Pick two. Never ascribe to malice, that which can be explained by incompetence. http://www.fellowshipchurch.com On Apr 7, 2005 11:53 AM, Bowman, Brent A <Bow...@st...> wrote: > I want to get the conversion without ever querying the ARS. I want to go directly to Oracle, but let the user input a Remedy style query. > > -----Original Message----- > From: ars...@ar... > [mailto:ars...@ar...]On Behalf Of Covert, Jack > Sent: Thursday, April 07, 2005 12:01 PM > To: ars...@ar... > Subject: RE: [Arsperl-users] Remedy Query to SQL > > How about running the query with SQL logging on? > > -----Original Message----- > From: Bowman, Brent A [mailto:Bow...@st...] > Sent: Thursday, April 07, 2005 7:09 AM > To: ars...@ar... > Subject: [Arsperl-users] Remedy Query to SQL > > Listers, > > I am wondering if there is a simple algorithm available to convert a remedy > qualification directly to an SQL query that I can use with oracle. Is there > something available in arsperl? > > Thanks, > > Brent Bowman |
|
From: Bowman, B. A <Bow...@st...> - 2005-04-07 18:17:20
|
Is this the only way? How does remedy do it. And how do I turn logging = on from the same perl script? Also, how do I know that the log entry is = from my query and not from another users' request? Really, the = algorithm would be better here. -----Original Message----- From: ars...@ar... [mailto:ars...@ar...]On Behalf Of Michael Armbrecht Sent: Thursday, April 07, 2005 2:10 PM To: ars...@ar... Subject: Re: [Arsperl-users] Remedy Query to SQL On Apr 7, 2005 5:53 PM, Bowman, Brent A <Bow...@st...> wrote: > I want to get the conversion without ever querying the ARS. I want to = go directly to Oracle, but let the user input a Remedy style query. 1.) Turn on SQL Logging on your ARS Server. 2.) Run your query in ARS 3.) Turn SQL Logging off. 4.) Check SQL Log file and you will see the SQL that ARS generated from your ARS Query. Use this for your application. M. > -----Original Message----- > From: ars...@ar... > [mailto:ars...@ar...]On Behalf Of Covert, Jack > Sent: Thursday, April 07, 2005 12:01 PM > To: ars...@ar... > Subject: RE: [Arsperl-users] Remedy Query to SQL >=20 > How about running the query with SQL logging on? >=20 > -----Original Message----- > From: Bowman, Brent A [mailto:Bow...@st...] > Sent: Thursday, April 07, 2005 7:09 AM > To: ars...@ar... > Subject: [Arsperl-users] Remedy Query to SQL >=20 > Listers, >=20 > I am wondering if there is a simple algorithm available to convert a = remedy > qualification directly to an SQL query that I can use with oracle. Is = there > something available in arsperl? >=20 > Thanks, >=20 > Brent Bowman >=20 > ------------------------------------------------------- > SF email is sponsored by - The IT Product Guide > Read honest & candid reviews on hundreds of IT Products from real = users. > Discover which products truly live up to the hype. Start reading now. > http://ads.osdn.com/?ad_ide95&alloc_id=14396&op=3Dclick > _______________________________________________ > Arsperl-users mailing list > Ars...@ar... > https://lists.sourceforge.net/lists/listinfo/arsperl-users >=20 > ------------------------------------------------------- > SF email is sponsored by - The IT Product Guide > Read honest & candid reviews on hundreds of IT Products from real = users. > Discover which products truly live up to the hype. Start reading now. > http://ads.osdn.com/?ad_id=3D6595&alloc_id=3D14396&op=3Dclick > _______________________________________________ > Arsperl-users mailing list > Ars...@ar... > https://lists.sourceforge.net/lists/listinfo/arsperl-users >=20 > ------------------------------------------------------- > SF email is sponsored by - The IT Product Guide > Read honest & candid reviews on hundreds of IT Products from real = users. > Discover which products truly live up to the hype. Start reading now. > http://ads.osdn.com/?ad_ide95&alloc_id=14396&opclick > _______________________________________________ > Arsperl-users mailing list > Ars...@ar... > https://lists.sourceforge.net/lists/listinfo/arsperl-users > ------------------------------------------------------- SF email is sponsored by - The IT Product Guide Read honest & candid reviews on hundreds of IT Products from real users. Discover which products truly live up to the hype. Start reading now. http://ads.osdn.com/?ad_ide95&alloc_id=14396&op=3Dclick _______________________________________________ Arsperl-users mailing list Ars...@ar... https://lists.sourceforge.net/lists/listinfo/arsperl-users |
|
From: Jeff M. <jcm...@je...> - 2005-04-07 18:43:22
|
On Thu, 2005-04-07 at 14:16 -0400, Bowman, Brent A wrote:
> Is this the only way? How does remedy do it. And how do I turn logging on from the same perl script? Also, how do I know that the log entry is from my query and not from another users' request? Really, the algorithm would be better here.
you can examine a query and derive the algorithm. i think they replace
spaces and non-alphanum characters in field and form names with
underscores. some forms are special ("User" is "USER_X").
so if you have "My Form #1" and it contains a field "Phone #" and
"Entry-Id". the view should be "MY_FORM__1" and it should contain
"PHONE__" and "ENTRY_ID".
you'll need to grant your oracle users permission to query the view.
jeff
|
|
From: Jeff M. <jcm...@je...> - 2005-04-07 18:50:19
|
On Thu, 2005-04-07 at 14:43 -0400, Jeff Murphy wrote: > On Thu, 2005-04-07 at 14:16 -0400, Bowman, Brent A wrote: > > Is this the only way? How does remedy do it. And how do I turn logging on from the same perl script? Also, how do I know that the log entry is from my query and not from another users' request? Really, the algorithm would be better here. > > > you can examine a query and derive the algorithm. i should've said "you can examine the view and derive the algorithm" |
|
From: Bowman, B. A <Bow...@st...> - 2005-04-07 19:05:20
|
Thanks Jeff. I guess I'll have to do it myself. I was hoping that it =
was done before :)
Brent
-----Original Message-----
From: ars...@ar...
[mailto:ars...@ar...]On Behalf Of Jeff Murphy
Sent: Thursday, April 07, 2005 2:43 PM
To: ars...@ar...
Subject: RE: [Arsperl-users] Remedy Query to SQL
On Thu, 2005-04-07 at 14:16 -0400, Bowman, Brent A wrote:
> Is this the only way? How does remedy do it. And how do I turn =
logging on from the same perl script? Also, how do I know that the log =
entry is from my query and not from another users' request? Really, the =
algorithm would be better here.
you can examine a query and derive the algorithm. i think they replace
spaces and non-alphanum characters in field and form names with
underscores. some forms are special ("User" is "USER_X").=20
so if you have "My Form #1" and it contains a field "Phone #" and
"Entry-Id". the view should be "MY_FORM__1" and it should contain
"PHONE__" and "ENTRY_ID".
you'll need to grant your oracle users permission to query the view.
jeff
-------------------------------------------------------
SF email is sponsored by - The IT Product Guide
Read honest & candid reviews on hundreds of IT Products from real users.
Discover which products truly live up to the hype. Start reading now.
http://ads.osdn.com/?ad_id=3D6595&alloc_id=3D14396&op=3Dclick
_______________________________________________
Arsperl-users mailing list
Ars...@ar...
https://lists.sourceforge.net/lists/listinfo/arsperl-users
|
|
From: Bowman, B. A <Bow...@st...> - 2005-04-07 19:06:19
|
Thanks Carey, That is very similar to what I thought I'd need to do. I was just = hoping that I wouldn't :) -----Original Message----- From: ars...@ar... [mailto:ars...@ar...]On Behalf Of Carey Matthew Black Sent: Thursday, April 07, 2005 2:19 PM To: ars...@ar... Subject: Re: [Arsperl-users] Remedy Query to SQL Brent, I must say I am a bit confused by what your trying to do.... I would think that you could more simply use the API to do the search (with a Remedy formated query) than to use the API to convert it to an SQL statement then use an SQL connection to do the search. (But maybe you have some reasons for trying to do what you are trying to do?) However, I can imagine a way of converting a Remedy query to an SQL query, but you are not going to like doing it. You have to know a good deal about Remedy's API and it will be error prone. (IMHO.) Your best bet (if you really have to do this) is to use a combination of the following ARSPerl functions: ars_GetFieldTable This will get a hash of Field ID to Field DB name. ars_GetListVUI Get a list of all views for the form in question ars_GetField Get the "displayInstanceList" and further the "dInstanceList" value for the VUI (View) that the query was built for.... You can then map field Label (for the view) to field ID/Field DB name. You will have to build a similar hash to what ars_GetFieldTable returns but based on the Field Labels for the View instead of the Fields DB Name. Then.... Parse the query to translate: * the English field names to field ID's (which nicely map to the Column names on the DB tables.) * The values might have wild cards or other differences in the notation between double quotes and single quotes for your RDBMS syntax. * And likely other things.... But maybe that would help you along your path? --=20 Carey Matthew Black Remedy Skilled Professional (RSP) ARS =3D Action Request System(Remedy) Solution =3D People + Process + Tools Fast, Accurate, Cheap.... Pick two. Never ascribe to malice, that which can be explained by incompetence. http://www.fellowshipchurch.com On Apr 7, 2005 11:53 AM, Bowman, Brent A <Bow...@st...> wrote: > I want to get the conversion without ever querying the ARS. I want to = go directly to Oracle, but let the user input a Remedy style query. >=20 > -----Original Message----- > From: ars...@ar... > [mailto:ars...@ar...]On Behalf Of Covert, Jack > Sent: Thursday, April 07, 2005 12:01 PM > To: ars...@ar... > Subject: RE: [Arsperl-users] Remedy Query to SQL >=20 > How about running the query with SQL logging on? >=20 > -----Original Message----- > From: Bowman, Brent A [mailto:Bow...@st...] > Sent: Thursday, April 07, 2005 7:09 AM > To: ars...@ar... > Subject: [Arsperl-users] Remedy Query to SQL >=20 > Listers, >=20 > I am wondering if there is a simple algorithm available to convert a = remedy > qualification directly to an SQL query that I can use with oracle. Is = there > something available in arsperl? >=20 > Thanks, >=20 > Brent Bowman ------------------------------------------------------- SF email is sponsored by - The IT Product Guide Read honest & candid reviews on hundreds of IT Products from real users. Discover which products truly live up to the hype. Start reading now. http://ads.osdn.com/?ad_id=3D6595&alloc_id=3D14396&op=3Dclick _______________________________________________ Arsperl-users mailing list Ars...@ar... https://lists.sourceforge.net/lists/listinfo/arsperl-users |
Thanks for helping keep SourceForge clean.
X