Tuesday, July 3, 2018

POWERSHELL - How write colored text output

write-host 'This is with red backgroud' -BackgroundColor Red
write-host '..and this is with yellow foretext' -ForegroundColor Yellow
Output:

Monday, July 2, 2018

AX - How to run AX from command line with another language

..here it run it in Russian language:
"C:\Program Files (x86)\Microsoft Dynamics AX\60\Client\Bin\Ax32.exe" -language=ru

Sunday, July 1, 2018

AX - How to use "display" method in forms datasource

This is useful if you need find some value in form. Parameter is source type for DataSource.

public display ItemNameDisplay ItemName( vm_data_detail _vm_data_detail )
{
    return ProdTable::find( _vm_data_detail.ProdId ).ItemName;
}

Friday, June 29, 2018

SSRS - How add to TextBox HTML formatting support

When you need use HTML formatting in TextBox item:

1) Select text (not only click on item, you must select it).
2) Right mouse click, properties (placeholder).
3) On General page set formatting to HTML. Now you can use base HTML tags in source string.

SSRS - How return substring only to first occurrence of specified char

When field ItemId contains for example value "ABC-156032_0_0000" and you want only first part of this string to first occurrence of "_" char, use this code for expression:
=Mid( First( Fields!ItemId.Value, "ProdRouteCardDS" ), 
1, InStr( First(Fields!ItemId.Value, "ProdRouteCardDS" ), "_" ) - 1 )
Output will be:
ABC-156032

Wednesday, June 27, 2018

AX - How to split path to file - to path, filename and extension

static void test_job(Args _args)
{
    Filename filepath;
    Filename filename;
    Filename filetype;

    /* get info */

    [ filepath, filename, filetype ] = Global::fileNameSplit( "c:\\temp\\readme.txt" );
    
    /* show result */
    
    info( filepath );
    info( filename );
    info( fileType );  
}
Output:

Friday, June 22, 2018

POWERSHELL - How to solve "import-module : the specified module 'activedirectory' was not loaded because no valid module file was found in any module directory." error

When you get for this powershell command,
Import-Module ActiveDirectory 
this error:
import-module : the specified module 'activedirectory' was not 
loaded because no valid module file was found in any module directory.
, you have to install "Active Directory Management Gateway Service" at first, and then run command again.

The command get for you access to AD powershell functions.

SQL SERVER - How to solve problem "OLE DB error: OLE DB or ODBC error: Login failed for user" on BI cubes processing

This error occurs, when user (here NT SERVICE\MSOLAP$TSTINST) have not access to source DB.


























Solution: Add to user db_datareader rights.

Tuesday, June 19, 2018

AX - How to solve Error "This installation package could not be opened" (Visual Studio Tools)

This error can occur in installation "Visual Studio Tools" for AX 2012.

When you have VS 2013 (Professional and higher), installation is supported only from CU8.

-> check, if you have CU8 subdir in upgrade directory (in install AX 2012 R3 directory).

DELPHI - How to work with TDirectory (generic)

TDictionary is collection of key-value. In this example is it used for saving previous values - and its refreshing.
procedure TSQLParams.LoadForSQL( _iSQL_ID : integer );
var
  i : integer;
  b : boolean;
  vValue : variant;
  pParam : TSQLParam;
  pSQLParam : TSQLParam;
  pOldValues : TDictionary < string, variant >;
begin

  pOldValues := TDictionary < string, variant >.Create;

  try
    { -- save old values of params }

    for i := 0 to self.Count - 1 do
      begin
        pParam := TSQLParam( Items[ i ] );

        if not VarIsNull( pParam.vValue ) then
          pOldValues.Add( pParam.sIdent, pParam.vValue );
      end;

    { -- clear all }

    clear;

    { - get info  }

    if DB_SP.sp_params_by_sql_id.Active then DB_SP.sp__params_by_sql_id.Close;
    DB_SP.sp_params_by_sql_id.ParamByName( '@sql_id' ).AsInteger := _iSQL_ID;
    DB_SP.sp_sql_detail_params_by_sql_id.Open;

    DB_SP.sp_params_by_sql_id.First;
    while not DB_SP.sp_params_by_sql_id.Eof do
      begin
        { create new param }

        pSQLParam := TSQLParam( self.Add );
        pSQLParam.sIdent := DB_SP.sp_params_by_sql_id.FieldByName( 'ident' ).AsString.ToLower;
        pSQLParam.vValue := Variants.null;

       { - try to find old saved value }

       b := pOldValues.TryGetValue( pSQLParam.sIdent, vValue );
       if b then pSQLParam.vValue := vValue;

       { - next row }

       DB_SP.sp_params_by_sql_id.Next;

     end;

  finally
    pOldValues.Free;
  end;

end;

Monday, June 18, 2018

Monday, June 11, 2018

DELPHI - How to split string with TStringList class support

For string split you can use class TStringList.
var
  i : integer;
  sResult : string;
  pSplit : TStringList;
begin
  pSplit := TStringList.Create;
  try
    { define delimiter }
    pSplit.Delimiter := ';';

    { - split it }

    pSplit.Clear;
    pSplit.DelimitedText := 'This;is;a;list';

    { - list it }

    sResult := '';
    for i := 0 to pSplit.Count - 1  do
      begin
        sResult := sResult + pSplit[i] + #13;
      end;

    ShowMessage( sResult );

  finally
    pSplit.Free;
  end;
Output:

Friday, June 1, 2018

POWERSHELL - Send output to file (with appending)

This example send first output to bothdirs.txt; second line with ">>" appends second output to end of file.
dir c:\tmp > c:\tmp\bothdirs.txt;
dir C:\Intel >> c:\tmp\bothdirs.txt;

notepad c:\tmp\bothdirs.txt;
Output:
Directory: C:\tmp

Mode                LastWriteTime     Length Name                                                                                                                        
----                -------------     ------ ----                                                                                                                        
d----       24. 5. 2018     11:05            png                                                                                                                         
-a---        1. 6. 2018     10:50          0 bothdirs.txt                                                                                                                
-a---       31. 5. 2018     13:54       8304 export.txt                                                                                                                  

    Directory: C:\Intel

Mode                LastWriteTime     Length Name                                                                                                                        
----                -------------     ------ ----                                                                                                                        
d----       14. 7. 2017     15:44            ExtremeGraphics                                                                                                             
d----       23. 5. 2018     12:47            gp                                                                                                                          
d----       14. 7. 2017     15:41            Logs                                                                                                                        

Friday, May 4, 2018

DELPHI - How ensure (system) path delimiter to end of file path

One of the good technique to ensure, that file path is ended with system delimiter.
var
  sMask : string;
begin
  ..
  sMask := IncludeTrailingPathDelimiter( ExtractFilePath( Application.ExeName ) ) + 
  '*' + '.txt';
Output (should be):
c:\dir\*.txt

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.

SQL SERVER - How use COLLATE over special chars

Sometimes (dependent how is your DB created) you need specify character set for some command that used special text chars. Here, for example in WHERE section.
Without COLLATE..
select count( * ) as rows
from salesline a
where
a.itemid = 'PŘEPRAVNÉ' and
a.confirmeddlv >= '2018/02/01' and
a.confirmeddlv <= '2018/02/07'
Output:
rows
-----------
0

(1 row(s) affected)
..you get empty result set.

But with COLLATE..
select count( * ) as rows
from salesline a
where
a.itemid collate latin1_general_ci_ai = 'PŘEPRAVNÉ' and
a.confirmeddlv >= '2018/02/01' and
a.confirmeddlv <= '2018/02/07'
Output:
rows
-----------
4

(1 row(s) affected)
..it is something else.

NOTE: Collate DB parameter you can get from DB properties.

Friday, March 9, 2018

DELPHI - How compute integral - (sin(x))^2 dx from 0 to PI

For integration is used class TIntegral which use for calculation trapezoidal rule.
procedure TForm1.BIntegralClick(Sender: TObject);
var
  i : integer;
  iX, iY : double;
  pIntegral : TIntegral;
  pSerie : TLineSeries;
begin
    pSerie := TLineSeries( Chart1.Series[0] );
    pSerie.Clear;

    { -- prepare integral class - interval 0..PI }

    pIntegral := TIntegral.Create;
    pIntegral.SetInterval( 0, pi );

    for i := 0 to pIntegral.pX.Count - 1 do
      begin
        { calc Y value }
        pIntegral.pY.AddValue( sqr( sin( pIntegral.pX.GetValue( i ) ) ) );

        { -> to graph }
        iX := pIntegral.pX.GetValue( i );
        iY := pIntegral.pY.GetValue( i );
        pSerie.AddXY( iX, iY );
      end;

    ShowMessage( FloatToStr( pIntegral.Calc ) );        // 1.57

    pIntegral.Free;
end;
Here is TIntegral.calc() method:
{ ---------------------------------------------------------------------------
  Method computes integral value (trapezoidal rule).  
  -------------------------------------------------------------------------- }
function TIntegral.Calc( _iCheckSumIntegralValue : double = 999 ) : double;
var
  i, iDeleteXFrom : integer;
  iValue : double;
begin
  result := 0;

  { -- checks }

  if pX.Count < 2 then exit;
  if pX.Count <> pY.Count then exit;

  { -- delete values }

  pIntegral.Clear;
  pCumulation.Clear;

  { -- trapezoidal rule }

  iDeleteXFrom := -1;

  for i := 1 to pX.Count - 1 do
    begin
      { -- compute integral value in interval }

      iValue := (
                  ( pX.GetValueCheckNull( i ) - pX.GetValueCheckNull( i - 1 ) )
                  *
                  ( ( pY.GetValueCheckNull( i ) + pY.GetValueCheckNull( i - 1 ) ) / 2 )
                );

       result := result + iValue;

       { -- check -> is greater than parameter ? }

       if _iCheckSumIntegralValue <> 999 then
       if result > _iCheckSumIntegralValue then
         begin
           { -- subtract }
           result := result - iValue;

           { -- index for removing }
           iDeleteXFrom := i;

           break;
         end;

       { -- record values }

       if bLogIntegral then pIntegral.AddValue( iValue );
       if bLogCumulation then pCumulation.AddValue( result );
     end;

  { -- delete items over test value (if exists) }

  if iDeleteXFrom <> -1 then
    for i := pX.Count - 1 downto iDeleteXFrom do
      begin
        pX.Delete( i );
        pY.Delete( i );
      end;

end;
Output:














And check (same result):

JAVA - How convert decimal to binary, hex, octal etc.

For converting from decimal to binary, hexadecimal, octal etc., use function Integer.toString().
Second parameter is base.
String sText = "";
Integer iValue;
String sValue = "";

/* -- convert from dec */

sValue = Integer.toString( 100, 10 );
sText += "dec(100) to dec =" + sValue + "\n";

sValue = Integer.toString( 100, 2 );
sText += "dec(100) to bin =" + sValue + "\n";

sValue = Integer.toString( 100, 16 );
sText += "dec(100) to hex =" + sValue + "\n";

sValue = Integer.toString( 255, 16 );
sText += "dec(255) to hex =" + sValue + "\n";

/* result */

memo.setText( sText );      
Output:

JAVA - How convert binary, hex, octal etc. to decimal

For converting from binary, hexadecimal, octal etc. to decimal, use function Integer.parseInt(). Second parameter is base.
String sText = "";
Integer iValue;
String sValue = "";
    
/* -- convert to dec */
    
iValue = Integer.parseInt( "100", 10 );    
sText += "dec(100) to dec = " + iValue.toString() + "\n";
    
iValue = Integer.parseInt( "100", 2 );    
sText += "bin(100) to dec = " + iValue.toString() + "\n";

iValue = Integer.parseInt( "100", 16 );    
sText += "hex(100) to dec = " + iValue.toString() + "\n";

iValue = Integer.parseInt( "ff", 16 );    
sText += "hex(ff) to dec = " + iValue.toString() + "\n";

/* result */
    
memo.setText( sText );      
Output:

Thursday, March 8, 2018

DELPHI - How get angle between points

This function:
{ ---------------------------------------------------------------------------
  Function calculate angle between points _iSource and _iDest in direction
  from _iSource.

  Returns value [0-360]. 
  -------------------------------------------------------------------------- }
function CalcAngle( _iSource, _iDest : TPoint ) : double;
var
  iAngle : extended;
begin
  iAngle := ArcTan2( _iSource.Y - _iDest.Y, _iSource.X - _iDest.X );

  result := ( iAngle * 180 ) / pi;

  { greater than 180 is negative -> change it }
  if result < 0 then
    result := 180 + ( 180 + result );
end;
And here is calling examle:
procedure TForm1.Button2Click(Sender: TObject);
var
  pt1, pt2, pt3, pt4 : TPoint;
  iResult1, iResult2, iResult3 : double;
begin
  pt1.x := 50; pt1.y := 50;
  pt2.x := 100; pt2.y := 100;
  pt3.x := 150; pt3.y := 50;
  pt4.x := 100; pt4.y := 150;

  iResult1 := CalcAngle( pt2, pt1 );
  iResult2 := CalcAngle( pt2, pt3 );
  iResult3 := CalcAngle( pt2, pt4 );

  ShowMessage( iResult1.ToString() + ' , ' + iResult2.ToString() + ' , ' + iResult3.ToString );
end;
Output:

DELPHI - How to call (old) BDE stored procedure

Note: orcl is TDatabase with connection.
var
  pStoredProc : TStoredProc;
begin
  try

    pStoredProc := TStoredProc.Create( Application );
    pStoredProc.DatabaseName := orcl.DataBaseName;

    { -- call stored procedure }

    pStoredProc.StoredProcName := AnsiUpperCase(  'factory.an_p1.Sql_Standard_DeleteDay' );
    pStoredProc.Prepare;
    pStoredProc.Params.ParamByName( 'pPeriod' ).AsString := _sPeriod;
    pStoredProc.ExecProc;

  finally
    pStoredProc.Free;
  end;

Wednesday, March 7, 2018

DELPHI - How add item (with more columns) to TListView

var
  pItem : TListItem;
begin
  ...
  Item := List.Items.Add;
  pItem.ImageIndex := -1;
  pItem.Caption    := IntToStr( iCounter );
  pItem.SubItems.Add( s.Name );
  pItem.SubItems.Add( IntToStr( iRowCount ) );
  pItem.SubItems.Add( IntToStr( iRowCountSum ) );
Output:

Friday, March 2, 2018

DELPHI - How get row count in text file

var
  iRowCount : integer;
  F : TextFile;
begin
  ...
  { read row count }
  try
    iRowCount := 0;

    AssignFile( F, EPath.Text + s.Name );
    Reset( F );
    while not EOF( F ) do
      begin
        readln( F, sText );
        inc( iRowCount );
      end;   
     ...       
   finally
     CloseFile( F );
   end;
...

DELPHI - How to make cubic B-Spline interpolation + Cox-De-Boora algorithmus

const giCount = 15;
      giDetail = 10;
...
procedure TForm1.BSpline_Clamped;
var
  j, i, iIndex : integer;
  iNumSegmentsCount : integer;
  iT, i1_T, iB0, iB1, iB2, iB3 : double;
  iNewX, iNewY : double;
  pSerieLine : TLineSeries;
  iValue : extended;
begin

  pSerieLine := TLineSeries( Chart1.Series[1] );
  pSerieLine.Clear;

  { draw count of cubic curves }
  for iIndex := -2 to px.Count - 2 do
    begin

       { every curve generate from I parts }
       for i := 0 to giDetail - 1 do
         begin
           iT := i / (giDetail-1);
           i1_T := 1 - iT;

           iB0 := ( i1_T * i1_T * i1_T ) / 6;
           iB1 := ( ( 3 * iT * iT * iT ) - ( 6 * iT * iT ) + 4 ) / 6;
           iB2 := ( ( -3 * iT * iT * iT ) + ( 3 * iT * iT ) + ( 3 * iT ) + 1 ) / 6;
           iB3 := ( iT * iT * iT ) / 6;

           iNewX := iB0 * GetValueX( iIndex ) +
                    iB1 * GetValueX( iIndex + 1 ) +
                    iB2 * GetValueX( iIndex + 2 ) +
                    iB3 * GetValueX( iIndex + 3 )
                    ;

           iNewY := iB0 * GetValueY( iIndex ) +
                    iB1 * GetValueY( iIndex + 1 ) +
                    iB2 * GetValueY( iIndex + 2 ) +
                    iB3 * GetValueY( iIndex + 3 );

           pSerieLine.AddXY( iNewX, iNewY );
         end;
  end;
  
end;

JAVA - How check if client is reachable - ping to client

String sText = "";
try {

  /* prepare IP address */
      
  String ipAddress = "10.26.1.3";
  InetAddress inet = InetAddress.getByName( ipAddress );

  /* check ping (timeout=1000 ms) */

  if ( inet.isReachable( 1000 ) ) 
    sText = "Client is reachable";
  else
    sText = "Client isn`t reachable";     
      
} catch( UnknownHostException ex ) {
  Logger.getLogger( FXMLMainController.class.getName() ).log( Level.SEVERE, null, ex );
} catch( IOException ex ) {
  Logger.getLogger( FXMLMainController.class.getName() ).log( Level.SEVERE, null, ex );
}
    
memo.setText( sText );    

SQL SERVER - How list active sessions

For listing active server sessions use this stored procedure:
exec sp_who
Or newest:
exec sp_who2
Output:
SPID  Status     Login       HostName       BlkBy DBName    Command          CPUTime DiskIO LastBatch      SPID  REQUESTID
----- ---------- ----------- ------------- ----- --------- ---------------- ------- ------ -------------- ------ ---------
...
65    sleeping   dd\tommy    PWSQ777         .   framework AWAITING COMMAND 0       20     02/14 10:55:32 65    0    
66    sleeping   ron         HP4004888       .   framework AWAITING COMMAND 204     7      03/02 08:02:14 66    0    
67    sleeping   flash       HP3010999       .   framework AWAITING COMMAND 95      0      03/02 07:12:17 67    0    

Wednesday, February 28, 2018

DELPHI - How to make Chalkin interpolation

procedure TForm1.ChaikinProc( var pNewX, pNewY : TVariable );
var
  iIndex : integer;
  iQx, iQy, iRx, iRy : double;
  iValue1, iValue2 : double;
  pNewListX, pNewListY : TVariable;
begin

  try
    pNewListX := TVariable.Create;
    pNewListY := TVariable.Create;

    { first }
    pNewListX.AddValue( pNewX.GetValue( 0 ) );
    pNewListY.AddValue( pNewY.GetValue( 0 ) );

    for iIndex := 0 to pNewX.Count - 2 do
      begin
        { - calc new Q coord }
        
        iQx := ( 0.75 * pNewX.GetValue( iIndex ) ) + ( 0.25 * pNewX.GetValue( iIndex + 1 ) );
        iQy := ( 0.75 * pNewY.GetValue( iIndex ) ) + ( 0.25 * pNewY.GetValue( iIndex + 1 ) );

        { - calc new R coord }

        iRx := ( 0.25 * pNewX.GetValue( iIndex ) ) + ( 0.75 * pNewX.GetValue( iIndex + 1 ) );
        iRy := ( 0.25 * pNewY.GetValue( iIndex ) ) + ( 0.75 * pNewY.GetValue( iIndex + 1 ) );

        { - add it to values list }

        pNewListX.AddValue( iQx );
        pNewListY.AddValue( iQy );
        pNewListX.AddValue( iRx );
        pNewListY.AddValue( iRy );
      end;

    { last }
    pNewListX.AddValue( pNewX.GetValue( pNewX.Count - 1 ) );
    pNewListY.AddValue( pNewY.GetValue( pNewY.Count - 1 ) );

    { copy }
    pNewListX.CopyTo( pNewX, true );
    pNewListY.CopyTo( pNewY, true );

  finally
    pNewListX.Free;
    pNewListY.Free;
  end;

end;

{ ---------------------------------------------------------------------------
  Chalkin`s curve.
  -------------------------------------------------------------------------- }
procedure TForm1.Chaikin;
var
  i, N : integer;
  pNewX, pNewY : TVariable;
  pSerieLine : TLineSeries;
begin
  pNewX := TVariable.Create;
  pNewY := TVariable.Create;

  for i := 0 to pX.Count - 1 do
    begin
      pX.CopyTo( pNewX );
      pY.CopyTo( pNewY );
    end;

  { get value from scrollbar }
  N := SChalkinFactor.Position;

  { count of smoothing }
  for i := 1 to N do
    ChaikinProc( pNewX, pNewY );

  pSerieLine := TLineSeries( Chart1.Series[1] );
  pSerieLine.Clear;

  for i := 0 to pNewX.Count - 1 do
  begin
    pSerieLine.AddXY( pNewX.GetValue( i ), pNewY.GetValue( i ) );
  end;

  pNewX.Free;
  pNewY.Free;
end;
Smoothing factor = 1
Smoothing factor = 10

Tuesday, February 27, 2018

DELPHI - How to make Lagrange interpolation

{ ---------------------------------------------------------------------------
  Calc Lagrange polynomial.
  -------------------------------------------------------------------------- }
function TForm1.CalcLagrange( _iX : double ) : double;
var
  i, j : integer;
  iNumerator, iDenominator : double;
begin
  result := 0;

  for i := 1 to pX.Count - 1 do
    begin
      iNumerator := 1;
      iDenominator := 1;

      for j := 1 to pX.Count - 1 do
        if j <> i then
          begin
            iNumerator := iNumerator * ( _iX - pX.GetValue( j ) );
            iDenominator := iDenominator * ( pX.GetValue( i ) - pX.GetValue( j ) );
          end;

      result := result + ( pY.GetValue( i ) * ( iNumerator / iDenominator ) );    
    end;
end;

{ ---------------------------------------------------------------------------
  Lagrange interpolation.
  -------------------------------------------------------------------------- }
procedure TForm1.Lagrange;
var
  j, i, iIndex : integer;
  iNumSegmentsCount : integer;
  iT, i1_T, iB0, iB1, iB2, iB3 : double;
  iNewX, iNewY : double;
  pSerieLine : TLineSeries;
  iValue : extended;
begin

  pSerieLine := TLineSeries( Chart1.Series[1] );
  pSerieLine.Clear;  

  { draw count of cubic curves }
  for iIndex := 0 to px.Count - 2 do
    begin

       { every curve generate from I parts }
       for i := 0 to 10 - 1 do
         begin
           iT := i / (10-1);

           iNewX := pX.GetValue( iIndex ) + 
                    ( (pX.GetValue( iIndex + 1 ) - pX.GetValue( iIndex )) * iT );

           iNewY := CalcLagrange( iNewX );          

           pSerieLine.AddXY( iNewX, iNewY );
         end;

  end;
end;

Monday, February 26, 2018

DELPHI - How make Ferguson cubic interpolation curve

procedure TForm1.Ferguson;
var
  j, i, iIndex : integer;
  iNumSegmentsCount : integer;
  iT, i1_T, iP0x, iP0y, iP1x, iP1y : double;
  iF1, iF2, iF3, iF4 : double;
  iNewX, iNewY : double;
  pSerieLine : TLineSeries;
  iValue : extended;
begin

  pSerieLine := TLineSeries( Chart1.Series[1] );
  pSerieLine.Clear;

  { draw specified count of the cubic curves }
  for iIndex := 0 to px.Count - 2 do
    begin

       { every cube generate from I parts }
       for i := 0 to giDetail - 1 do
         begin
           iT := i / (giDetail-1);

           { -- calculate Hermid polynomials }

           iF1 := ( 2 * power( iT, 3 ) ) - ( 3 * power( iT, 2 ) ) + 1;
           iF2 := ( -2 * power( iT, 3 ) ) + ( 3 * power( iT, 2 ) );
           iF3 := power( iT, 3 ) - ( 2 * power( iT, 2 ) ) + iT;
           iF4 := power( iT, 3 ) - power( iT, 2 );

           { -- and vectors }

           iP0x := abs( pX.GetValue( iIndex + 1 ) - pX.GetValue( iIndex ) );
           iP0y := abs( pY.GetValue( iIndex + 1 ) - pY.GetValue( iIndex ) );

           iP1x := abs( pX.GetValue( iIndex ) - pX.GetValue( iIndex + 1 ) );
           iP1y := abs( pY.GetValue( iIndex ) - pY.GetValue( iIndex + 1 ) );

           iNewX := ( pX.GetValue( iIndex ) * iF1 ) +
                    ( pX.GetValue( iIndex + 1 ) * iF2 ) +
                    ( iP0x * iF3 ) +
                    ( iP1x * iF4 );
           iNewY := ( pY.GetValue( iIndex ) * iF1 ) +
                    ( pY.GetValue( iIndex + 1 ) * iF2 ) +
                    ( iP0y * iF3 ) +
                    ( iP1y * iF4 );

           pSerieLine.AddXY( iNewX, iNewY );
         end;

  end;
end;

AX - Error message: "Cannot delete a record in Raw registrations (JmgTermReg). An update conflict occurred due to another user process deleting the record or changing one or more fields in the record."

This error shows in "Job registration" module for worker.

The reason is probably in "optimistic concurrency model" on the table JmgTermReg (table part is locked for changes)".

My solution:

1) Set JmgTermReg.OCCEnabled to No. Save it.
2) Connect with worker to "Job registration", and run action for him again (for example - clock out).
3) Set JmgTermReg.OCCEnabled again back to Yes. Save it.

Note: The reason could be bad session time on some client.

DELPHI - How make Catmull-Rom spline (interpolation)

function TForm1.GetCatmull_RomProc( _iT : double; _p0, _p1, _p2, _p3 : double ) : double;
begin
  result := 0.5 * ( ( 2 * _p1 ) +
                    ( ( ( -1 * _p0 ) + _p2 ) * _iT ) +
                    ( ( ( 2 * _p0 ) - ( 5 * _p1 ) + ( 4 * _p2 ) - _p3 ) * _iT * _iT ) +
                    ( ( ( - 1 * _p0 ) + ( 3 * _p1 ) - ( 3 * _p2 ) + _p3 ) * _iT * _iT * _iT )
                  );
end;

{ ---------------------------------------------------------------------------
  Catmull-Rom interpolation curve.
  -------------------------------------------------------------------------- }
procedure TForm1.Catmull_Rom;
var
  j, i, iIndex : integer;
  iNumSegmentsCount : integer;
  iT, i1_T, iP0, iP1, iP2, iP3 : double;
  iNewX, iNewY : double;
  pSerieLine : TLineSeries;
  iValue : extended;
  pNewX, pNewY : TVariable;
begin
  pNewX := TVariable.Create;
  pNewY := TVariable.Create;

  for i := 0 to pX.Count - 1 do
    begin
      pX.CopyTo( pNewX );
      pY.CopyTo( pNewY );
    end;

  { first and last point again }
  pNewX.InsertValue( 0, pNewx.GetValue( 0 ) );
  pNewY.InsertValue( 0, pNewy.GetValue( 0 ) );

  pNewX.InsertValue( pNewX.Count - 1, pNewx.GetValue( pNewX.Count - 1 ) );
  pNewY.InsertValue( pNewY.Count - 1, pNewy.GetValue( pNewY.Count - 1 ) );

  pSerieLine := TLineSeries( Chart1.Series[1] );
  pSerieLine.Clear;

  { draw count of cubic curves }
  for iIndex := 0 to pNewX.Count - 4 do
    begin

       { every curve generate from I parts }
       for i := 0 to giDetail - 1 do
         begin
           iT := i / (giDetail-1);
           i1_T := 1 - iT;

           iNewX := GetCatmull_RomProc( iT, pNewx.GetValue( iIndex ), 
                                        pNewx.GetValue( iIndex + 1 ),
                                        pNewx.GetValue( iIndex + 2 ), 
                                        pNewx.GetValue( iIndex + 3 )
                                      );

           iNewY := GetCatmull_RomProc( iT, pNewy.GetValue( iIndex ), 
                                        pNewy.GetValue( iIndex + 1 ),
                                        pNewy.GetValue( iIndex + 2 ), 
                                        pNewy.GetValue( iIndex + 3 )
                                      );

           pSerieLine.AddXY( iNewX, iNewY );
         end;

  end;

  pNewX.Free;
  pNewY.Free;
end;

Thursday, February 15, 2018

DELPHI - How send message to control

To the control TreeSql_Hierarchy is sended KEYDOWN message, that simulate INSERT key with SHIFT:
SendMessage( TreeSql_Hierarchy.Handle, WM_KEYDOWN, VK_INSERT, MapVirtualKey( VK_SHIFT, 0 ) );

DELPHI - How to open Excel with .xls/.xlsx file

In sFile variable is saved path to opened file.
var
  sFile : string;
  pExcel : TExcelApplication;
begin
  ...
  iLocaleID := GetUserDefaultLCID;

  pExcel := TExcelApplication.Create( nil );                  
  pExcel.DisplayAlerts[ iLocaleID ] := false;
  pExcel.Connect;
  pExcel.Visible[ iLocaleID ] := true;

  pExcel.Workbooks.Open( sFile,
                         EmptyParam, EmptyParam, EmptyParam,
                         EmptyParam, EmptyParam, EmptyParam, EmptyParam,
                         EmptyParam, EmptyParam, EmptyParam, EmptyParam,
                         EmptyParam, EmptyParam, EmptyParam, iLocaleID );
In old Delphi (6) I used directed opening through COM, something like this:
uses ComObj;

const
  cExcelIdentifier = 'excel.application';

{ ---------------------------------------------------------------------------
  Try to run it.
  --------------------------------------------------------------------------- }
function TExcel.Execute : boolean;
begin
  result := false;

  FExcel := CreateOleObject( cExcelIdentifier );

  if VarIsEmpty( FExcel ) then
    begin
      MessageDlg( 'Application is not accessible.', mtError,  [mbOk], 0 );
      exit;
    end;

  FExcel.Visible := true;
  FExcel.Workbooks.Add( 1 );

  result := true;
end;

Tuesday, February 13, 2018

DELPHI - How to call external program

This code execute external program (FFileName as path, FParams as optional parameters). It uses ShellExecuteEx(), in old Windows (XP and older) you can use ShellExecute() too.
function TExecute.Execute : THandle;
var 
  iShowCmd : integer;  
  b : boolean;
  exInfo : SHELLEXECUTEINFO;
begin

  iShowCmd := sw_ShowNormal;
  case FShowType of
    stNormal         : iShowCmd := sw_ShowNormal;
    stMaximized      : iShowCmd := sw_ShowMaximized;
    stMinimize       : iShowCmd := sw_Minimize;
    stHide           : iShowCmd := sw_Hide;
    stRestore        : iShowCmd := sw_Restore;
    stShow           : iShowCmd := sw_Show;
    stShowMinimized  : iShowCmd := sw_ShowMinimized;
    stShowNA         : iShowCmd := sw_ShowNA;
    stShowNoActivate : iShowCmd := sw_ShowNoActivate;
  end;

  { -- run it }

  exInfo.cbSize := sizeof( SHELLEXECUTEINFO );
  exInfo.fMask := SEE_MASK_NOCLOSEPROCESS; 
  exInfo.wnd := Application.Handle;
  exInfo.lpVerb := pchar( 'open' ); 
  exInfo.lpFile := pchar( FFileName );
  exInfo.lpParameters := pChar( FParams );
  exInfo.nShow := SW_SHOWNORMAL;
  //exInfo.hInstApp = NULL;
  //exInfo.lpDirectory = NULL;

  b := ShellExecuteEx( @exInfo );

  SetWindowPos(result, HWND_TopMost, 0, 0, 0, 0, SWP_NoMove or SWP_NoSize);

  if not b then ShowMessage( 'Execute problem: ' + IntToStr( GetLastError ) );

  result := GetLastError;
end;

Monday, February 12, 2018

DELPHI - How to make "stack" for colors

Returned colors are repeated still in same order.
{ ***************************************************************************

  TColorStack - stack for colors.

  ************************************************************************** }

  TColorStack = class
  private
    pList : TList;

    iCurrent : integer;
  public
    constructor Create;
    destructor Destroy; override;

    function GetColor : TColor;
    procedure Reset;
  end;

constructor TColorStack.Create;
begin
  inherited Create;

  pList := TList.Create;

  { -- add some colors to list }

  Reset;
end;

destructor TColorStack.Destroy;
begin
  pList.Free;

  inherited destroy;
end;

{ ---------------------------------------------------------------------------
  Get next color from list.
  -------------------------------------------------------------------------- }
function TColorStack.GetColor : TColor;
begin
  result := TColor( pList[ iCurrent ] );

  inc( iCurrent );
  if iCurrent > pList.Count - 1 then iCurrent := 0;
end;

{ ---------------------------------------------------------------------------
  Reset to initial state.
  -------------------------------------------------------------------------- }
procedure TColorStack.Reset;
begin
  pList.Clear;

  pList.Add( Pointer( TColor( rgb( 99, 167, 54 ) ) ) );
  pList.Add( Pointer( TColor( rgb( 245, 71, 106 ) ) ) );
  pList.Add( Pointer( TColor( rgb( 91, 115, 196 ) ) ) );
  pList.Add( Pointer( TColor( rgb( 249, 251, 155 ) ) ) );
  pList.Add( Pointer( TColor( rgb( 148, 148, 148 ) ) ) );
  pList.Add( Pointer( TColor( rgb( 207, 118, 189 ) ) ) );
  pList.Add( Pointer( TColor( rgb( 170, 231, 152 ) ) ) );
  pList.Add( Pointer( TColor( rgb( 126, 221, 226 ) ) ) );
  pList.Add( Pointer( TColor( rgb( 181, 176, 130 ) ) ) );
  pList.Add( Pointer( TColor( rgb( 244, 202, 53 ) ) ) );
  pList.Add( Pointer( TColor( rgb( 209, 209, 209 ) ) ) );
  pList.Add( Pointer( TColor( rgb( 74, 61, 245 ) ) ) );

  iCurrent := 0;
end;

SQL SERVER - How get info about database

SQL server command (stored procedure):
exec sp_helpdb 'framework'

Friday, February 9, 2018

AX - "UPDATE" job

"Update job" - it change flushing princip on group of items to "Finish".
static void SetFlushingPrincipItem(Args _args)
{
    InventTable inventTable;
    
    ttsBegin;

    while
    select forUpdate * from inventtable
    where
    inventTable.ItemId like "S-*" &&
    inventTable.ProdFlushingPrincip != ProdFlushingPrincipItem::Finish
    {      
        inventTable.ProdFlushingPrincip = ProdFlushingPrincipItem::Finish;          
        inventTable.update();
        
        info( InventTable.ItemId );     
    }

    ttsCommit;

    info( 'OK' );    
}

ORACLE - How to set user`s password and how to unlock account

connect sys/password as sysdba;

alter user UserName identified by NewTestPassword account unlock;

Thursday, February 8, 2018

JAVA - How to get local IP address and host name

import java.net.InetAddress;
import java.net.UnknownHostException;
...
try {
  InetAddress ia = InetAddress.getLocalHost();

  /* -- write results */

  System.out.println( ia.getHostAddress() );
  System.out.println( ia.getHostName() );

  System.out.println( ia.toString() );

  System.out.println( ia.getCanonicalHostName() );

  for ( int i = 0; i < ia.getAddress().length; i++ )
    System.out.println( ia.getAddress()[i] );

} catch ( UnknownHostException ex ) {
}
Output (could be):
10.26.1.11
pc082
pc082/10.26.1.11
pc082.org.local
10
26
1
11

AX - How use Query with date range (since-till) in where condition

When you need use date range in Query where condition, use SysQuery::range() function:
ProdRouteTrans prodRouteTrans;
Query q; 
QueryBuildDataSource qDS; 
QueryBuildRange qRange;
QueryRun qRun;
...
while
  select * from vm_resources
  where
  vm_resources.resource_mask != "" &&
  vm_resources.active == NoYes::Yes
  {
    q = new Query();
    qDS = q.addDataSource( TableNum( ProdRouteTrans ) );

    /* date range since..till */
    qRange = qDS.addRange( FieldNum( ProdRouteTrans, DateWIP ) );
    qRange.value( SysQuery::range( startDate, endDate ) );

    qRun = new QueryRun( q );
    while ( qRun.next() )
    {
       /* get ProdRouteTrans data from query */
       prodRouteTrans = qRun.get( TableNum( ProdRouteTrans ) );
       ...
    }
  ...
}

AX - How use Query with enum in where condition

When you need use enum item in Query where condition, use queryValue() function:
ProdRouteTrans prodRouteTrans;
Query q; 
QueryBuildDataSource qDS; 
QueryBuildRange qRange;
QueryRun qRun;
...
while
  select * from vm_resources
  where
  vm_resources.resource_mask != "" &&
  vm_resources.active == NoYes::Yes
  {
    q = new Query();
    qDS = q.addDataSource( TableNum( ProdRouteTrans ) );

    /* where condition: Qty only */
    qRange = qDS.addRange( FieldNum( ProdRouteTrans, TransType ) );
    qRange.value( queryValue( ProdRouteTransType::Qty ) );

    qRun = new QueryRun( q );
    while ( qRun.next() )
    {
       /* get ProdRouteTrans data from query */
       prodRouteTrans = qRun.get( TableNum( ProdRouteTrans ) );
       ...
    }
  ...
}

Monday, February 5, 2018

WIN OS - How to update client group policy settings

gpupdate /force
Output:
C:\Windows\System32>gpupdate /force
Updating policy...

Computer Policy update has completed successfull
User Policy update has completed successfully.

WIN OS - How get name of connection DC (domain controller)

echo %logonserver%

Thursday, February 1, 2018

SQL SERVER - How call stored procedure automatically when SQL server instance started

This code call stored proc dbo.MyCreatePermissions after SQL server instance start:
EXEC sp_procoption N'[dbo].[MyCreatePermissions]', 'startup', '1'
Note: For disable it use 'startup', '0'.

Tuesday, January 30, 2018

DELPHI - How to call stored procedure (TFDStoredProc)

TFDStoredProc with one input parameter and with integer return value.
var
  iGraphCount : integer;
  pStoredProc : TFDStoredProc;
begin
  ...
  { -- get graphs count }

  pStoredProc := DB.GetStoredProc( 'sql_detail_graphs_exists_by_SQL_ID' );
  try
    pStoredProc.ParamByName( '@sql_id' ).AsInteger := self.GetQuestion_Info.iSQL_ID;

    DB.CallStoredProc( pStoredProc );

    iGraphCount := pStoredProc.ParamByName( '@return_value' ).AsInteger;
  finally
    pStoredProc.Free;
  end;

function TDB.GetStoredProc( _sName : string ) : TFDStoredProc;
var
  pStoredProc : TFDStoredProc;
begin
  pStoredProc := TFDStoredProc.Create( Application );

  try
    pStoredProc.Connection := FDConnection;
    pStoredProc.StoredProcName := AnsiUpperCase( _sName );
    pStoredProc.Prepare;
  except
    on E : Exception do
      begin
        MessageDlg( 'Stored procedure error: ' + #13 + #13 +
                     E.Message, mtError, [ mbOk ], 0 );
      end;
  end;

  result := pStoredProc;
end;

procedure TDB.CallStoredProc( _pStoredProc : TFDStoredProc; _bOpen : boolean = false );
begin
  if _pStoredProc = nil then exit;

  if ( not _bOpen ) then
    _pStoredProc.ExecProc
  else
    _pStoredProc.Open;
end;

Monday, January 29, 2018

JAVA - How to call external application

try {
  Process p = Runtime.getRuntime().exec( "notepad.exe" );

} catch ( IOException ex ) {
  Logger.getLogger( Window.class.getName() ).log( Level.SEVERE, null, ex );
}
When you need to call it with parameter(s):
try {
  Process p = Runtime.getRuntime().exec( "notepad.exe c:\\test.txt" );

} catch (IOException ex) {
  Logger.getLogger( Window.class.getName() ).log( Level.SEVERE, null, ex );
}
When you use java.lang.Process.WaitFor() function your application will be wait to the end of the called program.
try {
  Process p = Runtime.getRuntime().exec( "notepad.exe c:\\test.txt" );

  try {
    /* -- wait for end of notepad.exe */
    p.waitFor();
  } catch ( InterruptedException ex ) {
    Logger.getLogger( Window.class.getName() ).log( Level.SEVERE, null, ex );
  }

} catch (IOException ex) {
  Logger.getLogger(Window.class.getName()).log(Level.SEVERE, null, ex);
}

Friday, January 26, 2018

ORACLE - How generate text file in PL/SQL

function CreateExportFile( pNumber varchar2 ) return number
is
  cursor c_lower is
    select * from table
    where
    lief_sch_nr = pNumber and
    c_pom = 2   /* spec.type */
    order by
    bsg_nr, pos_lfd_nr, tl_nr, tl_fam, tl_atn
    ;
  pc_lower   c_lower%rowtype;

  iCount number;
  sFileName varchar2( 50 );
  sDir varchar2( 256 );
  fileHandler utl_file.file_type;
begin
  /* -- check if some data exists */

  select count( * ) into iCount
  from
  factory.cieb_karosa_rozpad
  where
  lief_sch_nr = pNumber;

  if iCount = 0 then
    return( -1 );
  end if;

  /* -- export */

  sDir := 'c:\ifc42\utlfile';
  sFileName := pNumber || '_name_' || to_char( sysdate, 'dd_mm_yyyy_hh24_mi' ) || '.csv';
  fileHandler := utl_file.fopen( sDir, sFileName, 'w' );

  open c_lower;
    loop
      fetch c_lower into pc_lower;
      exit when c_lower%notfound;

      utl_file.putf( fileHandler, 
                     pc_lower.lief_sch_nr || ';' ||
              pc_lower.bsg_nr_kd || ';' ||
       pc_lower.pos_lfd_nr || ';' ||
       to_char( pc_lower.abschl_dat, 'dd.mm.yyyy' ) || ';' ||
       pc_lower.tl_nr_lief || ';' ||
       pc_lower.lsch_mng_me || ';' ||
       to_char( to_number( pc_lower.lsch_mng_mz ) ) || ';' ||
                     '\n' 
                   );

    end loop;
  close c_lower;

  utl_file.fclose( fileHandler );

  return( 0 );
end;

Output (could be):
DL160386;30003313;10;11.03.2016;5006006343;ks;5;
DL160386;30003313;240;11.03.2016;E5801470258;ks;4;
DL160386;30003313;240;11.03.2016;E5801470258;ks;12;
...

Thursday, January 25, 2018

ORACLE - How make PL/SQL cursor with parameter(s)

function CreateInvoice( pNumber varchar2, pErrorMessage out varchar2 ) return number
is
  cursor c_sets( pLief_sch_nr varchar2 ) is
    select * from table
    where
    lief_sch_nr = pLief_sch_nr and
    c_px = 1   
    ;
  pc_sets   c_sets%rowtype;
begin
  ...
  open c_sets( pNumber );
    loop
      fetch c_sets into pc_sets;
      exit when c_sets%notfound;
      ...
    end loop;
  close c_sets;

Wednesday, January 24, 2018

JAVA - How to get list of installed security providers

List installed security providers. Every security provider could provide some services - for example ciphers, etc.
import java.security.Provider;
import java.security.Security;
...
/* -- fill all security providers */

Provider[] providers = Security.getProviders();

/* -- go throught installed providers */

for (int i = 0; i < providers.length; i++ ) {
  System.out.println( providers[i].getName() + " - " + providers[i].getInfo() );
Output (could be):
SUN - SUN (DSA key/parameter generation; DSA signing; SHA-1, MD5 digests; 
SecureRandom; X.509 certificates; JKS & DKS keystores; PKIX CertPathValidator; 
PKIX CertPathBuilder; LDAP, Collection CertStores, JavaPolicy Policy; 
JavaLoginConfig Configuration)
SunRsaSign - Sun RSA signature provider
SunEC - Sun Elliptic Curve provider (EC, ECDSA, ECDH)
SunJSSE - Sun JSSE provider(PKCS12, SunX509/PKIX key/trust factories, 
SSLv3/TLSv1/TLSv1.1/TLSv1.2)
SunJCE - SunJCE Provider (implements RSA, DES, Triple DES, AES, Blowfish, ARCFOUR, RC2, PBE, 
Diffie-Hellman, HMAC)
SunJGSS - Sun (Kerberos v5, SPNEGO)
SunSASL - Sun SASL provider(implements client mechanisms for: DIGEST-MD5, GSSAPI, EXTERNAL, 
PLAIN, CRAM-MD5, 
NTLM; server mechanisms for: DIGEST-MD5, GSSAPI, CRAM-MD5, NTLM)
XMLDSig - XMLDSig (DOM XMLSignatureFactory; DOM KeyInfoFactory; C14N 1.0, C14N 1.1, 
Exclusive C14N, Base64, 
Enveloped, XPath, XPath2, XSLT TransformServices)
SunPCSC - Sun PC/SC provider
SunMSCAPI - Sun's Microsoft Crypto API provider

Tuesday, January 23, 2018

ORACLE - How round number to three decimal places

select ( 3.652 * 5.673 ) as n1, round( 3.652 * 5.673, 3 ) as n2 from dual
Output:
        N1         N2
---------- ----------
20,717796  20,718  

ORACLE - "for" statement in PL/SQL

Easy for loop for labels generation:
declare
  i number;
  ...
begin
...
  open c_city( pc1.arb_ord_nr, pc1.arb_ord_zae );
  loop

    fetch c_city into pCity;
    exit when c_city%notfound;

    for i in 1..pCity.quantity loop

      /* -- add one label per city cursor row */

      insert into labels
      ( sessionid, arb_ord_nr, c_number, tl_fam, tl_atn, c_type, dat_neu )
      values
      ( sid, pCity.arb_ord_nr, i, null, null, sType, sysdate );
      
      commit;

    end loop;

  end loop;
  close c_city;

ORACLE - How to make hierarchical query

In this example you can see hierarchical query with start with and connect by section.
  • start with - is initial row (data) for question (..here key to top item).
  • connect by - tells, how the new data are connected to previous row.
Here is example from BOM table:
select substr(lpad(' ', level) || level, 1, 10) as level_,
a.tl_nr, a.tl_unt_nr, a.tl_unt_fam, a.tl_unt_atn, 
a.mng_pos_re_mz as quantity, a.mng_pos_me as unit
from ertesr a
start with 
a.tl_nr = '442-97345-622-1' and
a.tl_fam = '0' and 
a.tl_atn = '02'
connect by prior a.tl_unt_nr = a.tl_nr
and prior a.tl_unt_fam = a.tl_fam
and prior a.tl_unt_atn = a.tl_atn
order by rownum
Output:
LEVEL_     TL_NR                TL_UNT_NR            TL_UNT TL_UNT   QUANTITY UN
---------- -------------------- -------------------- ------ ------ ---------- --
 1         442-97345-622-1      S-312-221-1001       0      0      ,025       KG
 1         442-97345-622-1      442-90780-566-1      0      0               1 KS
  2        442-90780-566-1      S-137-114-1060       0      0      ,6         KG
 1         442-97345-622-1      S-246-931-1005       0      0      ,019       KG
 1         442-97345-622-1      S-821-303-0144       0      0               1 KS
 1         442-97345-622-1      442-05011-145-1      0      0               2 KS
  2        442-05011-145-1      S-331-123-2058       0      0               1 KS

7 rows selected. 

ORACLE, SQL SERVER - typical update with subselect

update factory.an_plan a
set
a.sbj_id = ( select sbj_id from j_vad
             where
        vad_id = ( select vad_id from kuauko
                 where
            bsg_nr = a.bsg_nr
        )
    )
where
a.c_period = pPeriod and
a.bsg_nr is not null;

Monday, January 22, 2018

JAVA - How to rename file

import java.io.*;
...
File f = new File( "c:\\new_name.txt" );

if ( f.exists() ) {

  /* -- here can be override question */

  f.delete();
}

boolean b = new File( "c:\\old_name.txt" ).renameTo( f );

ORACLE - How get last day in the month

select sysdate, last_day( sysdate ) from dual;

select last_day( to_date( '201801', 'yyyymm' ) ) from dual;
Output:
SYSDATE  LAST_DAY
-------- --------
22.01.18 31.01.18

LAST_DAY
--------
31.01.18

ORACLE - select with limiting rows count (..how to show first 5 rows)

select tl_nr, dat_anf from ertest
where
rownum <= 5
Output:
TL_NR                DAT_ANF 
-------------------- --------
S-319-421-1000       12.02.97
-94547-508           12.02.97
-90391-127           12.02.97
-94547-517           12.02.97
-94215-137           12.02.97

ORACLE - having clause with between expression

You can limit group by condition with between expression:
select sbj_id, sum ( bew_mng_mz )
from factory.checks
where
to_char( c_datum, 'yyyy' ) = '2018' 
having sum( bew_mng_mz ) between 100 and 500
group by sbj_id;
Output:
    SBJ_ID SUM(BEW_MNG_MZ)
---------- ---------------
     37024             300
     36336             140
     37305             210
     30890             500
     38433             403

ORACLE - How get error text from PL/SQL function

Sometimes you need return from PL/SQL functions error code and error text too.
function Sql_Salary( pPeriod number, pErrorMessage out varchar2 ) return number
is
begin
  ...
  if i <> 0 then
    pErrorMessage := '..error message..';
    return( -1 );
  end if;  
  ...
  return( 0 );
end;  
Call it:
declare
  i number;
  sErrorMessage varchar2(500);
begin
  i := factory.an_p.Sql_Salary( '201801', sErrorMessage );
end;

ORACLE - How calculate months to date type

select sysdate, add_months( sysdate, -1 ) minus1, add_months( sysdate, 12 ) minus12 from dual
Output:
SYSDATE  MINUS1   MINUS12 
-------- -------- --------
22.01.18 22.12.17 22.01.19

ORACLE - How replace NULL value

nvl() function replace NULL value with second parameter (..in this example 0):
function sql_Color_AddNew_Rule( pQuestion varchar2, pUser varchar2, pGroup number ) return number
is
  iID number;
begin

  /* -- find new ID */

  select nvl( max( c_id ), 0 ) + 1 into iID
  from factory.an_color
  where
  lower( c_question ) = lower( pQuestion ) and
  lower( c_user ) = lower( pUser ) and
  c_group = pGroup;
...

ORACLE - How get sessionid

Every login in Oracle has our sessionid - you can use it for example for data inserted to table:
insert into factory.an_question_fort
( sessionid, tl_nr, tl_fam, tl_atn, bst_akt_mz, dat_neu )
values
( userenv( 'SESSIONID' ), pc_m.tl_nr, pc_m.tl_fam, pc_m.tl_atn, iInv, trunc( sysdate ) );

Thursday, January 18, 2018

ORACLE - How get already first/last row value from dataset (dense_rank)

Suppose this data:
select tl_nr, bel_nr, bew_dat, bew_art from factory.himtbw
where
tl_nr like 'S-442-700-0000' and
to_char( bew_dat, 'yyyymm' ) = '201503'

TL_NR                BEL_NR BEW_DAT  BE
-------------------- ------ -------- --
S-442-700-0000       108923 03.03.15 PP
S-442-700-0000       108924 03.03.15 PP
S-442-700-0000       391498 11.03.15 VY
S-442-700-0000       391498 11.03.15 PR
S-442-700-0000       384932 12.03.15 VV
S-442-700-0000       384936 12.03.15 VV
S-442-700-0000       394694 20.03.15 VV
S-442-700-0000       401817 26.03.15 VV

8 rows selected. 
When you need to get BEW_ART type for last date, you can use oracle analytical function dense_rank for it:
select min( bew_art ) keep ( dense_rank last order by ( bew_dat ) ),
min( bel_nr ) keep ( dense_rank last order by ( bew_dat ) ) from factory.himtbw
where
tl_nr like 'S-442-700-0000' and
to_char( bew_dat, 'yyyymm' ) = '201503'
Output:
MI MIN(BE
-- ------
VV 401817
How it works: You get BEW_ART/BEL_NR from data set, which is ordered by BEW_DAT -> and you use for data LAST row (when is there in this sorting one row only).

ORACLE, SQL SERVER - Typical subselect

Typical SQL subselect for finding value:
update factory.an_turnover a
set
a.c_last_date = ( select max( bew_dat ) from factory.himtbw
                  where
                  tl_nr = a.tl_nr and
                  tl_fam = a.tl_fam and
                  tl_atn = a.tl_atn and
                  bew_art in ( 'PP', 'PV', 'PN' ) and 
                  lag_ort not in ( '700', '701' )
                 )
;

commit;

ORACLE - How generate DML command to string and how execute it with parameters

When you need dynamically change name of the table in pl/sql procedure, one approach is generate command to string and then execute it.
function Sql_AnalyzeTurnover( pPeriod number ) return number
is
  sKey varchar2(4);
  str varchar2(4000);
begin
...
  str := 'update factory.cieb_an_turnover a ';
  str := str || 'set a.c_inventory = nvl( a.c_inventory, 0 ) ';
  str := str || 'from tiskman.uz_state_p' || sKey || ' ';
  str := str || 'where ';
  str := str || 'tl_nr = a.tl_nr and ';
  str := str || 'tl_fam = a.tl_fam and ';
  str := str || 'tl_atn = a.tl_atn ';
  str := str || 'and lag_ort not in ( :1, :2 ) ';
  str := str || ') ';
  str := str || 'where ';
  str := str || 'a.c_period = :3 ';

  execute immediate str
  using '700', '701', pPeriod;
...
In using part you can pass parameters.

ORACLE - How get info about user`s tables

Use system table all_tables.
select owner, table_name, tablespace_name, pct_free, num_rows, 
blocks, avg_row_len 
from sys.all_tables
where
lower( owner ) = 'tiskman' and
lower( table_name ) like 'uz_%';
Output:
OWNER       TABLE_NAME       TABLESPACE_NAME   PCT_FREE NUM_ROWS     BLOCKS AVG_ROW_LEN
----------- ---------------- ----------------- -------- --------- --------- -----------
TISKMAN     UZ_TRANS_P1506   USERS                   10     20314       610         188
TISKMAN     UZ_STATE_S1510   USERS                   10        95         5         111
TISKMAN     UZ_TRANS_S1510   USERS                   10      1611        54         191
...

Wednesday, January 17, 2018

ORACLE - How concatenate two strings

Use operator || or concat() function:
select 'This' || ' is text' from dual;

select concat( 'This', ' is text' ) from dual;
Output:
'THIS'||'IST
------------
This is text

CONCAT('THIS
------------
This is text