Monthly Archives: April 2014

Strange characters

In some cases, you get unexpected weird results being returned from your database like: Testù�Summary. This may be expected as one inserted TestùęSummary. Apparently symbols like ę were not recognised and were subsequently translated into �.
A likely reason is that the so-called codepage is wrong. Characters like ę are not included in the common characterset and an extended characterset (like unicode) must be used.
Fortunately, most DBMS support the unicode. As an example, we take an example from Teradata. Look as this code:

CREATE SET  TABLE SAN_D_FAAPOC_01.TestUnicode ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
Ident VARCHAR(255) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
Serial INTEGER,
Node VARCHAR(64) CHARACTER SET UNICODE NOT CASESPECIFIC)
PRIMARY INDEX (Serial);

insert into SAN_D_FAAPOC_01.TestUnicode(ident,node,serial)
values('TestùęSummary','TestùęSummary',1235);

SELECT	Ident, Serial, Node
FROM	SAN_D_FAAPOC_01.TestUnicode;

The results are:

	Ident	Serial	Node
1	Testù�Summary	1,235	TestùęSummary

The nice thing about Teradata is that columns can be defined as unicode-columns. Hence nothing extra needs to be done to store such unicode characters.
A similar situation exists with MySQL. Also in that DBMS, we may store data in columns that are defined as being unicode. As an example, one may use this code snippet:

CREATE TABLE t1
(
    col0 CHAR(10),
    col1 CHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci
);

insert into t1 values('TestùęSumm','TestùęSumm');

Also here, we have an illustration of the purpose on Unicode. It is an extension of the standard ASCII characterset to include all characters from all living languages. I understand that even Gothic and Music characters are included in unicode. A subset of unicode is the ASCII set. On top of that characters are included that are not within the ASCII dataset.
I understand we have different version of unicode. One such version is UTF-8. This version uses one byte to store the common latin characters such as ‘A’, ‘B’,’1′ etc. For the more exotic characters, more byte are used. An example is recently introduced “€” that takes 3 bytes. Other characters use 4 bytes.
On average a western text is stored quite efficiently in UTF-8. As most characters only use 1 byte, we end up with a file size (in terms of bytes) that equals the number of characters.
Another implementation is UTF-16 that uses 2 bytes per character. In that case, the file size, in terms of bytes, is double that of the number of characters. A western text, written in UTF-16 is then double as big as it would have been in UTF – 8.
As an example, I include two texts, one written in ASCII and one in UTF-8:
utf

Starting vbscript

I recently came across vbscript. I was a bit curious to see how this works. As a first step, I created a small batch file (“tom.bat”) that invokes a vbscript. This script reads as:

cscript C:\Users\TOM.VAN-MAANEN\tom.vbs "KINLW7D6LPVM1"
exit %ERRORLEVEL%

This script shows how such vbscripts can be invoked. One may start a script from the command line with the command “cscript”. This command is followd by a scriptname and a parameter. The script is ” C:\Users\TOM.VAN-MAANEN\tom.vbs” and the parameter is “KINLW7D6LPVM1”. The script returns a runcode that can be displayed with exit %ERRORLEVEL%. As an example, I also provide the script:

On Error Resume Next
Const CONVERSION_FACTOR = 1024
Const ONE_SEC = 1000
If WScript.Arguments.Count = 0 Then
 Wscript.Echo "Usage: tom.vbs server1 [server2] [server3] ..."
 WScript.Quit
End If
For Each Computer In WScript.Arguments
  Set objWMIService = GetObject("winmgmts://" & Computer)
  Set colLogicalDisk = objWMIService.InstancesOf("Win32_LogicalDisk")
  wscript.Echo colLogicalDisk.Count & " drives found on " & Computer 
  For i = 1 to 1
    For Each objLogicalDisk In colLogicalDisk
    wscript.Echo "There are " & int((objLogicalDisk.FreeSpace / (CONVERSION_FACTOR * CONVERSION_FACTOR * CONVERSION_FACTOR))) & _ 
    " Gigabytes on "  & " " & objLogicalDisk.DeviceID
    Next
    Wscript.Sleep ONE_SEC
  Next
Next

What does this script do? This script checks the disk space on each drive on the computer “KINLW7D6LPVM1”. It uses existing objects that are available in a windows environment with the WMI service. A class within this object provides information on the drives. It provides information on the number of drives and the free space that is available. The information is gathered and exported in a loop that displays the information on each of the drives. I also created a loop to allow for a regular check on available diskspace. Within this loop a delay is included.

Another nice example is this vbs programme:


Const cAppend = 8


paramStr = Wscript.Arguments.Item(0)


if (paramStr = "ONZIN") then
	paramFile = "C:\Users\TOM.VAN-MAANEN\test_list_parm.txt" '''''''''''''''BW parameter txt file for LASTSHIP DATE and others which run once a day
	InboundFolder = "C:\Users\TOM.VAN-MAANEN\"  '''''''''''''''BW TD folder path 
	logfile = "C:\Users\TOM.VAN-MAANEN\test_list_log.txt" 
end if


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objLogFile = objFSO.OpenTextFile(logfile, cAppend, True) 'open the text file for appending


if (trim(paramStr) = "") then
  objLogFile.WriteLine paramStr & " file checking aborted, error in input parameter value"
  set objFSO = nothing
  WScript.Quit(1)  ''''FAILURE
end if

IF objFSO.FileExists(paramFile) Then 

  Set fileList = objFSO.OpenTextFile(paramFile, 1) 'open the text file for reading only
  i = 0

'''' ------------------- Missing file listing ----------------
  Do While Not fileList.AtEndOfStream 'loop until the end of the file
   	strfilename = ""
	strfilename = fileList.ReadLine 
		
    If (Not strfilename = "") then ''''check filename is not empty
	If NOT (objFSO.FileExists(InboundFolder & strfilename))  then ''''check file exists in inbound folder
  		if (i =0) then 
		objLogFile.WriteLine "-------------------------------**** Missing file list *****----------------------------------"
		end if

		i = i + 1

		objLogFile.WriteLine cstr(i) & ". " & strfilename
    	End If

    End If	    
  Loop '''' end loop for missing files 
  fileList.Close
 

  If (i > 0) then '''' atleast some missing files, terminate
		objLogFile.WriteLine " "
 		objLogFile.WriteLine "========================================================="	  
   		objLogFile.WriteLine "File list not complete in Landed folder for " & paramStr & " at " & Now
	  	objLogFile.WriteLine "========================================================="
  		fileList.Close 
  		objLogFile.Close
		set objFSO = nothing
 		WScript.Quit(1) ''''FAILURE
  End If

  objLogFile.WriteLine " "
  objLogFile.WriteLine "========================================================="
  objLogFile.WriteLine "===== " & paramStr & " File checking process COMPLETED SUCCESSFULLY====" & Now
  objLogFile.WriteLine "========================================================="

  fileList.Close
  objLogFile.Close
  set objFSO = nothing
  WScript.Quit(0) '''''SUCCESS

ELSE
  objLogFile.WriteLine paramStr & " file checking Process terminated, parameter file is missing in scripts folder for " & paramStr
  objLogFile.WriteLine "========================================================="

  objLogFile.Close
  set objFSO = nothing
  WScript.Quit(1) ''''FAILURE
END IF

This script can be started with: cscript C:\Users\TOM.VAN-MAANEN\test_list_scr.vbs “ONZIN”. The idea in this script in that file is read that contains a list of file names. For each file name, it is checked as to whether such a file exits in a given directory. If that is not the case, such file name is added to an array. In a subsequent step, the list with file names that do appear in a given location are written to a file.