Firebird: bulk insert and bulk export

| category: Programming | author: st
Tags:

Firebird doesn't support INSERT BULK command or bcp.exe utility. But the external tables can be a good replacement. Simple example is below.

Check that firebird.conf file allow to create external tables. Write or uncomment line like

ExternalFileAccess = Full

Create external table

CREATE TABLE BCP_DATA EXTERNAL FILE 'с:\temp …

Delphi/Free Pascal: very simple "old school" mutex

| category: Programming | author: st
Tags: ,

The code below show how to implement very simple but cross-platform "old school" mutex (mutually exclusive semaphore)

interface

type
  TMutex = class
  private
    FFileHandle: integer;
  public
    constructor Create(const AName: string; const WaitForMSec: integer = 10000);
    destructor Destroy; override;
  end;

implementation

uses
  Classes, SysUtils, DateUtils,
  {$IFDEF MSWINDOWS}
  Windows
  {$ENDIF};

function GetTempDir: string …

Fast inserting a file into a table

| category: Programming | author: st
Tags:

How to insert fast a file (i.e. binary one) from disk to a SQL Server table?

Certainly the SQL Server account should have a corresponding permissions to access the file on the disk. For example I take the file C:\WINDOWS\system32\oembios.bin having the size about 12 …

SQL: generate date/time series

| category: Programming | author: st
Tags:

There are many examples of user defined table functions generating the series of dates(times) between two specified values. Usually, in OLAP you can see a table filled by the date/time series, too. Such table avoid to generate the values "on the fly".

However, in some cases you may …

SQL: delete duplicated rows

| category: Programming | author: st
Tags:

How to delete the rows having the duplicated values of one or more columns and considered as duplicates? The example in Transact SQL is below.

SET NOCOUNT ON;
CREATE TABLE #t (
  product_name nvarchar(20),
  vendor_name nvarchar(20)
)
GO
INSERT INTO #t (product_name, vendor_name) VALUES
('SQL Server', 'Microsoft'),
('Oracle', 'Oracle'),
('DB2 …

Transact SQL: exporting table or view in CSV

| category: Programming | author: st
Tags:

It's pretty easy on SQL Server but pay attention to the database/server rights of used connection. Usually, the preparing like sp_configure requires a system administrator privileges.

Activate "xp_cmdshell" option (only once)

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

Create and run wrapping stored procedure …

DateTime in C++

| category: Programming | author: st
Tags:

Many years ago we had a need to manipulate the data of types "date" and "time" (DateTime in C# and Delphi/C++Builder). I had developed the corresponding class for these purposes.

Here is the interface with comments:

/*
 * Initializes the object with the values taken from the formatted string like …

GUI: evolution of descriptions

| category: My notes | author: st
Tags:

Let's see one simple example showing how the methods and the languages of graphical user interface (GUI) description are progressing (or regressing, it depends) during the last 15-20 years.

The primitive form description requires:

1996: Delphi 2

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Test form'
  ClientHeight = 131
  ClientWidth …

Indexing columns of bit type

| category: Programming | author: st
Tags:

Clearly, an index build on only one column of the bit type is useless because the selectivity is poor (50% of a table in average). However, when a table has several columns of the bit type, the composite index may be efficient.

For the test example, we'll create a table …

Dynamic filtering with SQL

| category: My notes | author: st
Tags:

The one of the most frequent asked question in database development is "How to filter the data returned by a query according to some user selected criteria?". To complete the solution with dynamic ordering see my other blog post.

For every method template I will add icons indicating corresponding advantages …