Monday, August 6, 2018

SQL SERVER - How to interpret empty string as NULL value

When is in varchar field saved NULL value, is situation easy:
select isnull( revision, '< null >' ) from drawing_drawing;
Output is:
----------
< null >
Different situation is when varchar field is only empty. In that case isnull() returns only empty string:
select isnull( revision, '< null >' ) from drawing_drawing;
Output is:
----------
The solution is to use inner function nullif() - ...question: which value is NULL ?
select isnull( nullif( revision, '' ), '< null >' ) from drawing_drawing;
Output is:
----------
< null >

Thursday, August 2, 2018

ORACLE - How declare variable accross existing table field (%type)

Both variables are declared accross existing PEOPLE.NAME table field:
function Month_Execute( pPeriod varchar2, sErrorMessage out varchar2 ) return number
is
  sMinName people.name%type;
  sMaxName people.name%type;
begin
...

SQL SERVER - How disable updating of the table/field, UPDATE trigger

This code disable updating of the table:
create trigger [dbo].[drawing_drawing_update]
on [dbo].[drawing_drawing]
for update
as
begin
  set nocount on;

  raiserror( N'Table can`t be updated.', 16, 1 ) with nowait;
  rollback tran;
  return;
end;
Alternatively, you can protect only specified updated field(s):
create trigger [dbo].[drawing_drawing_update]
on [dbo].[drawing_drawing]
for update
as
begin
  set nocount on;

  if UPDATE( validsince )
  begin
    raiserror( N'Field can`t be updated.', 16, 1 ) with nowait;
    rollback tran;
    return;
  end;
end;
When you trying to save row in application, this message will appears:

Thursday, July 26, 2018

AX - How calculate only with opened days in calendar

Use WorkCalendarSched class:
WorkCalendarSched workCalendarSched;
date dateOnlyWorking, dateAll;

workCalendarSched = new workCalendarSched();

dateOnlyWorking = 
  workCalendarSched.schedDate( SchedDirection::Backward, today(), 6, NoYes::Yes, "5x24" );
dateAll = 
  workCalendarSched.schedDate( SchedDirection::Backward, today(), 6, NoYes::No, "5x24" );

info( date2str( dateOnlyWorking, 321, DateDay::Digits2, DateSeparator::Hyphen, 
      DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4 ) );
info( date2str( dateAll, 321, DateDay::Digits2, DateSeparator::Hyphen, 
      DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4 ) );
Output:

Wednesday, July 25, 2018

SSRS - How use labels from AX in SSRS report

1) Click right mouse button on the field, select "Expression..".

2) Use "Labels!" prefix and then the number of AX label:

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