Archives de catégorie : Development

Raw SQL in Ruby/Rails

Publié le par
Répondre

Everyone seems to discourage the use of raw SQL in Ruby applications and while ActiveRecord seems a good solution for general purpose CRUD operations, we have a strong expertise in SQL and we want to use it as much as possible. Here are the bits we have collected so far on the subject. We are new to Ruby/Rails so these might be obvious for some of you. We primarily write this for ourselves.

We are perfectly aware of the “limitations”. We love PostgreSQL and have no intention of being RDBMS-agnostic. At all.

find_by_sql the way we want it

We want named parameters because, despite the overhead, it’s the cleanest way to pass parameters (we don’t have to preserve a strict order which can lead to problems when enhancing the query, counting the “?”, etc). Also, when we use the same parameter several times in the same query, we don’t have to repeat it.

class ClientsController < ApplicationController

	def index
		@clients = Client.find_by_sql(
			<<-SQL
			SELECT idclient,name,email,contacts
			FROM clients 
			ORDER BY nom
			SQL
			)
	end

	def show
		@client = Client.find_by_sql(["	
			SELECT idclient,name,email,contacts 
			FROM clients 
			WHERE idclient = :idclient", 
			{ :idclient => params[:id] } 
		])[0]
	end

For the show method, we have to grab the first element in the array (thus the [0]at the end.

TdxSpreadSheet creation and filling via code

Publié le par
Répondre

Basic usage and formatting

procedure TForm1.b1Click(Sender: TObject);
Var xls:TdxSpreadSheet;
    sh: TdxSpreadSheetTableView;
begin
  xls:=TdxSpreadSheet.Create(Self);
  Try
    xls.BeginUpdate;
    xls.ClearAll;
    sh:=TdxSpreadSheetTableView(xls.AddSheet('Bénéficiaires'));

    // row,col
    if Cells[1,1]=Nil then
      CreateCell(1,1);
    Cells[Row,Col].AsString:='test';
    sh.cells[1,1].Style.Borders[bLeft].Style:=sscbsDouble;
    sh.cells[1,1].Style.Borders[bRight].Color:=clred;
    sh.Cells[1,1].Style.Brush.BackgroundColor:=clcxLightGray;
    sh.Cells[1,1].Style.AlignHorz:=ssahCenter;
    sh.Cells[1,1].Style.DataFormat.FormatCode:='dd/mm/yyyy';
    sh.Cells[1,1].Style.DataFormat.FormatCode:='# ##0.00';
    sh.Cells[1,1].Style.Font.Style:=[fsBold];

    // Formula
    CreateCell(2,1); // B3
    sh.Cells[2,1].SetText('=SUM(B2:B2)',true);

    xls.EndUpdate;
    xls.SaveToFile('C:\Users\gosane\Desktop\text.xlsx');
  Finally
    xls.Free;
  End;
end;

Helpers

function xlscolname(i:integer):string;
var a,b:integer;
begin
  if i<0 then
    result:='?'
  else if i<26 then
    result:=chr(65+i)
  else begin
    a:=i div 26;
    b:=i mod 26;
    result:=chr(64+a)+chr(65+b);
  end;
end;

Working with TPgDAC (DevArt components for Delphi)

Publié le par
Répondre

TPgLoader

interface uses DALoader,PgLoader;

Type TMyForm = Class(TForm)
       // ...
       private
         procedure PgLoader1PutData(Sender: TDALoader);
     End;

{...}

procedure TFxxx.PgLoader1PutData(Sender: TDALoader);
var i:Integer;
begin
  for i:=1 to 10 do
    Sender.PutColumnData('fieldname',i,Value);
end;


procedure TFxxx.Import;
Var pgl:TPgLoader;
begin
  pgl:=TPgLoader.Create(Application);
  Try
    pgl.Connection:=DM.PgConnection1;
    pgl.TableName:='MyTableName';
    pgl.Options.BufferSize:=65536;
    // pgl.OnProgress:=LoaderProgress;
    pgl.OnPutData:=PgLoader1PutData;
    pgl.CreateColumns;
    // Remove primary key from PgLoader
    for I := 0 to pgl.Columns.Count-1 do
      if pgl.Columns[i].Name='idprimarykey' then begin
        pgl.Columns.Delete(i);
        break;
      end;
    pgl.Load;
  Finally
    pgl.Free;
  End;
end;

Astuces Delphi

Publié le par
Répondre

Déclencher une action au démarrage de l’application

procedure TFMain.FormShow(Sender: TObject);
begin
  PostMessage(Button1.Handle,WM_LBUTTONDOWN,0,0);
  PostMessage(Button1.Handle,WM_LBUTTONUP,0,0);
end;

Créer des listes d’objets avec TObjectList

uses Contnrs;

Dans l’interface :

TSQLColumn = Class
  name     : string;
  typ      : string;
  // ...autres champs...
End;
TSqlColumnList = class(TObjectList)
  private
    function GetItem(Index: Integer): TSQLColumn;
    procedure SetItem(Index: Integer; const Value: TSQLColumn);
  public
    property Items[Index: Integer]: TSQLColumn read GetItem write SetItem; default;
end;

Dans l’implémentation :

{ TSqlColumnList }

function TSqlColumnList.GetItem(Index: Integer): TSQLColumn;
begin
  Result:=TSqlColumn(inherited GetItem(Index));
end;

procedure TSqlColumnList.SetItem(Index: Integer; const Value: TSQLColumn);
begin
  inherited setItem(index, value);
end;

Utilisation :

TMyClass = Class
  FColumns        : TSqlColumnList;
  Constructor Create;
  Destructor Destroy; reintroduce;
End;

{ TcatRequete }

constructor TcatRequete.Create;
begin
  FColumns := TSqlColumnList.Create;
end;

destructor TcatRequete.Destroy;
begin
  FColumns.Free;
end;

Et pour remplir la liste :

Var column : TSqlColumn;
begin
  FColumns.Clear;
  // Ajouter un élément
  column:=TSqlColumn.Create;
  FColumns.Add(Column);
  With column do begin
    name := 'xxx';
  end;
end;