Thursday, April 19, 2018

DELPHI - How check if OLE library is installed on client

When you use some special OLE object, you can check, if is installed on client (Winapi.ActiveX):
var
  classID: TCLSID;
  sOLEObject: string;
  bIsSupport : boolean;
begin
  { is installed ? }

  sOLEObject := 'Microsoft.DirectMusic.1';
  bIsSupport := CLSIDFromProgID( PWideChar( WideString( sOLEObject ) ), classID ) = S_OK;

  if bIsSupport then ShowMessage( 'Supported.' );
  ...

AX - How to work with advanced filters (..with sql where condition)

When you need some special filters, especially in relation to another field(s) from table, you can write directly sql where code.

This example shows all production orders, where difference between Delivery date and End date is 10 days:

Wednesday, April 18, 2018

SQL SERVER - How and why use "N" prefix for unicode strings

N prefix before string takes string as unicode (without N is string converted to DB codepage). It is useful for some special chars in some alphabets.
select 'Počty vzrůstajících kusů' as description
union all
select N'Počty vzrůstajících kusů'
Output:
description
------------------------
Pocty vzrustajících kusu
Počty vzrůstajících kusů

(2 row(s) affected)

Tuesday, April 17, 2018

DELPHI - How to create new directory (..and check if directory exists)

var
  sBackupPath : string;
begin
  ...
  sBackupPath := gsExePath + gcsDictionarySubDir;

  { check if not exists }
  if not DirectoryExists( sBackupPath ) then
    begin
      { try to create new directory }

      b := ForceDirectories( sBackupPath );

      if not b then
        begin
          ShowMessage( 'Backup directory cannot be created.' );
          exit;
        end;
    end;
...

Thursday, April 12, 2018

DELPHI - How to make tray icon with WIN API support only (without TTrayIcon)

trayIconData : TNotifyIconData;
...
{ add icon }
with TrayIconData do
begin
  cbSize := sizeOf( trayIconData );
  Wnd := Handle;
  uID := 0;
  uFlags := NIF_MESSAGE + NIF_ICON + NIF_TIP;
  uCallbackMessage := WM_ICONTRAY;
  hIcon := Application.Icon.Handle;
  StrPCopy( szTip, self.Caption );
end;

Shell_NotifyIcon( NIM_ADD, @TrayIconData );
And callback procedure:
procedure TFMain.WMIconTray( var msg : TMessage );
var
  pt : TPoint;
begin
  case msg.lParam of
    { popup menu }
    WM_RBUTTONDOWN:
      begin
         GetCursorPos( pt );
         PopupTray.Popup( pt.x, pt.y );
      end;

    { doubleclick }
    WM_LBUTTONDBLCLK :
      begin
        MPopup_NextClick( nil );
      end;

    { mouse over }
    WM_MOUSEMOVE :
      begin
        self.Caption := gcsTitle + GetNextWordTime;

        with TrayIconData do
        begin
          StrPCopy( szTip, self.Caption );
          uFlags := NIF_TIP;
          Shell_NotifyIcon( NIM_MODIFY, @TrayIconData );
        end;
        
      end;
   end;
end;
And removing after destroy:
procedure TFMain.FormDestroy(Sender: TObject);
begin
  ...
  Shell_NotifyIcon( NIM_DELETE, @trayIconData );
end;
Output:

Wednesday, April 11, 2018

AX - How to round values

Second parameter from round() function define how will be rounded.
real iValue = 15.5678532;
real iResult1, iResult2;
    
// round to 0.1
iResult1 = round( iValue, 0.1 );
// round to all number */
iResult2 = round( iValue, 1 );
        
info( num2str( iResult1, 15, 2, 1, 0 ) + " - " + num2str( iResult2, 15, 2, 1, 0 ) );
Output:
15.60 - 16.00

DELPHI - How to make rotation of shape around point

Suppose this easy class for shape - triangle:
TShape = class( TObject )
  p1 : TPoint;
  p2 : TPoint;
  p3 : TPoint;

  procedure Moving( _iDeltaX, _iDeltaY : integer );
  procedure Rotation( _iAngle : integer );
  procedure RotationAroundXY( _x, _y : integer; _iAngle : integer );
end;
And initialization:
pShape := TShape.Create;
pShape.p1.X := 300;  pShape.p1.Y := 250;
pShape.p2.X := 250;  pShape.p2.Y := 280;
pShape.p3.X := 350;  pShape.p3.Y := 330;
For rotation shape around point use this code:
procedure TShape.RotationAroundXY( _x, _y : integer; _iAngle : integer );
var
  iNewX, iNewY : integer;
  dCos, dSin : double;
  dRadian : double;
  p1x, p1y, p2x, p2y, p3x, p3y : integer;
begin
  { -- angle }

  dRadian := ( 2 * pi ) / ( 360 / _iAngle );
                     
  dCos := cos( dRadian );
  dSin := sin( dRadian );

  { -- point for rotation around (in other case the rotation is around start of coords) }

  p1x := p1.x - _x;
  p1y := p1.y - _y;
  p2x := p2.x - _x;
  p2y := p2.y - _y;
  p3x := p3.x - _x;
  p3y := p3.y - _y;

  { -- calculation of the positions }

  iNewX := round( ( p1x * dCos ) - ( p1y * dSin ) );
  iNewY := round( ( p1y * dCos ) + ( p1x * dSin ) );
  p1.X := iNewX + _x;
  p1.Y := iNewY + _y;

  iNewX := round( ( p2x * dCos ) - ( p2y * dSin ) );
  iNewY := round( ( p2y * dCos ) + ( p2x * dSin ) );
  p2.X := iNewX + _x;
  p2.Y := iNewY + _y;

  iNewX := round( ( p3x * dCos ) - ( p3y * dSin ) );
  iNewY := round( ( p3y * dCos ) + ( p3x * dSin ) );
  p3.X := iNewX + _x;
  p3.Y := iNewY + _y;
end;
Output:

Tuesday, April 3, 2018

JAVA, JAVAFX - listener and lambda expression

Since JDK 1.8 you can use lambda expression. This article compares old and new variants on one example with CheckBox listener.

Old variant (response for change in CheckBox):
@FXML CheckBox checkbox1;
@FXML Label label1;

@Override
public void initialize( URL url, ResourceBundle rb ) {               

  /* variant without lambda expression */
    
  checkbox1.selectedProperty().addListener( new ChangeListener< boolean >() {
    @Override
    public void changed( ObservableValue observable, Boolean oldValue, Boolean newValue ) {
      label1.setText( newValue.toString() );
    }
  } );        
        
  /* -- init */
    
  checkbox1.selectedProperty().set( true );    
}  
And new (optional - old variant is still working) with lambda expression:
@FXML CheckBox checkbox1;
@FXML Label label1;
  
@Override
public void initialize( URL url, ResourceBundle rb ) {               
   
  /* variant with lambda expression */
    
  checkbox1.selectedProperty().addListener(
    ( ObservableValue observable, Boolean oldValue, Boolean newValue ) -> {
      label1.setText( newValue.toString() );
      }
    );    
    
  /* -- init */
    
  checkbox1.selectedProperty().set( true );    
}  

Both variant have same result:

SSRS - How disable fitting chart axis (..to more columns)

Sometimes SSRS chart left axis is fitted to more columns.

For solving that, set left axis property LabelsAutoFitDisabled to true.

SQL SERVER - How get database instance version

print @@version
Output:
Microsoft SQL Server 2014 (SP2-GDR) (KB4019093) - 12.0.5207.0 (X64) 
 Jul  3 2017 02:25:44 
 Copyright (c) Microsoft Corporation
 Express Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

Thursday, March 29, 2018

DELPHI - How get greatest common divisor (for two digits)

The function returns greatest common divisor for two digits. When it does not found it, returns 1.
function GCD( _iNumber1, _iNumber2 : integer ) : integer;
var
  iTemp : integer;
begin
  if _iNumber1 < 0 then _iNumber1 := -_iNumber1;
  if _iNumber2 < 0 then _iNumber2 := -_iNumber2;

  repeat

    if _iNumber1 < _iNumber2 then
      begin
        iTemp := _iNumber1;
        _iNumber1 := _iNumber2;
        _iNumber2 := iTemp;
      end;

    _iNumber1 := _iNumber1 mod _iNumber2;

  until ( _iNumber1 = 0 );

  result := _iNumber2;
end;
Calling:
var
  i : integer;
begin
  i := GCD( 12, 16 );

  ShowMessage( IntToStr( i ) );
end;
Output:

Wednesday, March 28, 2018

DELPHI - How to show chi-square distribution curve

Propability density function (chi-square distribution).
var
  i, iK : integer;
  iX, iY, iGamma, iNumerator, iDenominator : double;
  pSerie : TLineSeries;
  pIntegral : TIntegral;
begin
  Memo1.Clear;

  pSerie := TLineSeries( Chart1.Series[0] );
  pSerie.Clear;

  pIntegral := TIntegral.Create;
  pIntegral.iStep := 0.05;
  pIntegral.SetInterval( 0.001, 20 );

  { -- set param + calc GAMMA function }

  iK := 8;
  iGamma := GetGamma( iK/2 );

  for i := 0 to pIntegral.pX.Count - 1 do
    begin
      iX := pIntegral.pX.GetValue( i );

      try
        iNumerator := power( iX, iK/2-1 ) * exp( ( -1*iX ) /2 );
        iDenominator := power( 2, iK / 2 ) * iGamma;

        if iDenominator <> 0 then
          iY := iNumerator / iDenominator
        else
          iY := 0;
      except
        iY := 0;
      end;
      pIntegral.pY.AddValue( iY );

      { -- add to chart }

      pSerie.AddXY( iX, iY );
    end;
end;
And here is very important Gamma() function with k parameter.
function GammaStirF( X : double ) : double;
var
  y : double;
  w : double;
  v : double;
  stir : double;
begin
  w := 1 / x;
  stir := 7.87311395793093628397E-4;
  stir := -2.29549961613378126380E-4 + w * stir;
  stir := -2.68132617805781232825E-3 + w * stir;
  stir := 3.47222221605458667310E-3 + w * stir;
  stir := 8.33333333333482257126E-2 + w * stir;
  w := 1 + w * stir;
  y := exp(x);
  if x > 143.01608 then
    begin
      v := power( x, 0.5 * x -0.25 );
      y := v *( v / y );
    end
  else
    begin
      y := power( x, x -0.5 ) / y;
    end;
  result := 2.50662827463100050242 * y * w;
end;

function GetGamma( _x : double ) : double;
var
  p : double;
  PP : double;
  q : double;
  QQ : double;
  z : double;
  i : longint;
  SgnGam : double;
begin
  SgnGam := 1;
  q := abs( _x );
  if q > 33.0 then
  begin
    if _x < 0.0 then
    begin
      p := floor(q);
      i := round(p);

      if i mod 2 = 0 then
      begin
        SgnGam := -1;
      end;

      z := q - p;

      if z > 0.5 then
      begin
        p := p+1;
        z := q-p;
      end;

      z := q * sin( pi * z );
      z := Abs(z);
      z := pi / ( z * GammaStirF( q ) );
    end
    else
    begin
      z := GammaStirF( _x );
    end;
    result := SgnGam * z;
    exit;
  end;
  z := 1;
  while _x >= 3 do
  begin
    _x := _x - 1;
    z := z *_x;
  end;
  while _x < 0 do
  begin
    if _x > -0.000000001 then
    begin
      result := z / ( ( 1 + 0.5772156649015329 *_x ) * _x );
      exit;
    end;
    z := z /_x;
    _x := _x + 1;
  end;
  while _x < 2 do
  begin
    if _x < 0.000000001 then
    begin
      result := z / ( ( 1 + 0.5772156649015329 * _x ) *_x );
      exit;
    end;
    z := z /_x;
    _x := _x + 1.0;
  end;
  if _x = 2 then
  begin
    result := z;
    exit;
  end;

  _x := _x - 2.0;
  PP := 1.60119522476751861407E-4;
  PP := 1.19135147006586384913E-3 + _X * PP;
  PP := 1.04213797561761569935E-2 + _X * PP;
  PP := 4.76367800457137231464E-2 + _X * PP;
  PP := 2.07448227648435975150E-1 + _X * PP;
  PP := 4.94214826801497100753E-1 + _X * PP;
  PP := 9.99999999999999996796E-1 + _X * PP;
  QQ := -2.31581873324120129819E-5;
  QQ := 5.39605580493303397842E-4 + _X * QQ;
  QQ := -4.45641913851797240494E-3 + _X * QQ;
  QQ := 1.18139785222060435552E-2 + _X * QQ;
  QQ := 3.58236398605498653373E-2 + _X * QQ;
  QQ := -2.34591795718243348568E-1 + _X * QQ;
  QQ := 7.14304917030273074085E-2 + _X * QQ;
  QQ := 1.00000000000000000320 + _X * QQ;

  result := z * PP / QQ;
  exit;
end;
Output:
k=2
k=8

Tuesday, March 27, 2018

Thursday, March 22, 2018

DELPHI - How to show normal distribution curve (Gaussian)

Typical propability density function (Gaussian distribution).
var
  i : integer;
  iY : double;
  pDN : TDistribution_Normal;
  pSerie : TLineSeries;
begin
  Memo1.Clear;

  /* prepare chart */

  pSerie := TLineSeries( Chart1.Series[0] );
  pSerie.Clear;

  /* prepare distribution data */

  pDN := TDistribution_Normal.Create;
  pDN.Init( 0, 1 );

  for i := 0 to pDN.pIntegral.pY.Count - 1 do
    begin
      { -- get value }

      iY := pDN.pIntegral.pY.GetValue( i );

      pSerie.AddXY( i, iY );
    end;

And here is very important TDistribution_Normal.Init() function with parameters as MEAN and SIGMA, here is generated range MEAN +-3.5 SIGMA.
The sum values under curve is equeal to 1.
function TDistribution_Normal.Init( _iMean, _iSigma : double ) : boolean;
var
  i : integer;
  iFrom, iTo, iValue : double;
begin
  result := false;

  { -- check }

  if _iSigma <= 0 then
    begin
      ShowMessage( 'Distribution_Normal() - standard deviation can`t be 0.' );
      exit;
    end;

  { -- add range -3.5 sigma - Mean - + 3.5 sigma }

  pIntegral.Clear;

  iFrom := _iMean - ( 3.5 * _iSigma );
  iTo := _iMean + ( 3.5 * _iSigma );
  { aproximation - 300 items }
  pIntegral.iStep := ( iTo - iFrom ) / 300;
  pIntegral.SetInterval( iFrom, iTo );

  { -- calc values normal distribution for every X }

  for i := 0 to pIntegral.pX.Count - 1 do
    begin
      iValue := ( 1 / ( _iSigma * sqrt( 2*pi ) ) ) * 
                exp( - ( sqr( pIntegral.pX.GetValue( i ) - _iMean ) ) / 
                ( 2 * sqr( _iSigma ) ) );

      pIntegral.pY.AddValue( iValue );
    end;

  pIntegral.Calc;

  result := true;
end;
Output:

Wednesday, March 21, 2018

JAVAFX - How fill and work with PieChart (graph)

import javafx.scene.chart.PieChart;
...
@FXML PieChart pieChart1;
...
/* set text */
    
pieChart1.setTitle( "This is a chart" );        

/* add data */
    
ObservableList series = FXCollections.observableArrayList(); 
series.add( new PieChart.Data( "Q1", 5 ) );
series.add( new PieChart.Data( "Q2", 5.5 ) );
series.add( new PieChart.Data( "Q3", 8 ) );
series.add( new PieChart.Data( "Q4", 3 ) );
         
pieChart1.setData( series ); 
Output:

JAVAFX - How fill and work with BarChart (graph)

import javafx.scene.chart.BarChart;
...
@FXML BarChart barChart1;
...
/* set texts */
    
barChart1.setTitle( "This is a chart" );
    
barChart1.getXAxis().setLabel( "x axis" );
barChart1.getYAxis().setLabel( "y axis" );
   
barChart1.setLegendVisible( false );

/* add data */
    
XYChart.Series series1 = new XYChart.Series();    
series1.getData().add( new XYChart.Data( "Q1", 5 ) );
series1.getData().add( new XYChart.Data( "Q2", 5.5 ) );
series1.getData().add( new XYChart.Data( "Q3", 8 ) );
series1.getData().add( new XYChart.Data( "Q4", 3 ) );        
         
barChart1.getData().add( series1 );       
Output:

Tuesday, March 20, 2018

DELPHI - How get hash value for file (SHA-1 fingerprint)

Hash function returns same value for same (in this case) file. This uses SHA-1 (Secure Hash Algorithm) algorithm.
uses IdHashMessageDigest;
...
var
  sFile : string;
  pSHA : TIdHashSHA1;
  pStream : TFileStream;
begin
  sFile := 'c:\_ax\error.png';

  pSHA := TIdHashSHA1.Create;
  pStream := TFileStream.Create( sFile, fmOpenRead or fmShareDenyWrite );

 try
   ShowMessage( 'File fingerprint = ' + pSHA.HashStreamAsHex( pStream ) );
 finally
   pStream.Free;
   pSHA.Free;
 end;
Output:

JAVAFX - How make "raw" object centering in the form (with properties binding)

public class FXMLMainController implements Initializable {
  
  @FXML private Label lbl1;
  
  @Override
  public void initialize( URL url, ResourceBundle rb ) {               
  }  
  
  /* Method for label centering */

  public void prepare() {
    Scene scene = lbl1.getScene();       
  
    if ( scene != null ) {
      lbl1.layoutXProperty().bind( scene.widthProperty().subtract( 
        lbl1.layoutBoundsProperty().get().getWidth()).divide( 2 ) );
      
      lbl1.layoutYProperty().bind( scene.heightProperty().subtract( 
        lbl1.layoutBoundsProperty().get().getHeight()).divide( 2 ) );
    }
    
  }
}
Method prepare() must be called after creating Scene class instance:
@Override
    public void start(Stage stage) throws Exception {

        FXMLLoader fLoader = new FXMLLoader( getClass().getResource( "FXMLMain.fxml" ) );
        Parent root = fLoader.load();
        FXMLMainController controller = fLoader.< fxmlmaincontroller >getController();      
        
        Scene scene = new Scene(root);
        
        stage.setScene(scene);
        stage.show();

        /* needs to be called here */
        controller.prepare();
    }
Output:

Monday, March 19, 2018

DELPHI - How get hash value for string (SHA-1 fingerprint)

Hash function returns same value for same string. This uses SHA-1 (Secure Hash Algorithm) algorithm.
uses IdHashSHA;
...
var
  pSHA : TIdHashSHA1;
begin
  pSHA := TIdHashSHA1.Create;

  try
    ShowMessage( 'abc = ' + pSHA.HashStringAsHex( 'abc' ) + #13 +
                 'abc = ' + pSHA.HashStringAsHex( 'abc' ) + #13 +
                 'abd = ' + pSHA.HashStringAsHex( 'abd' ) );
  finally
    pSHA.Free;
  end;
Output:

Friday, March 16, 2018

DELPHI - How get MD5 hash value for string (RSA-MD5 fingerprint)

Hash function returns same value for same string. This uses RSA-MD5 algorithm.
uses IdHashMessageDigest;
...
var
  pMD5: TIdHashMessageDigest5;
begin
  pMD5 := TIdHashMessageDigest5.Create;
  try
    ShowMessage( 'abc = ' + pMD5.HashStringAsHex( 'abc' ) + #13 +
                 'abc = ' + pMD5.HashStringAsHex( 'abc' ) + #13 +
                 'abd = ' + pMD5.HashStringAsHex( 'abd' ) );
  finally
    pMD5.Free;
  end;
Output:

Thursday, March 15, 2018

AX - How check free space in sequences (=if sequences have space for growing)

Sometimes you need to know, which sequences are full - or near to full.
This SQL script shows free space in sequences in percent.
select a.* 
from
(
select a.*, 
cast( cast( a.[free] as numeric(15,2) ) / cast( a.[space] as numeric(15,2) ) * 100 
as numeric(15,2) ) as [free_%] 
from
(
select a.numbersequence, a.lowest, a.highest, a.nextrec, 

a.highest - a.lowest as [space],
a.highest - a.nextrec as [free]

from numbersequencetable a
where
a.blocked = 0 and
a.cyclic = 0
) a
) a
order by a.[free_%]
Output:
numbersequence lowest      highest     nextrec     space       free        free_%
-------------- ----------- ----------- ----------- ----------- ----------- --------
Inve_38        1           999999      703701      999998      296298      29.63
Inve_33        1           99999       31366       99998       68633       68.63
Gene_24        1           99999       29841       99998       70158       70.16
Time_6         1           9999999     2665830     9999998     7334169     73.34
Docu_3         1           99999       19037       99998       80962       80.96
Prod_26        1           9999999     1327561     9999998     8672438     86.72
Prod_27        14000       999999      130299      985999      869700      88.20

Wednesday, March 14, 2018

WIN OS - How show user accounts dialog

..for example for possibility to change saved passwords.

From command line:
control userpasswords2
Output:

SQL SERVER - How update statistics for all DB tables

use framework;
exec sp_updatestats;
Output:
Updating [dbo].[sql_detail_graphs]
    [PK__sql_deta__3213E83F07EFD247] has been updated...
    [_WA_Sys_00000002_00507F52] has been updated...
    [_WA_Sys_0000000A_00507F52] has been updated...
    [_WA_Sys_00000009_00507F52] has been updated...
    [_WA_Sys_00000003_00507F52] has been updated...
    5 index(es)/statistic(s) have been updated, 0 did not require update.
 
Updating [dbo].[sql_zal]
    0 index(es)/statistic(s) have been updated, 0 did not require update.
...
Statistics for all tables have been updated.

Tuesday, March 13, 2018

SQL SERVER - INSERT trigger

Sometimes you need to perform some actions immediately after you insert row to table. For this purpose you can use insert trigger on the table.
Pseudo-table named "inserted" contains inserted data row - you probably utilize it here.
ALTER trigger [dbo].[sql_insert]
on [dbo].[sql]
for insert
as
  begin transaction;
  
  begin try

    /* -- add new initial role */

    insert into [role_object]
    ( ident, [description], [role_object_type_ident] )
    select inserted.id, null, 'Q' from inserted;  
  
    /* -- add log row about creation to log table */
  
    insert into sql_detail_changes
    ( sql_id, [description], [type] )
    select inserted.id, 'Creation', 'I' from inserted;  
    
    if @@TRANCOUNT > 0 commit transaction;      
  end 

  /* -- problem ? */

  try begin catch
    if @@TRANCOUNT > 0 rollback transaction;      
  end catch;  

Monday, March 12, 2018

JAVA - How to split string to parts (java.util.StringTokenizer)

Another variant for string split - class java.util.StringTokenizer:
StringTokenizer pSpaceDelim = new StringTokenizer( "This is a text." );
           
System.out.println( "Count = " + pSpaceDelim.countTokens() + System.lineSeparator() );
    
while ( pSpaceDelim.hasMoreTokens() )
  System.out.println( pSpaceDelim.nextToken() );
Output:
Count = 4

This
is
a
text.
NOTE: System.lineSeparator() is system dependent line separator.