tag:blogger.com,1999:blog-169622062009-07-11T16:21:09.813+02:00pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.comBlogger107125tag:blogger.com,1999:blog-16962206.post-33211464505217855502009-05-23T10:18:00.012+02:002009-05-23T10:42:38.229+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/

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com0tag:blogger.com,1999:blog-16962206.post-36138357960145369942009-03-14T17:16:00.003+01:002009-04-05T19:52:33.200+02:00

A real one this time.Running!!!I got some tricks and a basic training schedule from here and starting on monday I'll try to run on a regular schedule, here it is:Prima settimana (3 sedute)5′ al passo30″ di corsa + 1′ al passo, per 3 volte1′ di corsa + 2′ al passo, per 4 volte30″ di corsa + 1′ al passo, per 3 volte5′ al passoLunedi 16 ore 21.47: Merda, oggi niente corsa, bisogna che domani ci

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com0tag:blogger.com,1999:blog-16962206.post-59728752055676722362008-11-02T11:27:00.016+01:002008-12-26T14:20:25.637+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

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com0tag:blogger.com,1999:blog-16962206.post-66727662197712713712008-10-01T12:01:00.004+02:002008-10-01T12:40:00.226+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

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com0tag:blogger.com,1999:blog-16962206.post-78406245228253210212008-09-08T19:04:00.017+02:002009-04-03T23:57:57.259+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

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com0tag:blogger.com,1999:blog-16962206.post-25682136776296764572008-07-29T15:00:00.026+02:002008-07-29T16:14:08.034+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

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com5tag:blogger.com,1999:blog-16962206.post-15745889456640047232008-07-27T12:19:00.017+02:002008-07-29T14:59:47.395+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

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com1tag:blogger.com,1999:blog-16962206.post-27104169268504665542008-07-22T15:52:00.016+02:002008-08-15T09:52:58.896+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

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com2tag:blogger.com,1999:blog-16962206.post-80103988349751000912008-07-22T12:47:00.006+02:002008-07-22T12:58:46.667+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

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com2tag:blogger.com,1999:blog-16962206.post-55405562003791515482008-07-21T15:19:00.008+02:002008-07-25T10:48:11.576+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

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com4tag:blogger.com,1999:blog-16962206.post-43537088681666646742008-07-15T22:48:00.009+02:002008-07-16T15:38:37.988+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

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com0tag:blogger.com,1999:blog-16962206.post-50840402113397132252008-06-23T17:59:00.008+02:002008-06-23T18:26:21.600+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

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com2tag:blogger.com,1999:blog-16962206.post-39873020890499107842008-06-14T15:48:00.002+02:002008-06-14T15:51:49.719+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.

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com0tag:blogger.com,1999:blog-16962206.post-74676152218201704772008-06-08T15:11:00.003+02:002008-06-08T15:14:38.349+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 ;))

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com0tag:blogger.com,1999:blog-16962206.post-14678024710785411612008-05-23T23:18:00.004+02:002008-05-23T23:33:53.916+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,

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com4tag:blogger.com,1999:blog-16962206.post-74208088178198656012008-04-25T12:21:00.010+02:002008-05-23T15:59:44.411+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==============================================================

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com2tag:blogger.com,1999:blog-16962206.post-84432454454106320532008-04-19T22:39:00.004+02:002008-04-19T22:52:15.891+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

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com0tag:blogger.com,1999:blog-16962206.post-715452128673438332008-04-06T12:55:00.003+02:002008-04-06T13:41:05.390+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

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com2tag:blogger.com,1999:blog-16962206.post-36297400626335076382008-03-30T14:03:00.008+02:002008-03-30T14:27:36.487+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

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com0tag:blogger.com,1999:blog-16962206.post-50801506428395046582008-03-24T09:13:00.004+01:002008-03-24T12:47:40.047+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

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com1tag:blogger.com,1999:blog-16962206.post-40265368166680726042008-03-16T11:30:00.006+01:002008-03-23T20:20:20.132+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

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com2tag:blogger.com,1999:blog-16962206.post-14852141978958457252008-03-09T13:56:00.013+01:002008-03-09T16:00:09.764+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

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com2tag:blogger.com,1999:blog-16962206.post-76338123302260970012008-03-01T17:52:00.014+01:002008-03-04T12:16:13.505+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

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com1tag:blogger.com,1999:blog-16962206.post-72515763690034460492008-01-06T21:12:00.002+01:002008-11-02T12:19:11.678+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

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com4tag:blogger.com,1999:blog-16962206.post-24874674819156875512007-12-26T11:20:00.000+01:002007-12-28T12:04:50.684+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

pablojhttp://www.blogger.com/profile/03043517866113096024noreply@blogger.com0