Horizon* Specific Information for JTURL

 

Version 1.0g and higher

 

Be sure and enter a Default-Server under Setup. This can be any valid web server. I recommend that you use your own local server. This will give the utility a valid server it can use as the server address if the data from the 856 tag is gibberish. These type of links should still report as errors but this will help avoid some fatal problems. 

This version will only work with an ODBC connection.  Using isql.exe or sqladv.exe directly is no longer an option.  You can of course, use those tools to retrieve the URLs outside of JTURL.  The URL file should be TAB delimited and contain the bib#(or other key field) and the URL, in that order.  The default script in JTURL has a bib# limitation for test purposes.

The defaults on the other fields should suffice in most cases.  See the Help option for more information.

Note: In pre-Horizon 7.2 the tags were small integers rather than a Character datatype.  This requires that you remove the quotes from the 856's for pre-Horizon 7.2  ex. 856 rather than "856".

Jim Taylor

 

Version 1.0f and lower


Click for RPA Information

In case you do not have one, I have included a script which will pull subfield "u" from the 856 tag and the bib number for use with JTURL. You will need to update the database name in the "use" statement. 

Also, case is important for the database parameters on the setup screen. The ones that you will want to use are -S server, -D database, -U user, -P password, -i input (source) file, -o output file, -w width (under additional parameters). Only the -i and -o are required in their respective boxes. It pulls the name from the Script File box when it executes the script.

Be sure and enter a Default-Server. This can be any valid web server. I recommend that you use your own local server. This will give the utility a valid server it can use as the server address if the data from the 856 tag is gibberish. These type of links should still report as errors but this will help avoid some fatal problems.

Jim Taylor

Note: In pre-Horizon 7.2 the tags were small integers rather than a Character datatype.  This requires that you remove the quotes from the 856's below  ex. 856 rather than "856".

Sybase & JTURL Version 1.0g and later
(MSSQL below)


use horizon
set nocount on
go

-- If the subfield "u" continues past the 255th column it will be truncated and, most likely, JTURL will not return 
-- an accurate response code when verifying the link. This is an ISQL limitation.

declare url_curs cursor for 
select bib#, text
from bib 
where tag = '856' 
and text IS NOT NULL
union
select  bib#, convert(varchar(255),longtext)
from bib_longtext 
where tag = '856' 
go

open url_curs
declare @url varchar(255), @bib# integer, @tab char(1)
select @tab = char(9)


fetch url_curs into @bib#, @url
while @@sqlstatus = 0 begin

if (select charindex(char(31)+"u", @url)) > 0 
select @url = SubString(@url,charindex(char(31)+"u",@url)+2, 255)
else
select @url = "http://i.dont.see/A_SUBFIELD_U.html" 

if (select charindex(char(31), @url)) > 0 select @url = SubString(@url,1, charindex(char(31), @url)-1)
if (select charindex(char(30), @url)) > 0 select @url = SubString(@url,1, charindex(char(30), @url)-1)

select @url = Rtrim(@url)
print "%1!%2!%3!", @bib#, @url

fetch url_curs into @bib#, @url
end /* while */

Deallocate Cursor url_curs

go

 

Sybase & JTURL Version 1.0f and before
(MSSQL below)


use horizon
set nocount on
go

-- If the subfield "u" continues past the 255th column it will be truncated and, most likely, JTURL will not return 
-- an accurate response code when verifying the link. This is an ISQL limitation.

declare url_curs cursor for 
select text, bib#
from bib 
where tag = "856" 
and text IS NOT NULL
union
select convert(varchar(255),longtext), bib# 
from bib_longtext 
where tag = "856" 
go

open url_curs
declare @url varchar(255), @bib# integer

fetch url_curs into @url, @bib#
while @@sqlstatus = 0 begin

if (select charindex(char(31)+"u", @url)) > 0 
select @url = SubString(@url,charindex(char(31)+"u",@url)+2, 255)
else
select @url = "http://i.dont.see/A_SUBFIELD_U.html" 

if (select charindex(char(31), @url)) > 0 select @url = SubString(@url,1, charindex(char(31), @url)-1)
if (select charindex(char(30), @url)) > 0 select @url = SubString(@url,1, charindex(char(30), @url)-1)

select @url = Rtrim(@url)
print "%1!|%2!", @url, @bib#

fetch url_curs into @url, @bib#
end /* while */

Deallocate Cursor url_curs

go

 

MSSQL & JTURL Version 1.0g and later

use horizon
set nocount on
go

-- If the subfield "u" continues past the 255th column it will be truncated and, most likely, JTURL will not return 
-- an accurate response code when verifying the link. This is an ISQL limitation.

declare url_curs cursor for 
select bib#, text
from bib 
where tag = '856'
and text IS NOT  NULL
union
select bib#, convert(varchar(255),longtext)  
from bib_longtext 
where tag = '856'


open url_curs

declare @url varchar(255), @bib# integer,
@curs_cnt integer,
@curs_chk integer,
@tab char(1)
select @curs_cnt = @@cursor_rows
select @curs_chk = 0
select @tab = char(9)

fetch url_curs into @bib#, @url
while @curs_chk < @curs_cnt

begin

select @curs_chk = @curs_chk + 1

if (select charindex(char(31)+'u', @url)) > 0 
select @url = SubString(@url,charindex(char(31)+'u',@url)+2, 255)
else
select @url = 'http://i.dont.see/A_SUBFIELD_U.html'

if (select charindex(char(31), @url)) > 0 select @url = SubString(@url,1, charindex(char(31), @url)-1)
if (select charindex(char(30), @url)) > 0 select @url = SubString(@url,1, charindex(char(30), @url)-1)

select @url = Rtrim(@url)
print convert(char, @bib#) + @tab + @url

fetch url_curs into @bib#, @url
end

MSSQL & JTURL Version 1.0f and before

use horizon
set nocount on
go

-- If the subfield "u" continues past the 255th column it will be truncated and, most likely, JTURL will not return 
-- an accurate response code when verifying the link. This is an ISQL limitation.

declare url_curs cursor for 
select text, bib#
from bib 
where tag = '856'
and text IS NOT NULL
union
select convert(varchar(255),longtext), bib# 
from bib_longtext 
where tag = '856'


open url_curs

declare @url varchar(255), @bib# integer,
@curs_cnt integer,
@curs_chk integer
select @curs_cnt = @@cursor_rows
select @curs_chk = 0

fetch url_curs into @url, @bib#
while @curs_chk < @curs_cnt

begin

select @curs_chk = @curs_chk + 1

if (select charindex(char(31)+'u', @url)) > 0 
select @url = SubString(@url,charindex(char(31)+'u',@url)+2, 255)
else
select @url = 'http://i.dont.see/A_SUBFIELD_U.html'

if (select charindex(char(31), @url)) > 0 select @url = SubString(@url,1, charindex(char(31), @url)-1)
if (select charindex(char(30), @url)) > 0 select @url = SubString(@url,1, charindex(char(30), @url)-1)

select @url = Rtrim(@url)
print @url + '|' + convert(char, @bib#)
-- print @url

fetch url_curs into @url, @bib#
end


 

 

RPA

Please be sure to read the information at the top of the page as some of it also applies to JTURL in general.  

JTURL must be run on the RPA server unless you extract the data from the RPA database in the appropriate format (url|resourcecode) and use the file locally.  The Help file explains this in more detail.  The ODBC Source is normally 'RPA', please verify.  The user and password are the same as what you use for RPA Admin.

Use the following Script:

        select resourceCode,SuccessUrl from resources where SuccessUrl != ''

After 'Retrieve'ing  the URL's please remove the first line if it reads '/rpa/refUrl.exe|refUrl'

You are then ready to 'Verify'

 

* Horizon/Sunrise and RPA are registered trademarks of Dynix, I assume.