Talis Developer Network - Comments

database analyser

Brian Crampton — Tue, 13 May 2008 16:09:22 +0100

I've downloaded the file using IE and Firefox and the the zip files were OK.

Were there were any particular errors.codes reported when you tried to open the zip file? Can you try on another PC just to check? By the way what operating system is on the PC?

Brian Crampton
Developer, Talis

Ah.. makes sense now. The

markhughes — Thu, 8 May 2008 10:44:38 +0100

Ah.. makes sense now.

The way I understood it from the TDN article was that rowcount didn't work at all on prism, that's why I was trying to find various workarounds for the 'top x' type of query.

Cheers
Mark

http://www.wirral-libraries.net/
http://www.stembystem.co.uk/

rowcount and prepared searches

Brian Crampton — Thu, 8 May 2008 08:48:36 +0100

There are two types of prepared searches - Offline and Online.

In Offline prepared searches the results are extracted into a table from a script that is run once and so rowcount can be used.

The Online prepared searches are run 'live' when accessed by a user and those have issues with using rowcount.

Some queries are better suited to the Offline approach because of the performance implications of the query. A top x type of query is likely to be one where the performance would not be suitable for an on-line prepared search even given the rowcount issue.

Thanks

Brian Crampton
Developer, Talis

Set rowcount in prepared search

markhughes — Wed, 7 May 2008 16:21:52 +0100

Brian,

I see from a few forum posts that 'set rowcount' seems to work in a prism prepared search, but the TDN article says it doesn't. Is there a problem using it in prism?

Cheers
Mark

http://www.wirral-libraries.net/
http://www.stembystem.co.uk/

Hello Have you tried setting

gi06 — Wed, 30 Apr 2008 13:46:10 +0100

Hello
Have you tried setting the TAL_ISSUE_WHEN_RESERVED to No, which should stop borrowers from issuing a reserved item.

reservations and renewals

Brian Crampton — Fri, 18 Apr 2008 17:23:59 +0100

It is possible to get a list from Talis Decisions but it would require joining two queries. One from the Reservation universe and one from the Circulation universe to get both reservation and loan data for items.

I have done a quick experiment on a basic report and it seems to work but there may be some assumptions I've made that don't match your requirements.

However by using a batch script, and I would tend to favour using PERL, you could get messages added to the items automatically as well and this would not be possible in Decisions.

In terms of reporting on all items it is difficult to say without seeing the code but you should be able to get a list of items attached to a reservation by going through the RESERVED_LNK table. For the reservation ids in question you wan the TARGET_IDs where TYPE=0. If using Perl this list of items can be stored in an array and then each item in the array can then be checked for the number of renewals since a specified date.

I realise I'm being a bit vague but without knowing what approach you are taking with your code it is difficult to be be precise. If you want to post the code of your script or send it to me directly I may be able to be more specific.

Thanks

Brian Crampton
Developer, Talis

reservation popularity

Brian Crampton — Thu, 17 Apr 2008 18:20:10 +0100

The problem is that the SS_reservations table does not exist and this command will not create that table for you.

if you change the SQL to

SET ROWCOUNT 50
SELECT RL.TARGET_ID "WORK_ID", COUNT(RL.RESERVATION_ID) "FIGURES"
into #SS_reservations
FROM RESERVED_LINK RL, RESERVATION R
WHERE RL.TYPE = 1
AND RL.RESERVATION_ID = R.RESERVATION_ID
AND R.CREATE_DATE >= DATEADD(DAY, -90, GETDATE())
GROUP BY RL.TARGET_ID
ORDER BY COUNT(RL.RESERVATION_ID) DESC

It will create a temporary table called #SS_reservations (The hash indicates a temporary table.)

By temporary I mean that within an isql session that table is available to be used so

select * from #SS_reservations

will work until you exit from isql and it won't exist for anyone else of for you if you start another isql session.

A temporary table is held in temp_db database so you need to be careful that you don't fill it or the logs up.

The SQL above is unlikely to do that but care needs to be taken.

What you need to do is create the SS_reservations table in prod_talis first.

e.g.
create table SS_reservations (
WORK_ID ID NOT NULL,
FIGURES smallint NULL)
go
grant all on SS_reservations to public
go

The smallint is upto 32,768 so should be big enough. Use INT (2^31) if not.

The trouble is that I now think with more consideration that with the SQL you have you may have to change the options on prod_talis to allow bulk data load but this would cause problems with transaction logging so would have to be done carefully e.g. the options changed, the script run, the options changed back and then a full dump done to allow transaction logging to start.

There are talis scripts that require these changes but this may be more than you want to do.

A different syntax will still log the transactions (the SS_reservations table still needs to be created) so allow the insert to work.

insert into SS_reservations (WORK_ID,FIGURES)
SELECT RL.TARGET_ID "WORK_ID", COUNT(RL.RESERVATION_ID) "FIGURES"
FROM RESERVED_LINK RL, RESERVATION R
WHERE RL.TYPE = 1
AND RL.RESERVATION_ID = R.RESERVATION_ID
AND R.CREATE_DATE >= DATEADD(DAY, -90, GETDATE())
GROUP BY RL.TARGET_ID
ORDER BY COUNT(RL.RESERVATION_ID) DESC
go

Note: you would need to remove any data from the SS_reservations table first.

As I said before all this type of changes to the db need to be done with great care as they are not covered by Support and done at your own risk.

You don't want to fill your transaction logs or tempdb and affect anything the whole system.

Thanks

Brian Crampton
Developer, Talis

Code

markhughes — Thu, 17 Apr 2008 15:09:37 +0100

Thanks Brian,

I was trying your code:

SET ROWCOUNT 50
SELECT RL.TARGET_ID "WORK_ID", COUNT(RL.RESERVATION_ID) "FIGURES"
into SS_reservations
FROM RESERVED_LINK RL, RESERVATION R
WHERE RL.TYPE = 1
AND RL.RESERVATION_ID = R.RESERVATION_ID
AND R.CREATE_DATE >= DATEADD(DAY, -90, GETDATE())
GROUP BY RL.TARGET_ID
ORDER BY COUNT(RL.RESERVATION_ID) DESC

Cheers
Mark

http://www.wirral-libraries.net/
http://www.stembystem.co.uk/

I've knocked a script

Ian Frost — Thu, 17 Apr 2008 12:19:33 +0100

I've knocked a script together which I think is doing what I require, however when it comes to works with multiple items I only get the barcode of the 1st item, how do I get the script to report all item barcodes?

IT Officer (Systems) @ University of Sunderland

inserting data into a table

Brian Crampton — Wed, 16 Apr 2008 15:55:17 +0100

You shouldn't need to grant permissions but could you let me have the SQL statement/s you were using and I'll have a look.

Thanks

Brian Crampton
Developer, Talis

Brian, I was tinkering with

markhughes — Wed, 16 Apr 2008 15:39:40 +0100

Brian,

I was tinkering with this on the MIS server, but gave up when it said

"You can't run SELECT INTO in this database. Please check with the Database
Owner."

Out of curiosity... how would I get round this? How would I grant permissions to 'select into' a table?

Cheers
Mark

http://www.wirral-libraries.net/
http://www.stembystem.co.uk/

edi claims to swets

rachel — Wed, 16 Apr 2008 14:54:18 +0100

Thanks Brian, i think it may be to do with the location of our parameter file.

edi swts script

Brian Crampton — Wed, 16 Apr 2008 10:11:17 +0100

It is difficult to comment precisely as edi_claims_swets.sc sounds like a shell script you have created and I don't know the contents or how it was run from the cron.

If the shell script cannot find the perl scripts then it is probably an issue about either the location of the perl script or permissions.

If the perl script is not in a directory defined in the PATH environment variable then unless the shell script and PERL script are in the logon directory the shell script would not find the perl script. I would suggest adding the full path to the perl script in the shell script.

You can also get that error is a script does not have the right permissions e.g. execute permissions. I would suggest checking the permissions on the perl script.

Other than that more details on the shell script and cron line would be needed.

Thanks

Brian Crampton
Developer, Talis

edi_claims to swets

rachel — Wed, 16 Apr 2008 09:29:37 +0100

Hi,
We have been trying to send a test edi claim to swets. We set up the edi claims script to run on 15th April but the only message returned in var/tmp was:

"stty: : No such device or address
stty: : No such device or address
edi_claims_swets.sc: edi_claims_swets.pl: not found
edi_claims_swets.sc: edi_claims_swets2.pl: not found
edi_claims_swets.sc: edi_claims_swets3.pl: not found"

Any ideas what is going wrong ie parameters, clm file etc.?

Thanks,
Rachel.

Hi, I've been trying the

davids — Thu, 10 Apr 2008 11:33:59 +0100

Hi,

I've been trying the sandbox url

http://sandbox.talis.com/TalisKeystone/ViewMyAccountRESTService?Barcode=19734689&Summary

for a few days now and each time it seems to timeout. Can anyone confirm that this sandbox url is actually working?

Thanks

Dave