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;

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);
}

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;
...

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;

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

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;

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 ) );

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
...

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

ORACLE - How to convert string to date (with mask)

select to_char( to_date( '20180105', 'yyyymmdd' ), 'dd.mm.yyyy' ) from dual;

Output:

TO_CHAR(TO
----------
05.01.2018

ORACLE - How convert (current) datetime to formated string

select to_char( sysdate, 'dd.mm.yyyy hh24:mi' ) from dual;

Output:

TO_CHAR(SYSDATE,
----------------
17.01.2018 13:26

ORACLE - How call DDL command from stored procedure + how to create global temporary table

For calling DDL (Data definition command) from Oracle PL/SQL stored procedure use execute immediate command:

function sql_temporary_piecelist return number
is
begin

  /* delete - if exists */

  execute immediate
   '
   drop table temporary_piecelist
  ';

  /* create new one */

  execute immediate
   '
   create global temporary table temporary_piecelist
   ( key number,
    name varchar2(25 )
   )
   on commit preserve rows
  ';

  return( 0 );
end;

Note: "On commit preserve rows" clause ensure, that global temporary table will be exists to end of the oracle session (..then will be deleted).

JAVA - How to convert string to byte[] array

For conversion use method java.lang.String.getBytes():

String s = "This is text";
    
char[] bytes = s.getBytes();
    
System.out.println( Arrays.toString( bytes ) );

Output:

[84, 104, 105, 115, 32, 105, 115, 32, 116, 101, 120, 116]

Note: In method java.lang.String.getBytes() you can optionally set default charset.

JAVA - How to convent string to date

For conversion use SimpleDateFormat class where you can specify date mask.

import java.text.*;
...
/* - value for conversion */

String s = "20.10.2017";

/* - specify format mask */

DateFormat df = new SimpleDateFormat( "dd.MM.yyyy" );

try {
  /* - try to convert */

  Date d = df.parse( s );

  /* - print result */

  System.out.println( df.format( d ) );
}
catch ( ParseException ex ) {}

Output:

20.10.2017

JAVAFX - How bind controls by properties

Suppose this .fxml form:

Add this code to form controller initialize() method:

public class FXMLMainController implements Initializable {  
  @FXML private ChoiceBox< javafx.geometry.side > choiceBox;
  @FXML private TabPane tabPane;
  
  @Override
  public void initialize( URL url, ResourceBundle rb ) {
    choiceBox.getItems().addAll( javafx.geometry.Side.values() );        
    choiceBox.setValue( Side.TOP );
    
    tabPane.sideProperty().bind( choiceBox.valueProperty() );
  }  
  
}

How you can see, tabPane.side property is binded to choicebox.value - so, after change in choicebox.value is immediately changed tabPane.side.

SQL SERVER - How add new user to DB and how add him admin rights

This code:

1. Create new login.
2. Add new user for this login to DB.
3. Set full admin rights for this user.

/* -- new admin user login - creation */

if not exists 
  ( select 'x'  
    from master.sys.server_principals
    where
    lower( name ) = 'framework_admin' 
  )
begin
  create login framework_admin with password = 's5gh7f5s3', default_database = [master];
end;

/* -- add user for created login to DB */

if not exists
  ( select 'x' 
    from sys.database_principals
    where
    lower( name ) = 'framework_admin'
  )
begin
  use framework;
  create user framework_admin for login framework_admin;
end;

/* -- add all rights */

use framework;
exec sp_addrolemember N'db_owner', N'framework_admin';

SQL SERVER - How execute script block code from script

/* -- script for DB creation */

declare @script_create nvarchar( max );

/* MSSQL 2014 */
if @iProductVersion = 12 
  begin
    set @script_create =
      'create database {DATABASE_NAME} on primary 
       ( name = ''{DATABASE_NAME}'', 
         filename = ''{FILE}'', 
         size = 5120KB, 
         maxsize = UNLIMITED, 
         filegrowth = 1024KB 
       )
       LOG ON 
       ( name = ''{DATABASE_NAME_LOG}'', 
         filename = ''{FILE_LOG}'', 
         size = 1024KB, 
         maxsize = 2048GB, 
         filegrowth = 10%
       ) 
       collate Czech_CI_AS
      '
    ;
  end
    else
  begin
    set @script_create =
      'create database {DATABASE_NAME} on primary 
       ( name = ''{DATABASE_NAME}'', 
         filename = ''{FILE}'', 
         size = 3072KB, 
         maxsize = UNLIMITED, 
         filegrowth = 1024KB 
       )
       LOG ON 
       ( name = ''{DATABASE_NAME_LOG}'', 
         filename = ''{FILE_LOG}'', 
         size = 1024KB, 
         maxsize = 2048GB, 
         filegrowth = 10%
       ) 
       collate Czech_CI_AS
      '
    ;
  end;

set @script_create = REPLACE( @script_create, '{DATABASE_NAME}', @database_name );
set @script_create = REPLACE( @script_create, '{DATABASE_NAME_LOG}', @database_name_log );
set @script_create = REPLACE( @script_create, '{FILE}', @file );
set @script_create = REPLACE( @script_create, '{FILE_LOG}', @file_log );

/* - call execute() */

execute( @script_create );

AX - How (re)deploy all SSRS reports

Run "Microsoft Dynamics AX management shell" and write this command:

Publish-AXReport –ReportName *

The action takes several minutes.

AX - How get table recordcount

info( strfmt( "Record count = %1", ( select count(RecId) from CustInvoiceJour ).recId ) );

DELPHI - How prevent use cache data during navigation to page

Use NavNoReadFromCache flag as second optional parameter.

var
  WebBrowser : TWebBrowser;
begin
  ..
  WebBrowser.Navigate( self.sName, 4 {NavNoReadFromCache} );

Note: You can use here many other interesting options (you can combine it):

navOpenInNewWindow = $00000001;
navNoHistory = $00000002;
navNoReadFromCache = $00000004;
navNoWriteToCache = $00000008;
navAllowAutosearch = $00000010;
navBrowserBar = $00000020;
navHyperlink = $00000040;
navEnforceRestricted = $00000080;
navNewWindowsManaged = $00000100;
navUntrustedForDownload = $00000200;
navTrustedForActiveX = $00000400;
navOpenInNewTab = $00000800;
navOpenInBackgroundTab = $00001000;
navKeepWordWheelText = $00002000;
navVirtualTab = $00004000;
navBlockRedirectsXDomain = $00008000;
navOpenNewForegroundTab = $00010000;

JAVA - How to use "serialver" tool (serialver.exe)

Every serializable class is in time of deserialization identified by its hash. The synonym for this value is serialVersionUID.

When you want to read class hash code, use tool serialver.exe. It is in your \java\jdk\bin directory.
I recommend you set this directory into your system path variable.

Suppose this class (Person.class file):

import java.io.Serializable;

public class Person implements Serializable {
          
  public Person() {}
      
  String name;
  int age;      
}

, then I can read class serialVersionUID:

When you make small change:

import java.io.Serializable;

public class Person implements Serializable {

public Person() {}

String name;
int age1;
}

, returned value is other:

AX - How solve AOS error 1067

Look at SQL server, if there is a disk space (DB log files).