+ Post New Thread
Page 1 of 4 1234 LastLast
Results 1 to 15 of 46
MIS Systems Thread, SIMS Learning about SQL and Writing basic Scripts in Technical; Hi All I'm currently a data manager but one of the things I will love to do is understand how ...
  1. #1

    Join Date
    Mar 2013
    Posts
    165
    Thank Post
    41
    Thanked 6 Times in 5 Posts
    Rep Power
    22

    SIMS Learning about SQL and Writing basic Scripts

    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

  2. #2
    CAM
    CAM is offline

    CAM's Avatar
    Join Date
    Mar 2008
    Location
    Burgh Heath, Surrey
    Posts
    4,115
    Thank Post
    826
    Thanked 357 Times in 281 Posts
    Blog Entries
    60
    Rep Power
    281
    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.

  3. #3

    tmcd35's Avatar
    Join Date
    Jul 2005
    Location
    Norfolk
    Posts
    5,665
    Thank Post
    850
    Thanked 893 Times in 738 Posts
    Blog Entries
    9
    Rep Power
    328
    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.

  4. #4
    Guest

    Join Date
    Jun 2009
    Posts
    3,754
    Thank Post
    1,458
    Thanked 489 Times in 375 Posts
    Rep Power
    0
    Quote Originally Posted by tmcd35 View Post
    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.

  5. #5
    CAM
    CAM is offline

    CAM's Avatar
    Join Date
    Mar 2008
    Location
    Burgh Heath, Surrey
    Posts
    4,115
    Thank Post
    826
    Thanked 357 Times in 281 Posts
    Blog Entries
    60
    Rep Power
    281
    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.

  6. #6

    jinnantonnixx's Avatar
    Join Date
    Mar 2011
    Location
    In the Calamatorium.
    Posts
    1,970
    Thank Post
    113
    Thanked 490 Times in 336 Posts
    Blog Entries
    2
    Rep Power
    283
    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.
    Last edited by jinnantonnixx; 11th October 2013 at 09:25 AM.

  7. #7


    Join Date
    May 2009
    Posts
    3,012
    Thank Post
    267
    Thanked 796 Times in 603 Posts
    Rep Power
    289
    Quote Originally Posted by chrisateen View Post
    * 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 :-)!

  8. #8

    Join Date
    Mar 2013
    Posts
    165
    Thank Post
    41
    Thanked 6 Times in 5 Posts
    Rep Power
    22
    Quote Originally Posted by CAM View Post
    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)

    Quote Originally Posted by Rawns View Post
    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.

    Quote Originally Posted by pcstru View Post
    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)

  9. #9
    CAM
    CAM is offline

    CAM's Avatar
    Join Date
    Mar 2008
    Location
    Burgh Heath, Surrey
    Posts
    4,115
    Thank Post
    826
    Thanked 357 Times in 281 Posts
    Blog Entries
    60
    Rep Power
    281
    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.

  10. #10
    GNewnham's Avatar
    Join Date
    Dec 2008
    Location
    Bath
    Posts
    65
    Thank Post
    9
    Thanked 9 Times in 7 Posts
    Rep Power
    22
    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.

  11. #11

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,407
    Thank Post
    368
    Thanked 639 Times in 521 Posts
    Rep Power
    158
    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.

  12. #12

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,452
    Thank Post
    1,439
    Thanked 1,170 Times in 798 Posts
    Rep Power
    707
    Quote Originally Posted by chrisateen View Post
    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)

  13. #13

    matt40k's Avatar
    Join Date
    Jun 2008
    Location
    Ipswich
    Posts
    4,407
    Thank Post
    368
    Thanked 639 Times in 521 Posts
    Rep Power
    158
    Like this - Sims.Net Photo Extractor - SalamanderSoft Limited - Free Utilities

  14. #14

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,452
    Thank Post
    1,439
    Thanked 1,170 Times in 798 Posts
    Rep Power
    707
    Quote Originally Posted by matt40k View Post
    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

  15. Thanks to LosOjos from:

    chrisateen (14th October 2013)

  16. #15

    Join Date
    Mar 2013
    Posts
    165
    Thank Post
    41
    Thanked 6 Times in 5 Posts
    Rep Power
    22
    Quote Originally Posted by matt40k View Post
    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

    Quote Originally Posted by LosOjos View Post
    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.

SHARE:
+ Post New Thread
Page 1 of 4 1234 LastLast

Similar Threads

  1. [SIMS] SIMS Learning gateway (attendance registers) and iPads
    By Oops_my_bad in forum MIS Systems
    Replies: 21
    Last Post: 6th January 2014, 08:30 PM
  2. Learning Platform wysiwyg and javascript document.write method
    By duxbuz in forum Virtual Learning Platforms
    Replies: 0
    Last Post: 19th October 2010, 02:59 PM
  3. SIMS Learning gateway and data security
    By reggiep in forum MIS Systems
    Replies: 2
    Last Post: 5th May 2009, 10:55 PM
  4. Replies: 0
    Last Post: 20th August 2007, 11:22 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •