[sims] Accessing the SIMS database and querying it to retrieve information

[dunc]

Hi all,

 

Looking to develop a system (using PHP, which I'm confident with) which queries the SIMS database to retrieve attendance information.

 

Does anyone have any suggestions as to where I can start? A tutorial, or the like? Or is it simply a case of finding the DB that SIMS is running on, getting a username&password, and querying it as you would with any other DB?

 

Cheers,

Duncan

[chris_uk]

You will need to use command reporter.

 

generate an xml file that has the data you want to query attendance on.

commandreporter.exe /user:USERNAME /password:PASSWORD /report:"REPORT NAME" /PARAMFILE:"XML FILE WITH DATE" /output:"XML OUT"

 

You can then use php to import it to mysql.

[matt40k]

Yer, going straight into the database would equal death in my book. Attendance data isn't the sort of stuff you want in real-time (assuming we're talking parents) so @chris_uk is right on the money.

[CyberNerd]

It is doable - our old Moodle integration system did this. The problem comes if Capita unexpectedly change the database. You would be better off using commandreporter, or a SIF agent ( see SchoolsICT - enabling 21st century learning if you want to report data to parents), groupcall to do this. Also capita have made a complete mess of the database so it would be an uphill struggle.

[chris_uk]

If you create the correct reports it's so easy to pull data out overnight. We have student timetables, attendance, contact details. It all updates nightly into MySQL for some web apps that we have in school. Pulling the data out as XML makes it even easier over pulling it out as a CSV. If you contact your support provider for SIMS you can get an additional help file that includes all of the commands for command reporter.

[jinnantonnixx]

If the command reporter doesn't give exactly what you need, you can combine views. PM sent.

[matt40k]

If the command reporter doesn't give exactly what you need, you can combine views. PM sent.

 

PM sent include details about creating a limited user account for it to use?

[dunc]

Thanks for the help gents. E-mail system was down all day yesterday so I didn't realise I had so many replies

 

I'll look into the PM you sent jinn, and the solution you've suggested chris - many thanks.

[dunc]

You will need to use command reporter.

 

generate an xml file that has the data you want to query attendance on.

commandreporter.exe /user:USERNAME /password:PASSWORD /report:"REPORT NAME" /PARAMFILE:"XML FILE WITH DATE" /output:"XML OUT"

 

You can then use php to import it to mysql.

Hi again chris,

 

I've set up my attendance report; found CommandReporter.exe; run the line you gave me and it works insofar as the XML is displayed in my CMD window, but it doesn't generate an XML file.

 

The exact line I was using:

C:\Program Files\SIMS\SIMS .net>CommandReporter.exe /user:Duncan.Wraight /passwo
rd:password /report:"100% Attendance" /output: "g:\test.xml"

Edited October 7, 2011 by dunc

[rpwillis]

The exact line I was using:

C:\Program Files\SIMS\SIMS .net>CommandReporter.exe /user:Duncan.Wraight /passwo
rd:password /report:"100% Attendance" /output: "g:\test.xml"

 

You got some extra white space between /output: and the file name. Use

 

C:\Program Files\SIMS\SIMS .net>CommandReporter.exe /user:Duncan.Wraight /passwo
rd:password /report:"100% Attendance" /output:"g:\test.xml"

Edited October 7, 2011 by rpwillis
Get rid of quote markup

[dunc]

Wow, what a noob Thanks very much Richard!

[vikpaw]

... If you contact your support provider for SIMS you can get an additional help file that includes all of the commands for command reporter.

Does it contain any more than you can get from running it with the "/?" parameter?

[matt40k]

Does it contain any more than you can get from running it with the "/?" parameter?

 

Maybe

[LosOjos]

Maybe

 

Take that as a yes then? I've tried getting this from my local support but they have no clue what I'm talking about, very frustrating.

[matt40k]

To be fair to them, it's one of those areas not many people use and unless you've used it, you won't know about it.

[LosOjos]

To be fair to them, it's one of those areas not many people use and unless you've used it, you won't know about it.

 

That's fair enough - but it doesn't help me get hold of the documentation!

[matt40k]

One would hope they would be clever enough to go find out and get back to you - just like I had to do.

[vikpaw]

So there are secret hidden commands within command reporter? I do hope they involve fixing dates, and time periods.

[David_Grashoff]

Losojos - ask your local support team to contact me directly and we will get it resolved quickly for you.

[LosOjos]

Losojos - ask your local support team to contact me directly and we will get it resolved quickly for you.

 

Thanks David but I've got CLR sussed now. If you could possibly provide some info on the "Business Objects" however, that'd be incredibly useful...

[David_Grashoff]

same applies - ask your local support team to contact me directly with the SIMS areas that you wish to read or write.

[hsimpson]

Hi all,

 

Looking to develop a system (using PHP, which I'm confident with) which queries the SIMS database to retrieve attendance information.

 

Does anyone have any suggestions as to where I can start? A tutorial, or the like? Or is it simply a case of finding the DB that SIMS is running on, getting a username&password, and querying it as you would with any other DB?

 

Cheers,

Duncan

 

You should contact Capita and use one of their existing mechanisms (Command Line Reporter and Capita Business Objects I think) rather than querying the database directly - the database schema is susceptible to change and any changes could break your queries.

[vikpaw]

Thanks David but I've got CLR sussed now. If you could possibly provide some info on the "Business Objects" however, that'd be incredibly useful...

same applies - ask your local support team to contact me directly with the SIMS areas that you wish to read or write.

 

I'd like all the documentation you have got please (for all areas) . . .

 

You should contact Capita and use one of their existing mechanisms (Command Line Reporter and Capita Business Objects I think) rather than querying the database directly - the database schema is susceptible to change and any changes could break your queries.

 

That is the direction being taken now (CLR).

[matt40k]

I'd like all the documentation you have got please (for all areas) . . .

 

HAHAHA!!! That's like the head asking for all your documentation on your network - including that "Windows" thing. It's simply not possible. The document they send out about getting a connection and doing basic tasks is great, but completely pointless unless you invest the time learning. Capita provide good examples if you ask about doing a specific task, but again, you need to send the time fitting the bits together and smoothing the edges.

[vikpaw]

HAHAHA!!! That's like the head asking for all your documentation on your network - including that "Windows" thing. It's simply not possible. The document they send out about getting a connection and doing basic tasks is great, but completely pointless unless you invest the time learning. Capita provide good examples if you ask about doing a specific task, but again, you need to send the time fitting the bits together and smoothing the edges.

It was offered so i asked. Nobody has ever said there is any documentation at all in the past.

Maybe the lack of documentation hinders them as much as it hinders us!