I know what you did last summer!

Hi.

Let’s take a look at one always hot topic - automatic users activity logging…

And we are not interested in using some CUBRID Manager integrated logs solution or similar – I want a solution which does not depend on any CUBRID client type, a solution which works from whatever client the users will use to connect to a CUBRID database.

I want to be able to track what the user did – the INSERTs, the DELETEs, and the UPDATEs which were executed in my database.

So how do we do that…?

Well, it’s pretty obvious, the only easy way this would work is by using TRIGGERs!

Let’s simplify the problem a little bit – let’s talk about just one single table and let’s use the nation table from the demodb database.

First, let’s create a table which will hold the users’ activity log – nation_log:

Next thing – a visit to CUBRID Triggers tutorial!

After reading the tutorial and looking into online CUBRID TRIGGER manuals, it’s pretty clear what we have to do:

• Create one TRIGGER for each SQL operation type: INSERT, UPDATE, DELETE
• In each of these triggers, execute a custom SQL statement that updates the nation_log log table with the relevant operation information

Let’s start with the INSERT trigger and let’s log only two of the nation’s columns – [code] and [name], for example:

Ok - it’s time now to do some testing:

As you can see, the INSERT was intercepted by our trigger and the event data was saved into our logging table – which is exactly what we needed! :)

One more example – catching an UPDATE event:

I will leave the DELETE up to you, it’s not that complicated at all…

And this is it – now I know what you did in my database, so don’t lie to me! :)

Btw, these simple logging examples can be improved in so many ways…! For example:

• Log user name
• Log user IP address
• Log operation timestamp
• etc...

See you next time!

/Dee

P.S. Have you seen this application: http://sourceforge.net/projects/triggeraudit/...?

A picture is worth a thousand words! (Part II)

Hi again.

Remember, from the 1st part of this post, you have Sales, you have Profit & Loss and, most important, you have a demanding boss! :)

We saw how we can easily retrieve CUBRID data into Excel by using the OLE DB Driver and writing some VBA macro code. In this 2^nd part of the blog post, we will not write any custom code at all, but we will use only the built-in Excel OLE DB support to achieve similar results.

Btw, let's not forget what we said - "a picture is worth a thousand words"! So let’s try more images and less talk... :)

We will start by creating a CUBRID OLE DB connection from Excel:

Next, we will select the CUBRID View we need to use to show the boss the Sales data (see 1-st part of the post):

Once all these steps are completed, we have our data in Excel:

All is left to do is to create the chart we need. One easy way to achieve this is to select the “Pivot chart” option, in the last step of the wizard:

Of course, you can go on "playing" further with the Sales data – there are no limits -  once you got the right data into Excel, you have so many ways to display it…

And there is one last thing to clarify: how does the data gets refreshed? Well, it’s quite simple – take a look:

That’s it - See you next time – Bye-bye! :)

P.S. Remember, there’s more choices to get CUBRID data into Excel – for example, you can try to use as well the CUBRID ODBC Driver.                                                                                                             

A picture is worth a thousand words! (Part I)

Hi.

Let’s see - you have Sales, you have Profit & Loss and, most important, you have a boss who does not really care about how you choose to keep the company data in your CUBRID database – all he wants to see is Figures and Charts...

...So what's an easy way to get out the boring data as nice and easy to understand images?

Well, it’s not really that complicated - in the end, it’s all about mixing Excel with CUBRID!

For simplicity, let’s assume we have only 2 Sales tables in our database – Sales_People and Sales_Figures:

First step – let’s be smart – let’s create a VIEW (we only need to perform data query, no updates here):

CREATE VIEW Sales_Data AS

SELECT a.name, b.sales_date, b.amount

FROM sales_people a, sales_figures b

WHERE a.id = b.sales_people_id

ORDER BY b.sales_date AS

Now, how do we link CUBRID with Excel?

Simple as that, we have 2 solutions:

• Use a hard-coded but more powerful VBA macro approach
• Use the standard out-of-the-box Excel support for OLE DB and/or ODBC

The key thing for both approaches is to know that CUBRID provides both OLE DB and ODBC drivers out-of-the-box.

Let’s take a look first at the option of writing some VBA code by ourselves…

First we need to get the CUBRID data into an Excel worksheet. We will start by creating a new Excel VBA project/macro:

Next, we need to add a reference to the CUBRID OLE DB Type library:

Finally, here is some basic VBA code that does extract the Sales data from the CUBRID database and populates the worksheet:

Sub cubrid()

  Set conn = CreateObject("ADODB.Connection")

  Set rs = CreateObject("ADODB.Recordset")

  strConn = "Provider=CUBRID.OLEDBProvider;Location=localhost;Data Source=demodb;User Id=public;Port=33000"

 

  conn.Open strConn

 

  If conn.State <> 1 Then

    MsgBox "Sorry. No CUBRID today!"

    Exit Sub

  End If

  

  Set rs = conn.Execute("select name, sum(amount) AS amount from sales_data group by name")

 

  With Worksheets(1).Cells

    .ClearContents

    .CopyFromRecordset rs

  End With

  conn.Close

End Sub

And here is the result (of course, after adding also the chart we want, but this is an easy step):

Oh, one last piece of the puzzle – how to refresh data easily…? Well, nothing special in there – all you need to do is to add a customized button to the Excel ribbon, a button your can press anytime to have the data refreshed.

How exactly do you do that? I will let you figure out this one for yourself – here is the only tip you will need)

The 2nd part of the post will be published soon - keep an eye on the blog!

See you next time – Bye-bye! :)

P.S. Do you know who first said that “a picture is worth a thousand words”…?