Full-text search using PostgreSQL: simple, fast, cached

Let’s find out what can we achieve; using what we already have, without adding other layers, servers or applications: PostgreSQL. It will help us keep the architecture simple, please be patient 🙂 We need to prepare records for full-text search using 1-to-1 relationship for relevant records in other tables with search term vector from one … Read moreFull-text search using PostgreSQL: simple, fast, cached

PostgreSQL and Node.js – 100% JavaScript Adventure

This is a log of every step I will take to create a 100%-JavaScript application back to front. Hardware: Windows 7 64bit Home Edition ASUS laptop with Intel P6100 CPU @2.0GHz, 4GB RAM, 320GB HDD Oracle VirtualBox 4.2.12 Ubuntu 12.04.2 – Long term support Downloaded 32bit ISO image – laptop does not support 64bit virtualisation … Read morePostgreSQL and Node.js – 100% JavaScript Adventure

Show MS Access Subreports With No Data

On MS Access applications, usually I use subreport objects with SQL queries of their own, linked to the main report which includes the subreport. Classic example of such master-detail reports is invoices: + master (main report) section includes invoice date, invoice number and details of the customer account such as name and address lines. + … Read moreShow MS Access Subreports With No Data

Paging records in MS SQL Server 2005

After years of torturing their SQL Server users, Microsoft provided a “better” way of paging records in version 2005, and it is a weird one! The following example is from their website, an article about ROW_NUMBER (Transact-SQL).

It is simply unacceptable, non-standard, impractical! By the way, you can not use column numbers (e.g. ORDER … Read morePaging records in MS SQL Server 2005

MS SQL Server: Temporary Tables

Just a way of using temporary tables in Microsoft SQL Server, if you want to duplicate one or more records quickly, you can use commands like this:

This is much shorter if you have 50 fields in your source table. Otherwise, you have to specify each field and their data types;

MS Access Reports: Sums from distinct group headers

The problem: When we use formulas on textboxes in the report footers of MS Access reports, such as “=sum(a_field_in_header)”, we get incorrect results because of duplication of same amounts for multiple items in detail section. Here is an example to make it clear: We have two tables one for header and another detail records, for … Read moreMS Access Reports: Sums from distinct group headers

MS SQL Anomalies 3

For those who are still using ancient data types such as CHAR be carefull if you are also using REPLACE function in MS SQL servers: Let’s test VARCHAR and CHAR fields on a simple table like the following:

The output is: (version MS SQL Server 2000) As usual, Microsoft developers are full of surprises!