ODBC and data mining

[Spencer Elliott]

Here's the story...

I'm 27 years old and soon to be a father, I'm a self taught (not exactly true, I've read a few books written by teachers on the subject, but I mean to say I have not attended any formal classes on the subject) programmer. At best I am a novice, and my experiance limited. Although I'm not totally useless...
I'm not good enough to write code as a living, but I do enjoy it as a hobby.
Have written some winsock applications that use a TCP/IP connection for example, so you get an idea of "Where I'm at".

I'm currently on a secondment which will be ending in approx two months. The job I am doing I really enjoy, it doesn't involve any programming really, only if self motivated enough to write some simple JavaScript, but this is not a requirement.
My main role currently is supporting IT applications at my place of work, and being involved in the development cycle of two applications which have there own development team.

I do not want to go back to my normal job after this secondment has ended, but it looks like I will be, and that job is far from safe, I think redundancy would soon follow. Unless I can pull something out of the bag...

One of the prgrams I am involved in writes a tonne of data to a SQL database. The application at the end of the month, basically extracts a whole months worth of data from the SQL database, dumps it all in an excell file, and saves it to a shared drive for someone to analyse and create readable information from.
The person who does this, doesn't really have time to do it, and it's something that has been put on them. It's a horrible system and a nasty problem that no one wants to look at...

This is where I think I can make an impression, and hopefully secure a job.

I would like to write a program in C++ that has a GUI (I have some experiance in this field and the GUI would be at the end of the program, I am not too worried about that right now), I would like the application to be able to connect to the SQL database (connect using an alias rather than the PC name). I would liek to be able to define the PC name in my applications GUI, but obviously first things first.. Let's get the application basics working first...

Once the application has connected to the database, I would then like to be able to make an excell file based on queries on the SQL database from the application. I want the application to be able to easily build queries, save those queries, and the ability to modify saved queries (so I can create commonly used examples, and the just modify the date range each week/month/whenever).

The database is massive, with around two hundred columbs, but I don't always want all that information. For example, the database always has an entry of "division", so I might want the application to create an excell file, that only shows me jobs where division equals "Day care centre".
This is one of the simplest examples I can give, a more complex example might be to show my all the entries that contain "Day Care Centre" from the dates of 21/03/07 and 12/08/08, and also populate only the "entry ID" and the "Donation given amount".

I do not have much exeriance with running queries, SQL databases or ODBC connections. I know this is ambitious, and obviously the project would start out much smaller than what I want the end result to be, but I know that if I was able to deliver this, I would be able to save my butt from redundancy, and secure a future for myself. Plus I would like to see if I can actually do it.

Is there anyone here that can give me some advice on how to start this project? I am not even sure how to make a simple program that connects to a database, let alone run a query and create an excel file based on the results. I have already googled ODBC C++, but there's nothing like someone actually helping you, or giving you advice based on there own experiance.

I really would appreciate some getting started advice. Like, really appreciate it.

[WingedPanther]

My first thought on reading this is "Crystal Reports". This is a reporting tool that is designed to create reports that are based on the results of a SQL query. You can have parameters in the query so that you can specify date ranges, fields, etc.

Delphi/Lazarus has an easy GUI and built in components for connecting to a variety of types of databases as well.

[Spencer Elliott]

Thank you for the reply. Indeed, crystal reporting has been considered already. But what is really needed is something easy to use, so I can build some commonly used queries in the application for others to use, and also make it look and feel like a custom app just for this project.
Plus, if I make an actual program myself to do this, it will make me look pretty good.
Using crystal reports puts it out of my domain, I cannot get involved then with it too much and I feel like it would be a lost oppurtunity to impress.

I'm certainly not asking for someone to write this for me, just some advice on how to get started. I really want to impress so that I don't get made redundant to be honest.

When you mention Delphi, do you mean some reporting tool, or the programming langauge? I am not familiar with any language other than C++ to be honest... :-/

Thank you for your thoughts on the matter WingedPanther

[WingedPanther]

Delphi is a programming language. If you go with C++, you'll need a GUI library to use with it, and an appropriate database library. Depending on what you're used to, those may or may not be easy decisions to make.

[LukeyJ]

In regards to the specific data you access from the database. This can be done using catered SQL statements.

To be pedantic, it is a relationship database, and it is access via SQL language. While SQL is quite useful, there are different brands of database, Oracle, DB2, and a Microsoft one. If you have a better idea of which one, there is generally a built in language which adds flexibility to SQL, in the case of Oracle, this is PL\SQL. The standard is SQL, but each DB has a procedural language built in which you can use to include decision making such as ifs and loops in your SQL.

I think the best strategy is to locate a bridge between the database and the language, I think this is ODBC but this is outside the field of my knowledge. If you can do that, then you can set up a DB on your system and start to manipulate the database through the use of C++, then once you've got that sorted you can better understand how to implement functionality into any program you write.