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