so many trails ... so little time

Calculations with key date

2011-10-27T12:07:00.002+02:00

A bit of follow up to the previous post ... we'd like to have the key date at hand for performing some calculations, unfortunately we didn't find an easy way so with some micro abap we managed to get that variable into another variable (??) that can be used. Coding for variables (CMOD on BW side, EXIT_SAPLRRS0_001, include ZXRSRU01 etc.) follows: Wish there was a simpler way ... Code: case

Prompts in SAP BO OLAP universe (query key date)

2011-08-22T17:17:00.016+02:00

Having recently struggled with a key date prompt in a BO universe built on a BEx query ... I'm posting here some samples and screenshots of the outcome as I didn't find/couldn't understand documentation on this .... The universe wizard in my case didn't generate something usable out of the box, so I had to tweak it a bit First one, how to show it as a datepicker (sounds strange ...) The

Filtering on hierarchy values in DTP

2011-07-20T12:43:00.007+02:00

Having spent some time unsuccessfully googleing for a way to use a hierarchy as filter in a DTP I've come up with a (rather inelegant) solution myself.Posting it here in hope it's helpful. Code: DATA: l_idx like sy-tabix.DATA: lw_hier_id TYPE rshi_s_rshiedirkey.DATA: lt_nodes_a_leaves TYPE rshi_t_hienode.DATA: lw_nodes_a_leaves LIKE LINE OF lt_nodes_a_leaves.DATA: l_hier_name TYPE

I love windows functions

2010-03-20T22:02:00.011+01:00

... or whatever they call those functions that allow ranking and anyway working on "windows" of data (or sets inside sets or ...).A couple of questions on Devshed forums I recently answered using those functions.First one is about recordset paging (well, there are hundred of ways to do recordset paging, just google, but ayway) LIMIT being one of them, stored procedures another one (and ugly IMHO)

The missing link ...

2010-01-16T17:07:00.010+01:00

... between me and Firebird.The Tracker, that's it, claims are it's up again but it doesn't work for me.I'd love to open a feature request, or add my vote to an existing one, "role assignment to stored procedures".Actually I'm after assignment of the rdb$admin role to a stored procedure, in order to give unprivileged users the ability to perform some administrative tasks without the need for

Consuming webservices in JSP with Apache AXIS

2009-05-23T10:18:00.012+02:00

This is a rather old tutorial moved here from my old site on Geocities, hope it's still useful.Recently I started fumbling around with webservices, a nice integration and communication tool for my customers, what follows is a work in progress, which is going to be enhanced while I proceed in my “trial and error for the illiterate” learning path.First of all I installed AXIS (http://ws.apache.org/

Old challenges, new synthax ...

2008-11-02T11:27:00.016+01:00

I've blogged before about the new SQL synthax which is becoming available in databases and how it helps solving questions which are increasingly common.Now it's time for another example, something which doesn't come up often in a reporting environment as most tools have this feature, but can be a problem if you're building your output with a scipting language.Adding a "Total" row at the end of

Adding an OpenLDAP server as SQLServer linked server

2008-10-01T12:01:00.004+02:00

... and querying it.You can link to an OpenLDAP server from SQLServer by:1. Running the following stored procedures:Use mastergoexec sp_addlinkedserver'ADSI_OpenLDAP', --name of the linked server'My OpenLDAP Linked server', --description'ADSDSOObject','adsdatasource'goexec sp_addlinkedserverlogin'ADSIOpenLDAP',False,'sa','your_root_dn','your_root_dn_password'goNote that this skips each and every

Random selection, with a bias ...

2008-09-08T19:04:00.017+02:00

Say you want to randomly select your employee of the month, but not so randomly, better, you'd like to give your best employees a bigger chance to be selected based on their rating.This is just an example, you could be randomly displaying ads from your customers, but giving an higher chance to be displayed to those who are paying more, there can be a million other example, but I hope you got the

Setting up ColdFusion and Firebird

2008-07-29T15:00:00.026+02:00

After a long struggle on Devshed forums I decided to try on my own, after all I know Firebird and don't see why it and ColdFusion can't play nice together ... infact they did, and easily.Already having installed on my PC the database (Firebird 2.5 Alpha1) and a client tool (Flamerobin 0.8.6) I went for the server, downloading and installing the free developer version of ColdFusion 8 from Adobe's

The new MINI Clubman

2008-07-27T12:19:00.017+02:00

I'm the happy owner of a New MINI ONE and had a chance to try the New MINI Cooper Clubman, which, apart being the bigger sister of my car, is also a second generation New Mini, which means new engine, new steering and some other things ... unfortunately not all of them are nice.First of all, the new engine seems to be better than the old one, with a nice sound and good power.The new steering is

PostgreSQL - Firebird comparison

2008-07-22T15:52:00.016+02:00

Google led me to a comparison sheet about PostgreSQL and Firebird by AMSoftwareDesign, as it looks a bit outdated I decided to add some infos about the latest Firebird release, see it in action: PostgreSQL vs Firebird feature comparison Feature PostgreSQL 8.2.x Firebird 2.0.x Firebird 2.5 Alpha MVCC Yes Yes Yes Row level LockingAvailable Yes

Has them all

2008-07-22T12:47:00.006+02:00

A question that pops up frequently on Devshed forums is "How can I get all products that are available in Red and Green colors?" or "How can I find out which customers bought this book and that CD?", solution is simple and I'll provide an example here, it can be made more complicate at your option, but it all boils down to a where and an having condition.Say we have a table that lists all

Another great new feature of Firebird 2.5

2008-07-21T15:19:00.008+02:00

Other than CREATE USER something really valuable has been added, the ability to query other databases.Using a table structure like my previous post what follows is an example of a query running on an external database:SET TERM ^ ;CREATE PROCEDURE GET_MASTER_PROD_ALL_EXTRETURNS (P_CODE Char(5),I_ENABLED Char(1),P_DESCR Varchar(50) )ASdeclare variable qry varchar(5000); BEGINqry = 'SELECT

Controlling user access to data

2008-07-15T22:48:00.009+02:00

A straight and simple question on Devshed prompted me to post this example about using updateable views to limit the ability of users to read and manipulate data both horizontally and vertically.Before you ask, with "horizontally" I mean restricting access to a subset of rows in a table, with "vertically" I mean restricting access to a subset of columns in a table.See this scenario, a table

Don't silently turn your outer joins into inner joins ...

2008-06-23T17:59:00.008+02:00

Surprised by outer joins not returning the expected results? Looks like they behave like an inner join?You are probably messing with the join and the where clause.That's also why it's important to use the full join notation for every join instead of the where based one (often used for inner joins) as it allows for a better separation of the join and the where clause.Remember that the where clause

Firefox running for the Guinness record

2008-06-14T15:48:00.002+02:00

Let's help Firefox to reach the goal and set the new world record for downloads on June 17, 2008.BTW: I've tried it as a portable app ... WOW, much better than Firefox 2, fast and doesn't slow down with many open tabs.

The neglected driver ...

2008-06-08T15:11:00.003+02:00

Looks like the Firebird team is going to put some effort on the ODBC driver, which has been lying there abandoned for a few years (last activity report is of 2006) despite it's usefulness in windows desktop apps (at least for me ;))

Data load speed test

2008-05-23T23:18:00.004+02:00

I've run some data load tests with various databases using DBMonster, so connecting to databases through JDBC on a WindowsXP personal computer.Here are the results, in both cases I loaded 100 rows in the parent table and 1000 in the child table, with foreign keys enabled.Firebird 2.1 with Jaybird 2.1.3 and DBMonster 1.0.3 (And Java .6)Table structure is:CREATE TABLE GUYS(GUY_ID Integer NOT NULL,

Integrated auth in Firebird 2.1

2008-04-25T12:21:00.010+02:00

Just a quick sample of integrated windows auth in Firebird 2.1Logon to my Firebird server as a user with administrative privileges on the machine:C:\Programmi\Firebird\Firebird_2_1\bin>isql localhost:employeeDatabase: localhost:employeeSQL>So I'm logged. let's check who am I for Firebird:SQL> select current_user from rdb$database;USER==============================================================

Loading data from files

2008-04-19T22:39:00.004+02:00

Having already blogged about loading data from flat files to MySQL, it's time to post a similar case for PostgreSQL, as the manual seems to lack a real life example ...First of all the table to be loadedCREATE TABLE target(code character(3) NOT NULL,"name" character varying(50) NOT NULL,amount numeric,CONSTRAINT pk_1 PRIMARY KEY (code))WITH (OIDS=FALSE);as you can see one of the column names is a

Two basic indexing tips ...

2008-04-06T12:55:00.003+02:00

Here are two basic tips for proper indexing ...Don't mess with datatypes, too often people refer to an attribute defining it as one datatype in a table and as another in different tables, this actually prevents index usage in joins (forget about FKs for this time ;)) See an example here. You could declare a function based index as a workaround, but why don't we all try to make it right?Put

PgAgent - Installation and usage

2008-03-30T14:03:00.008+02:00

Prompted by the usual Devshed's post, this time about pgAgent, here I am with a small "tutorial" (or maybe just a small collection of screenshots).PgAgent is a great little tool, much like SQLServer's SQLAgent or the built in scheduling abilities of Oracle and MySQL (which is a much nicer solution IMHO), what's weak is the installation and the docs, I mean, why the pgAdminIII installer does ask

Shoot in the foot

2008-03-24T09:13:00.004+01:00

I've just finished reading two recent blog posts about new query optimizations in the upcoming MySQL 6.0, it's all fine and dandy but ...Looking at Correlated semi-join subqueries and PostgreSQL by S. Petrunia we can read something like Quote: The first thing we did was to take a look at PostgreSQL as it is easily available and seems to have at least decent subquery handling (or even better

Fasten your seatbelts ...

2008-03-16T11:30:00.006+01:00

... or how to safely run data manipulation statements in your database.Reading posts on Devshed's forums I sometime notice people doing maintenance work on their data without any safety net apart from occasional ages old backups ;-).Anyway I think there's no need for a restore if you just issued the wrong update query, I mean, transactions are here for this, it's just a matter of educating people

Simulating procedural logic

2008-03-09T13:56:00.013+01:00

Sometimes I see people having great difficulties in describing how to fetch data for a report.They are unable to reason by sets and tend to describe things in procedural terms.Here I'm posting a small example of how you can write a query that reproduces that procedural reasoning and lets the optimizer do the work of translating it into efficient SQL.Say someone has a table structure like this, a

How to sync two tables in MySQL

2008-03-01T17:52:00.014+01:00

A question pops out quite often on Devshed forums, "How do I keep table x of my local database in sync with a remote copy?"Usually replication is the suggested answer, but it might be a little overkill, think of those who just want to push the new product catalogue from a local server to their hosted site? No permanent connection and so on ...In this case the right tool might be a mix of the

New challenges, new synthax

2008-01-06T21:12:00.002+01:00

This post wants to be:1. A quick glance at the new "common table expression" (aka hierarchical queries) in Firebird 2.12. A call to action for other opensource databasesSo, on with 1., Firebird recently added another great feature, common table expressions (CTE) which, to my eye at least, boils down to hierarchical queries.This is basically the ability to efficiently and easily query hierarchical

Back to the Blog ... back to the bug (report)

2007-12-26T11:20:00.000+01:00

Today I decide to put up a post about a common requirement which leads to an interesting SQL query (see an example here) using Firebird and Flamerobin, after a few seconds I was writing bug reports :-( see:[ 1858394 ] Error selecting from mon$statementsand[ 1858397 ] Error running SELECT query. (Not a Flamerobin bug)Hope these will be fixed soon.EDIT: the developers instantly responded to my bug

Lufthansa ships with ...

2007-08-04T17:43:00.000+02:00

For those who are spending their miles at the Lufthansa Worldshop for the first time, they ship with UPS ... I haven't been able to find this out from their site and the tracking code remained rather obscure to me until I asked for clarification.Their answer was quick and accurate, but, is it so difficult to put a link to the UPS site?

Gnuwin32, what about the docs????

2007-06-28T18:33:00.001+02:00

Today I had to surrender and use grep on a pile of rubbish, er ..., on some Cognos files, those pesky .icr, so I went to the GNUWin32 site, downloaded the grep installer for windows and run, just like any good (windows) boy would do ... and the first surprise came, the gnuwin32/bin dir is not added to path ... anyway, I added it by hand and went on, in hope that a simple grep --help would give me

Opensource database comparison

2007-06-20T18:23:00.000+02:00

"Which is the best opensource database?", "How does MySQL/PostgreSQL/Firebird ... (you name it) stand against Firebird/PostgreSQL/MySQL ... (you name it again)?" I'm shure we've all heard this questions many times, but now I have the definitive answer, by Jim Starkey himself, you can read the full post here, but in short is"I think the short answer is that nobody with the experience to do this is

Automating mantenance tasks on MySQL

2007-05-27T12:33:00.000+02:00

I'm trying to automate some trivial maintenance tasks for my own MySQL server, and trying also to minimize the effort, so ... Here is the recipe:Take an excellent generalized stored procedure like the one by Konstantin Osipov, see "Dynamic SQL is Stored Procedures" on MySQLForge (example 4).Tune it a bit so that it takes into account only non system tables (I'm trying to turn it into something

MYSQLDUMP, mind your options!!

2007-05-20T11:05:00.000+02:00

In the process of upgrading my desktop installation of MySQL I noticed that, unlike triggers, stored procedures are not automatically dumped, that's a "not so nice" feature of mysqldump, sic, here is a snippet for those who don't have time to check the manualC:\Documents and Settings\user>mysqldump -uroot --single-transaction --routines--databases test remote_test > c:/backup_mysql_20_05_07.sql--

SAP BW, IF for your BEx formulas

2007-05-04T10:59:00.000+02:00

Have you ever felt the need for an IF in your BEx formulas? Didn't find it? That's because SAP expects you to know about boolean math ... See, we are subtracting two quantities and we want our result to be shown as 0 if the actual result is negative, the trick is very simple, look at this example: ( ( 'GR quantity' - 'PO quantity' ) > 0 ) * ( 'GR quantity' - 'PO quantity' ) The first block

Firebird 2.1 alpha, a quick glance at the new features

2007-04-07T17:26:00.000+02:00

After reading about the new Firebird 2.1 alpha I was eager to test it, and here are some sample queries that show how the new features look like, all examples are based on the standard EMPLOYEE.FDB.The first one is about the MERGE statement, which is useful in many ways (my favourite is synching tables).MERGE INTO country c USING (SELECT * FROM countryUNION ALLSELECT 'Maroc' country, '

Being a mod means

2007-03-30T10:56:00.000+02:00

This time I turned the kind gift of the Devshed guys into:"Financial Intelligence" an interesting book which I hope will help in my ever evolving job, I really need to understand what financial people are saying and what they really mean. This one looks promising, it's a side of my professional life that I've never really dug into, but it's very interesting (and somehow fun) .

Another handy MySQL function

2007-02-24T17:48:00.000+01:00

This time it's a quick function to validate email addresses, based on regexp.It can be used in a trigger to add data validation, or to check data already in your database that needs a clean up ... really a simple wrapper around a simple regexp query, but it can be helpful.Here it is:DELIMITER $$ DROP FUNCTION IF EXISTS `test`.`is_valid_email` $$CREATE DEFINER=`root`@`localhost` FUNCTION `

Missing DLLs when starting GIMP 2.3.x

2007-02-14T13:55:00.000+01:00

I've just downloaded and installed Gimp 2.3.14 for windows and noticed what seems to be a widespread problem, startup errors due to missing dlls. Of the many solutions listed here I found out that:1. Changing the shortcut icon on the desktop (point 5 in the page linked)2. Changing the shortcut icon in Start -> Programs -> GIMP -> Gimp 23. Adding a registry key (point 6 in the page linked)solved

MySQL spanning different timezones

2007-02-11T14:02:00.000+01:00

So what, if you are planning a centralized MySQL server and many clients all around the world?First of all, check the manualThen install timezone description tables from hereThen run a small example to see how the server and client timezones can interact.Here is the standard server setupmysql> show variables like 'time_zone';+---------------+--------+| Variable_name | Value |+---------------+---

Powerline, the good, the bad and the ugly ...

2007-01-27T21:24:00.000+01:00

After much esitance I decided to jump on the bandwagon of Powerline (a.k.a. Homeplug), that's it, ethernet over power!At first it sounds great, I mean, no cabling, no pesky radiations going round like with WiFi and so on ... an easy way of sharing internet connection for my 2 PCs at home.I tested WiFi 802.11b before and wasn't impressed with it's performance, so I reached my purse and got a pair

Signs of MySQL usage at BBC.CO.UK

2007-01-22T14:21:00.000+01:00

A very interesting document from BBC's Standards and Guidelines (Database Design and Development Standards v1.00) mentions only two databases, guess what? Oracle and MySQL!!!BTW the document is also a source of good design tips like:Normally, use only alphanumeric characters plus the underscore (_) character for table names.MySQLTable names are case sensitive under UNIX (or Linux). To avoid

Playing with transactions on MySQL

2007-01-21T19:02:00.000+01:00

Here is my attempt, failed as per the video quality, at showing a small example of transactions usage on MySQL 5.

Falcon, the new MySQL Storage Engine

2007-01-04T19:06:00.000+01:00

While taking a quick look at the newly released docs for Falcon, the new storage engine for the MySQL database from MySQL AB (and this is important, as actually InnoDB and the now ditched BDB storage engines for MySQL database are from Oracle) I noticed two "strange" things, at least to someone thinking of large databases running on dedicated hardware.First of all the data storage, chapter

Creating an auto incrementing column in Firebird

2006-12-30T18:32:00.003+01:00

or in any database that supports triggers and generators (aka sequences) ...The answer is well known however the question comes out quite often, be it in an Oracle, Firebird or PostgreSQL forum.Well, the PostgreSQL guys took a radical approach and embedded the common solution into a datatype named SERIAL, what follows is an example aimed at the Firebird newbie. Say you have a table like this

An Opensource approach to engineering

2006-12-10T14:45:00.000+01:00

I love this guys!Image extracted with GIMP and it's excellent SIOX tool

How to use BLOB field in VB 6 with Firebird

2006-11-26T15:34:00.000+01:00

As per this post on Devshed forums I'm pasting here some sample code (taken from Mike Hillyer's excellent "Accessing MySQL BLOB columns using Visual Basic 6"), hope it helps.Code uses a simpler table structure and due to the fact that I didn't bother about reproducing the "autoincrement" field it will get you into troubles if trying to run the sample multiple times.You'll notice that basically

Custom ordering for your results

2006-11-08T19:07:00.000+01:00

... or, taming the ORDER BY clause.Say you want to implement a custom ordering for your queries, as an example, you want to display each customer's orders with shipped orders first, then waiting orders and open orders last.That's ordering by the 'status' column and you can't use alphabetical ordering!In this case you'll have to implement some kind of logic in your order by clause, and a CASE is

Of correlated and uncorrelated subqueries

2006-11-02T16:29:00.003+01:00

Many SQL questions on Devshed forums are easily solved using correlated and uncorrelated subqueries, but many don't know of them at all, or at least are not shure about the syntax, usage and performance implications, they sometimes rave about a stored procedure to loop and select other values ...The definition (taken from O'Reilly's "Learning SQL on SQL Server 2005") of a correlated subquery:A

Firebird's explain PLAN

2006-10-28T19:49:00.000+02:00

After a post on Devshed forums I'm putting here an excerpt from a little tutorial about Firebird I wrote some time ago, this is about the explain plan of a query:More on speeding … the Explain planAn extremely useful feature of relational databases is the “explain plan”,whichshows us how the database engine is going to execute a query. It allows the DBA tocheck if indexes are used and how tables

Audacity 1.3.2 is coming

2006-10-22T17:10:00.000+02:00

See release notes for Audacity 1.3.2

Handy MySQL function

2006-10-22T09:27:00.000+02:00

Here is a simple function that can help coping with wrong database design, when you have a mix of NULLs and blank fields meaning the same, no value, which they shouldn't.DELIMITER $$ DROP FUNCTION IF EXISTS `test`.`is_initial` $$CREATE FUNCTION `test`.`is_initial` (f varchar(255)) RETURNS BOOLBEGIN SET @is_initial = false; CASE fWHEN NULL THEN SET @is_initial = TRUE;WHEN '' THEN SET @is_initial

Something you'll need for shure in Poland

2006-09-16T17:19:00.000+02:00

Well, if you don't want your laptop to lie dead soon the first thing to buy when in Poland is an adaptor like this from DPM Elektronik.

Php and SOAP, getting to the corporate business intelligence repository

2006-09-05T19:22:00.000+02:00

What follows is a snippet taken from a very old experiment I did, integrating a php application with BusinessObject's WebIntelligence 2.7.x through it's (very experimental) webservices toolkit, which was really promising but Java and .NET oriented ;)// include the classinclude("nusoap.php"); // create a instance of the SOAP client object // remember that this script is the client,// accessing the

MySQL function: months_between

2006-08-26T14:21:00.000+02:00

After directing a Devshed poster looking for a way to compute the number of months between two dates to the manual I decided to turn the solution posted in user comments by Isaac Shepard into a function, here it is:DELIMITER $$ DROP FUNCTION IF EXISTS `test`.`months_between` $$CREATE FUNCTION `test`.`months_between` (date_start DATE, date_end DATE) RETURNS INTBEGIN SELECT IF((((YEAR(date_end) -

Flamerobin as a GUI for embedded Firebird

2006-08-17T15:31:00.000+02:00

In general you just need to have Flamerobin connect to Embedded Firebird through local protocol, my setup with Firebird 1.5.3 (Firebird 2.0 RC3 is giving me troubles!) is:1. Pick fbembed.dll from Firebird-1.5.3.4870-0_embed_win32.zip2. Copy it to C:\Program Files\FlameRobin (the Flamerobin installation folder, whatever it's named3. Rename it to fbclient.dll4. Register a new server in Flamerobin,

The case for function based indexes - part two

2006-08-16T17:27:00.000+02:00

Some time ago I wrote about function based indexes, showing a PostgreSQL based case, right now I'll show a Firebird based example, due to the fact that Firebird 2.0 supports Expression Indexes, which are my good old function based indexes.This example will be based on the sample database which ships with Firebird, named EMPLOYEE.FDB.As the old example was based on extracting a substring from a

MySQL Control Center, live after death???

2006-08-16T13:38:00.000+02:00

Hey, looks like someone decided to give a spin to the old MySQLCC (aka Mysql control center) , see Sourceforge project page by releasing version 0.9.7.

OpenOffice Calc and Firebird through ODBC

2006-08-11T12:18:00.000+02:00

This is meant to be a quickstarter in using OpenOffice 2.0.3 and Firebird on Windows.Of course many of this steps are common to any other database used in OpenOffice, so I hopeothers will be able to use this doc as a source.I'll be using the EMPLOYEE.FDB sample database that comes with Firebird.First of all you need to install the ODBC driver for Firebird, you can download the latest andgreatest

ASP and Firebird

2006-08-01T17:37:00.000+02:00

This is really a copy of a PDF file I made available on my old website, I'm posting it here because I'm trying to clean up things.It's about Firebird and ASP pages, the most interesting thing is that it shows how to use transactions within ASP pages, which is not so common.Connect to Firebird and retrieve data in ASP:...on error resume nextdim adoConndim adoRSdim counterset adoConn =

Translating for developers

2006-07-16T12:26:00.000+02:00

Hi, what follows are my personal thoughts after reading Dmitry Yemanov's comments to a great poll posted at Firebirdnews about the features people would like to see implemented.Quote:1. SMP support is a clear winner (23% of votes) in the poll. And this is definitely one of the high priority tasks in the project. As you know, it was the major goal of the Vulcan development and its implementation

Relocating MySQL data

2006-07-12T17:21:00.000+02:00

After an interesting question I'm posting here a simple description of how to place your MySQL data on another drive, if you got a server with multiple disks this can really benefit performance.The picture is this, you have installed MySQL server in the usual C:\Program Files\MySQL\MySQL Server 5.0\ folder, but you have another drive D: and you want to place your data there, you'll create three

ASP and PostgreSQL quick snippet

2006-06-30T13:30:00.000+02:00

Hi, here is a small snippet to connect to PostgreSQL from an ASP page and run a select query.dim adoConn dim adoRS dim counter set adoConn = Server.CreateObject("ADODB.Connection") set adoRS = Server.CreateObject("ADODB.Recordset") strConn = "Provider=PostgreSQL.1;Data Source=localhost;location=postgres;" & _"User Id=login_role;password=login_role_password;" adoConn.Open strConn

Hardware upgrades are good!

2006-06-25T15:04:00.000+02:00

Hi there, working in the IT sector and standing in front of my laptop every day for many hours brought me to forget about my home desktop, used by my parents and expecially about their old CRT 17'' monitor.I kept thinking it was a bit old but generally good, now I switched to a 17'' LCD display and I have to say that is's waaaaay much better, more environment friendly, more desk space friendly,

I love it!!

2006-06-18T12:48:00.000+02:00

After the Olympics Turin has become a very nice place to stay, here is another great dayI'd love an LX 125cc 4t to speed through the city.

Being a mod means ...

2006-06-10T16:57:00.000+02:00

receiving a little gift from the owners of the site ;-) which is very much appreciated.In my case the first gift turned into this book about composites.Don't be fooled by the title, I'm more into the sailing or skiingside of composites, but it's a nice book.An year after I got another nice one, "The Design of Everyday Things", well I just received it and haven't had the time to even take a peek

Good news from the blogs!!!

2006-05-25T13:35:00.000+02:00

Yes, for you replication lovers on MS platform, who had great time with MsSQL and MySQL's replication now Slony (replication engine for PostgreSQL) is here.See the original blog post by Robert Treat (with installation guide). A prebuilt windows binary is available here.

ASP and MySQL

2006-05-05T12:38:00.000+02:00

Seems there is some interest in running MySQL as a backend for (classic) ASP based sites, at least looking at recent posts on DevShed forums.As questions are quite similar and I've not found a good ASP+MySQL tutorial on the web I'm posting a short example hereHere is my snippet: dim adoConn dim adoRS set adoConn = Server.CreateObject("ADODB.Connection") set adoRS = Server.CreateObject("

MySQL does it again ... MySQL Forge!!!

2006-05-05T12:30:00.000+02:00

Great snippet on Dynamic SQL in stored procedures from MySQLForge!!!

Migrating triggers from SQLServer to MySQL

2006-04-28T15:31:00.000+02:00

After a few posts on Devshed forums I decided to summarize some basic directions about porting SQLServer triggers to MySQL.There are some notable differencies between trigger syntax in SQLServer and MySQL (and other databases in general).First of all usually you reference old (preexisting) and new (inserted or updated) column values by NEW.column_name and OLD.column_name, but in SQLServer

An INFORMATION_SCHEMA for Firebird?

2006-04-24T23:49:00.000+02:00

Excellent blog post by Lorenzo Alberton! Is INFORMATION_SCHEMA coming to Firebird? I hope so!EDIT:The author left out one important bit of info that you can retrieve from Firebird, a list of Stored Procedures, here is the sql query:SELECT* FROM rdb$procedures;

MySQL full LOAD DATA INFILE example

2006-04-20T20:39:00.000+02:00

Here is a more detailed example of LOAD DATA INFILE syntax:First of all create a table to be loaded:mysql> create table 2beloaded (field_1 char(1), field_2 int, field_3 char(1), field_4 varchar(50));Query OK, 0 rows affected (0.15 sec)Then the text file:c, 1, ab, 2, d(named 2beloaded.txt and placed in c:\, so it's a file created on Windows platform!!)Now the query to load it, we want to load the

MySQL triggers and Master/Detail tables

2006-04-18T00:12:00.000+02:00

Have you ever wondered how to update the status of an order to "Closed" when the whole ordered quantity has been received?If you are on a database that supports triggers like me (I'm on MySQL 5.0.16 right now) you can have something like:Table structure:Orders tableDROP TABLE IF EXISTS `test`.`orders`;CREATE TABLE `test`.`orders` ( `order_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `

Php PDO and Firebird

2006-04-12T13:12:00.000+02:00

A quick snippet ...query('SELECT COUNTRY from COUNTRY') as $row) { print_r($row); } $dbh = null; } catch (PDOException $e) { print "Error!: " . $e->getMessage() . ""; die();} ?>

Php Firebird (ehm Interbase) functions ...

2006-04-11T19:28:00.000+02:00

A post on Devshed forums showed that the php manual did not have any detailed info about ibase_service_attach and some other Interbase/Firebird functions, of course the question has been answered in the forums and the user promised to add a note on the official docs, anyway here is a small example of use of that specific function:...// get server version and implementation strings if (($service =

Replicating from MsSQL Server to PostgreSQL

2006-04-11T19:16:00.000+02:00

Very interesting post from Magnus Hagander, take a look at it.

OpenLDAP Win32

2006-04-01T22:38:00.000+02:00

Good news for OpenLDAP fans!!Lucas Bergman with help from Matthias Mohr has put up a win32 build of OpenLDAP 2.2.29.Thank you very much and keep up the good work.

Small Firebird trigger example

2006-03-18T22:44:00.000+01:00

After reading a post in the Firebird forum at Devshed I immediately put up a small example about writing a trigger (and also upgraded my favourite tool, Flamerobin, to the latest version) , here it goes:1. Create an empty table2. Create the trigger on it using Flamerobin's guided menuAs you can see the menu will guide you through defining the trigger name, to activate it (in other words you can

GIMP 2.3.7 on WinXP

2006-03-14T14:01:00.000+01:00

After many difficulties with preceding development releases I finally succeeded in compiling The GIMP on WinXP with MinGW.Right now I only used a simple --configure --disable-print --disable-python --prefix=/d/gimp237 but I'll try with more complex config options and also adding littlecms to the pack, see some screenshots below

MySQL Workbench 1.0.5 beta ...

2006-03-09T09:23:00.000+01:00

... nice, but not quite there yet.I mean, it has a clean look at I processed the ported Firebird Employee database of my previous posts to check it's reverse engineering abilities, everything went smooth for tables and views (and foreign keys too!), but unfortunately no triggers and stored procs where reverse engineered.Here is the visual cronicle:Connection to database:After the connection MySQL

iPod enabled tie

2006-02-25T12:04:00.000+01:00

Look at this great tie!! I want it.

TOra alive and well, releases v. 1.3.21

2006-02-25T11:53:00.000+01:00

TOra 1.3.21 is out, release notes:Notes:1.3.21:2006-02-08: Applied patch from Pawe=B3 Kucharczyk to fix storage queries used against Ora 10gR2.2006-02-18: Readded Check Syntax to SQL Worksheet (many thanks to Sergei Kuchin of otl)2006-02-18: minor changes to PL/SQL Debugger.Solved a bug where the debugger stops on every call of a stored function,now when compiling the current position of the

After the olympics ... ski mountaineering championships!

2006-02-25T11:33:00.000+01:00

The case for function based indexes

2006-02-17T17:46:00.000+01:00

Since a lot of codes are "speaking codes" (...) meaning that they can be split into fixed meaningful substrings I often see queries like this:select * from t_table where c_code like 'DD6%' which is highly inefficient, look at it's explain plan:Seq Scan on t_table (cost=100000000.00..100000001.06 rows=1 width=33) Filter: (((c_code)::text ~~ 'DD6%'::text)Since the first 3 chars only are

Borland to dismiss Interbase (again)?

2006-02-11T17:33:00.000+01:00

An email from the Italian branch of Borland Developer News announced that Borland is to dismiss a lot of it's more traditional business, including Interbase (no mention of it in Tod Nielsen's letter) .Time to switch the remaining Interbase customers to Firebird?

TOra 1.3.20 is out

2006-02-07T18:12:00.000+01:00

Great news for all Oracle users, after the release of a free version of Oracle 10g (named Oracle Database 10g Express Edition) which is free to develop, deploy and distribute, we now have a new version of the great opensource management tool for Oracle, TOra 1.3.20, which nicely complements the free database and will hopefully take the place of the free version of TOAD on my PC.More on this later

Porting the EMPLOYEE database from Firebird 2.0 to MySQL 5.1 part 8

2006-02-03T12:39:00.000+01:00

A more interesting example:SET TERM ^ ;CREATE PROCEDURE ADD_EMP_PROJ (EMP_NO Smallint,PROJ_ID Char(5) )ASBEGINBEGININSERT INTO employee_project (emp_no, proj_id) VALUES (:emp_no, :proj_id);WHEN SQLCODE -530 DO EXCEPTION unknown_emp_id;ENDSUSPEND;END^SET TERM ; ^This procedure makes use of exceptions to manage errors, MySQL currently doesn't support them but equivalent can be achieved with

Porting the EMPLOYEE database from Firebird 2.0 to MySQL 5.1 part 7

2006-02-03T11:19:00.000+01:00

Trying to keep pace with work and porting Firebird stored procs to MySQL can be really hard ... anyway here are two sp's translated:Firebird procedure to list some stats for departments by department head:SET TERM ^ ;CREATE PROCEDURE SUB_TOT_BUDGET (HEAD_DEPT Char(3) )RETURNS (TOT_BUDGET Decimal(12,2),AVG_BUDGET Decimal(12,2),MIN_BUDGET Decimal(12,2),MAX_BUDGET Decimal(12,2) )ASBEGINSELECT SUM(

Porting the EMPLOYEE database from Firebird 2.0 to MySQL 5.1 part 6

2006-02-01T09:54:00.000+01:00

More on foreign keys in MySQL:After all those FK added I can only recommend to check that columns have the same datatype, if this is not true MySQL will spit strange error messages like "can't create table ..." when trying to add foreign keys.Foreign keys are not deferrable in MySQL so the circular reference between employee and department tables makes impossible to load employee before dept and

Porting the EMPLOYEE database from Firebird 2.0 to MySQL 5.1 part 5

2006-01-31T15:33:00.000+01:00

Having used InnoDB table handler in MySQL to be on par with Firebird capabilities I'm going to add foreign keys, unique constraints and check constraints and indexes to the tables migrated in MySQL.Here is a screenshot of Flamerobin showing constraints for table CUSTOMER.First of all I'll add the foreign key constraints, here is MySQL's syntax:ALTER TABLE `employee`.`customer` ADD CONSTRAINT `

Porting the EMPLOYEE database from Firebird 2.0 to MySQL 5.1 part 4

2006-01-31T09:30:00.000+01:00

Now the next trigger, it will be an AFTER INSERT trigger, so to fire after the previous one has inserted a row, obviously an after insert trigger can reference old and new data but can't modify them.So, on with the code:DELIMITER $-- DROP TRIGGER new_salary $$CREATE TRIGGER new_salary AFTER INSERT ON salary_historyFOR EACH ROW BEGINUPDATE salary_history SET new_salary = (new.old_salary +

Porting the EMPLOYEE database from Firebird 2.0 to MySQL 5.1 part 3

2006-01-30T18:14:00.000+01:00

Ok, on with triggers, I'll start migrating the existing triggers then I'll look at triggers as a workaround for computed columns.Firebird syntax for SAVE_SALARY_CHANGE trigger (active AFTER UPDATE) on table EMPLOYEE is:...BEGIN IF (old.salary <> new.salary) THENINSERT INTO salary_history(emp_no, change_date, updater_id, old_salary, percent_change)VALUES (old.emp_no,'NOW',user,old.salary,(

Porting the EMPLOYEE database from Firebird 2.0 to MySQL 5.1 part 2

2006-01-30T15:55:00.000+01:00

More on calculated columns, here is the create table statement for one of Firebird's Employee.fdb database: CREATE TABLE SALARY_HISTORY (EMP_NO EMPNO NOT NULL,CHANGE_DATE DATE DEFAULT 'NOW' NOT NULL,UPDATER_ID VARCHAR(20) NOT NULL,OLD_SALARY SALARY NOT NULL,PERCENT_CHANGE DOUBLE PRECISION DEFAULT 0 NOT NULLCHECK (PERCENT_CHANGE BETWEEN -50 AND 50), NEW_SALARY COMPUTED BY(

Porting the EMPLOYEE database from Firebird 2.0 to MySQL 5.1 part 1

2006-01-30T15:24:00.000+01:00

Hi, I've decided to make an in depth test of MySQL's new functionalities by porting Firebird's EMPLOYEE.FDB database to MySQL 5.1.5, here is what I found, I hope it will be useful for people porting apps from MySQL to Firebird and vice-versa.First of all, a screenshot (taken from Firebird's excellent Flamerobin admin tool) showing the actual employee.fdb structure.Note the presence of

Discordian Date for MySQL

2006-01-29T12:24:00.000+01:00

Here is a MySQL version of a function to convert from gregorian to discordian dates.DELIMITER $DROP FUNCTION IF EXISTS `test`.`toDiscordian` $CREATE FUNCTION `toDiscordian`(gdate DATE) RETURNS varchar(50)DETERMINISTICCOMMENT 'Discordian Date Converter'BEGINSET @year = EXTRACT(YEAR FROM gdate);SET @dyear = @year + 1166;SET @month = EXTRACT(MONTH FROM gdate);SET @mday = DAYOFMONTH(gdate);SET @yday

Changelog to the people

2006-01-28T15:08:00.000+01:00

Right now two other guys added their voice to mine in this feature request to MySQL AB for a public changelog of MySQL QueryBrowser.Please add your comments too, if interested in it.

Going wireless!!!!

2006-01-28T15:03:00.000+01:00

If you live in Europe, as I do, don't think twice about it, go to eBay for your wi-fi gear.Now I'm on a cheap used (??) Linksys BEFW1154 which works very well.

On with MySQL 5.1

2006-01-28T13:00:00.000+01:00

Eager to experiment ... I installed MySQL 5.1.5 on my laptop, in place of an existing MySQL 5.0.x installation from which I borrowed the my.ini file, the outcome has been bug 16781 at MySQL AB, the new one doesn't seem to like this row in the ini file:# The default storage engine that will be used when create new tablesdefault-storage-engine=InnoDB

A better PHPBB schema

2006-01-24T15:26:00.000+01:00

I've just modified the phpBB schema for PostgreSQL by:Taking it from phpBB 2.0.18Placing everything in the "phpbb" schemaThe schema is referred to a role (owner) named "phpbb"There is some sample data in it (rubbish) you can login as "Admin" password "admin"Now the most relevant changes:Added foreign keysAdded unique constraintsTested on PostgreSQL 8.1 win32Downloadable hereHope it helps!!I'm

Select a random row from a table with Firebird

2006-01-21T15:21:00.000+01:00

After reading this page I noticed that Firebird was missing, so I had to add this little bit of info.In order to select a random row from a table in Firebird you need to first install an UDF that provides the rand() function, which is ib_udf, it comes for free with the server, but it's not installed by default.So execute the appropriate sql script to install a bunch of UDFs (ib_udf.sql and