SIMS Learning about SQL and Writing basic Scripts

[chrisateen]

Hi All

 

I'm currently a data manager but one of the things I will love to do is understand how the SIMS SQL database work and possibly learn how to write basic scripts using commandreporter and maybe one day be able to write small scripts that enables me to do things I'm unable to do. Plus it would help me career wise as high paying Data Manager roles are asking for experience with SQL and I may end up changing careers and become a programmer.

 

I just wanted to ask:

 

* is there documentation/ what programming language do I need to write scripts in commandreporter

* do you think the SIMS Technical courses on Installing and Configuring the SQL database useful for me to attend with me only having data management knowledge

* What advice or stuff or training could I do if I'm interested in doing the more technical stuff with SIMS or looking to possibly become a programmer in the future

 

 

Thanks for your advice

[CAM]

Command Reporter just runs a report designed in the SIMS system and outputs the results to CSV I think. You can't interact directly with the database though.

[tmcd35]

Doesn't Capita jealously guard access to the actual database? $$$$$?

 

I have seen the database and I can't say it was structured or named in a way that would make it easy to figure out. I think you'd need Capita's API's and reference manuals to be able to do anything more than can already be done through the built in Reports system.

[Rawns]

Doesn't Capita jealously guard access to the actual database? $$$$$?

 

I have seen the database and I can't say it was structured or named in a way that would make it easy to figure out. I think you'd need Capita's API's and reference manuals to be able to do anything more than can already be done through the built in Reports system.

 

You 'can' access the database through SQL management studio and run various queries against it, but any modifying of data, tables etc is a strict no no! If yo do this, cause problems and go to Capita, they will either not help or charge you a huge amount in consultancy fees to correct it!

 

Capita do a programming course in C++ where they show you how to use their API's to create various little handy programs and the like. However I believe it's not cheap.

[CAM]

If you want to access the database directly, you need to purchase a developer's contract (or whatever they call it). This gives access to the documentation and API details plus it covers the extra support given to developers by the Capita support teams.

[jinnantonnixx]

Work your way through the w3school course for a good grounding in SQL.

SQL Introduction

 

The key to understanding how the SIMS database is threaded together is by looking at the views they've built. These views draw together the underlying tables of the database. Examining the diagrams of these views will show you how the data is organised.

 

It goes without saying that you should never experiment with a live database.

Edited October 11, 2013 by jinnantonnixx

[pcstru]

* What advice or stuff or training could I do if I'm interested in doing the more technical stuff with SIMS or looking to possibly become a programmer in the future

Within your role there will likely be plenty for scope for using VBA in excel to perform/automate data analysis. That is a reasonable way to learn some programming basics while you are at work. You will though develop bad habits.

 

Off your own back, perhaps get involved in some open source project. Pick something PHP/MySQL based, the PHP will consolidate basic programming techniques and SQL is pretty standard as a required skill across most of the software development industry (obviously there are exceptions). Pick up a few languages, any web based development will have opportunities to dabble in Javascript or perhaps Java. Working on an active project with other developers will give you some support and encourage good programming practices (although PHP won't).

 

Have a look at something like Arduino and do a bit of low level interfacing. It will help consolidate your programming and give you some experience of development for embedded devices.

 

Consider the OU courses available and look to get a qualification that includes programming modules. (I would strongly urge you to do this if you seriously want a career in software development).

 

If you want to take an unqualified route, apply for jobs with software houses in support or testing. Those roles can be entry points into development. MIS suppliers might be a good target :-)!

[chrisateen]

Command Reporter just runs a report designed in the SIMS system and outputs the results to CSV I think. You can't interact directly with the database though.

 

Yeah I read online that to be able to have read/write permissions you need to purchase a very expensive license from Capita. I'm sure they do this to limit the competition as being able to modify the database opens up a lot of opportunities to develop programs and have less of a need to purchase packages from SIMS. But still I won't mind learning how use command reporter to run certain queries. (For example I would like to create a program that exports student photographs in JPEG file and naming each photo file by the student name or admission number)

 

You 'can' access the database through SQL management studio and run various queries against it, but any modifying of data, tables etc is a strict no no! If yo do this, cause problems and go to Capita, they will either not help or charge you a huge amount in consultancy fees to correct it!

 

Capita do a programming course in C++ where they show you how to use their API's to create various little handy programs and the like. However I believe it's not cheap.

 

What I would have done if I had access to the server is make a copy of the database and run SIMS on a standalone computer and do what i want to do with it without it affecting the live database. then if everything goes wrong I can delete it make another copy of the live database e.t.c

 

Is there a way I can access the SQL database on a Client Machine? (I'm guessing the answer to this is no)

 

Sound like a good course which i would love to do but i don't think I'm in a school where they will be willing to pay for it after what happened when I started here 2 months ago.

 

Within your role there will likely be plenty for scope for using VBA in excel to perform/automate data analysis. That is a reasonable way to learn some programming basics while you are at work. You will though develop bad habits.

 

Off your own back, perhaps get involved in some open source project. Pick something PHP/MySQL based, the PHP will consolidate basic programming techniques and SQL is pretty standard as a required skill across most of the software development industry (obviously there are exceptions). Pick up a few languages, any web based development will have opportunities to dabble in Javascript or perhaps Java. Working on an active project with other developers will give you some support and encourage good programming practices (although PHP won't).

 

Have a look at something like Arduino and do a bit of low level interfacing. It will help consolidate your programming and give you some experience of development for embedded devices.

 

Consider the OU courses available and look to get a qualification that includes programming modules. (I would strongly urge you to do this if you seriously want a career in software development).

 

If you want to take an unqualified route, apply for jobs with software houses in support or testing. Those roles can be entry points into development. MIS suppliers might be a good target :-)!

 

Thanks for the advice. I did learn VB at college and now I totally forgot how to write in VB. Recently I have started to learn VBA and SQL (I like how logical the sql language is and its so easy to read and understand) but i have heard that coding in VBA leads to bad habits. I even wanted to apply for an MSc in Computer Science for people with a degree in a completely different subject and was hoping that my new job will sponsor it but with what happened when I first started and with me not being happy in my new job I'm now reluctant to ask.

 

I'm starting to think that schools are not open to change and well behind on modern technology plus most managers I work with have no clue and too stuck in their own ways and hate ideas that make sense. I'm starting to doubt whether I should continue as a data manager but the fact I had a 2.2 degree, will have to do a Masters (which costs £9k) plus I will have to take a salary cut to break into the industry is making me question if i should change careers. Plus if I can find right school that allows Data Managers to do more technical stuff then I will be happy (hoping to do this in my new role but i don't think it's happening anymore)

[CAM]

I think in this case Capita do genuinely stop people from tinkering with the database directly because it does have potential to utterly trash the system. The dev licences aren't targeted at schools anyway but at industry where the money from the finished product will exceed the licence cost.

 

I know how you feel as I wouldn't mind a bit of raw database experience for a possible future industry move. But TBH, data managers tend to be left to their own devices giving lots of room for self-CPD which makes work easier as a bonus when you apply it to the job.

[GNewnham]

Have you tried SQL Reporting Services in BIDS (Business Intelligence Development Studio) - that is what we use to access the iSAMS SQL database. If you are not comfortable writing from scratch, you can take an existing extract / report and clone it, alter it and deploy it.

[matt40k]

SSRS is awesome, if you've got a proper dimensional warehouse. I did write SIMS Bulk Import - Home - which needs someone to show it some love if anyone's got any C# skills.

[LosOjos]

For example I would like to create a program that exports student photographs in JPEG file and naming each photo file by the student name or admission number

 

Student (and staff) photos are stored in SQL as base64 encoded text. I wrote a little utility app that takes an XML document (the SIMS RptDef to produce that file is included) and outputs all the photos contained within it as JPG to a specified folder, with options to resize them all on-the-fly, select between name, unique ID or admission number for the file name and also add the name to the bottom of the image if necessary.

 

I'd be happy to share it with you if you're interested, source code too if you want it (it was written in C# - quick and easy)

[matt40k]

Like this - Sims.Net Photo Extractor - SalamanderSoft Limited - Free Utilities

[LosOjos]

Like this - Sims.Net Photo Extractor - SalamanderSoft Limited - Free Utilities

 

Yes just like that but with added options I personally needed, plus the offer of source code to look at. I'm not trying to compete with anyone, don't worry

[chrisateen]

Like this - Sims.Net Photo Extractor - SalamanderSoft Limited - Free Utilities

 

I tried that but every time I tried running the command line it opens for a few seconds then closes again not giving me a chance to write the command to run the programme

 

Yes just like that but with added options I personally needed, plus the offer of source code to look at. I'm not trying to compete with anyone, don't worry

 

If you could share it with me that would be great . As I have done a few small stuff in C# I could sit down learn a bit more and then could try and understand what you wrote as well.

[chrisateen]

Edited my last post dumb me did not see the SQL folder in the SIMS drive and spent ages trying to look for it:doh:.

 

Made a copy of it and will have a look at it tonight at home

Edited October 14, 2013 by chrisateen

[matt40k]

Yes just like that but with added options I personally needed, plus the offer of source code to look at. I'm not trying to compete with anyone, don't worry

 

Haha, was just more a is this the kinda thing you were doing - do agree about the source, you did use to be able to run reflector and get something useful out of it. @chrisateen - did you put a pause in after it. Also I'm sure you're worrying a few people at Capita, might want to have a go at the build in reporting first, sounds like your trying to run before you can walk.

[LosOjos]

Haha, was just more a is this the kinda thing you were doing - do agree about the source, you did use to be able to run reflector and get something useful out of it. @chrisateen - did you put a pause in after it. Also I'm sure you're worrying a few people at Capita, might want to have a go at the build in reporting first, sounds like your trying to run before you can walk.

 

Excuse me - I forgot to take my bowl of humour this morning!

 

BTW - Code Reflect is a good substitute for Reflector now that the latter has gone commercial: CodeReflect Free .NET Decompiler

 

I tried that but every time I tried running the command line it opens for a few seconds then closes again not giving me a chance to write the command to run the programme

 

Are you just double clicking the exe? If so, that's the problem: it's a CLI program, so you need to call it from a command prompt, otherwise Windows will automatically close the window when the program exits (which it will do immediately after printing the help message if you provide no params). My app works exactly the same - seemed pointless making a GUI for it when I mainly call it from scripts.

 

If you could share it with me that would be great . As I have done a few small stuff in C# I could sit down learn a bit more and then could try and understand what you wrote as well.

 

It isn't the tidiest of code but it gets the job done and should give you an idea how to parse XML and convert a base64 string back in to an image stream (handy for any DB work): SIMSImageGrabber.zip

 

You'll need to import the included RptDef (if you'd rather make your own, it only needs a few fields, I'm happy to just tell you what they are) to SIMS, then run that and save the XML somewhere. Finally, run the XML through my app with any additional args (run it with no args to see what's available, or work it out from source ). Of course this can all be automated with BAT scripts and CommandReporter - in fact I do just that to gather photos for my Intranet's assessment analysis system.

Edited October 14, 2013 by LosOjos

[matt40k]

BTW - Code Reflect is a good substitute for Reflector now that the latter has gone commercial: CodeReflect Free .NET Decompiler

 

You tried JetBrains one? Was more pointing out that Richard has now obfuscated code.

[chrisateen]

I have made a copy of the SIMS database and because it is a db file I'm unable to open in SQL studio express. How do I go about opening the SIMS database file

[matt40k]

Stop.

 

Go to work, open SIMS .net, create a SIMS report. Play with that. Get booked onto a SIMS reporting course, then a Managing your SIMS.

 

If you've got a copy of the school's SIMS database at your home, your breaching DPA and you could get fired. Delete it. Walk before you can run. If you want to learn about Microsoft SQL - first use Microsoft example database AdventureWorks.

[chrisateen]

Stop.

 

Go to work, open SIMS .net, create a SIMS report. Play with that. Get booked onto a SIMS reporting course, then a Managing your SIMS.

 

If you've got a copy of the school's SIMS database at your home, your breaching DPA and you could get fired. Delete it. Walk before you can run. If you want to learn about Microsoft SQL - first use Microsoft example database AdventureWorks.

 

I already know how to do complex reports in SIMS.net and have set up a whole load of reports for teachers to use. Thanks for the advice and I think I'm going to give up on learning how to run queries using the sims database. (I already know/learnt how to run some queries using SQL). Just wanted to do it as if I want to get a higher paid data manager role one of the requirements is to have experience using SQL

[matt40k]

Yer, AdventureWorks is the way forward. Also get MS certificated, you can pick the self-study books quite cheap, SIMS is... pretty specialist. I think that's the polite way of putting it.

[pcstru]

I have made a copy of the SIMS database and because it is a db file I'm unable to open in SQL studio express. How do I go about opening the SIMS database file

I think you would need to do a backup of the database and then restore. However, SQLExpress may complain if the DB is over 10GB and hails from the future (different version).

[chrisateen]

I think you would need to do a backup of the database and then restore. However, SQLExpress may complain if the DB is over 10GB and hails from the future (different version).

 

Thanks. I don't have access to the server at the school (only have access to the client machine) and it's going to be impossible for me to do without server access so I think I'm going to leave it for now