Tag: SQL

..contributing to IT in London since 2002..

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 …

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 …

Adaptable Database Design

I had an opportunity to work on a Magento implementation and checked its database. I had heard about its adaptability to almost any online e-commerce business in almost any country. So, I had a chance to see how it is achieved on the database level. For example, when we want a system which is adaptable …

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. + …

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 …

Stock Kid 2009

Another free database application from Argeus: Stock Kid 2009. Download and see it for yourself if it can help you track your inventory and costs.

VAT Kid 2009

Argeus Solutions is now giving away a free database application: VAT Kid 2009. You can download and use it for free! It is useful for small companies to enter all sales and purchase transactions without any details and get VAT return form quickly and easily. Microsoft Office Access 2003 is required. Please contact me if …

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 …

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!