Database Manager software: Navicat Premium Essentials

Jul 11
2017

In my daily work, I have to deal with several different databases. Until now, it were mostly Oracle, SQL Server and MySQL, but in the last time SQLite and PostgreSQL have been added to my list.

In the past I had used mostly DB Manager from DBTools software in Brasil, but the last release of this software is from the year 2010, so about 7 years old, and it does not work with the Oracle database I have to deal with.

Searching for a valid successor without spending too much money (I need 3 licenses as we are 3 developers in our company), I have found Navicat Premium Essentials by Premiumsoft Cybertech Ltd, and after a short trial period I have acquired the needed licenses, and was really happy with it.

Now they have released their next version, Navicat 12, and I have upgraded my licenses to the new version.

What I have to say: they are very serious. I have discovered two bugs in the new version and opened a support issue with them. In a relatively short period they answered and have acknowlegdet the bugs (the immediatly, for the other it needed a day more and more specifications from my).

They have several different versions of their database manager, but for my needs the Premium Essentials version is the right one, as it has support for all databases I need (for a short period I needed also Firebird support, not available in Navicat), and is relatively affordable.

Set initial date to WPF Calendar control

Mar 17
2017

In WPF, you can set the SelectedDate property to any DateTime value, but this value is not shown as initial date in the calendar control.

If you wish to set the initial date, you need to set the DisplayDate property.

If you use databinding, you can bind both values to the same property on the ViewModel, or to two different, what you like more. Of course in the latter case the initial value needs to be set to both properties.

Another suggestion: if you work with the Calendar control directly, you should embed it in a ViewBox control to have it adjusted when you resize your window.

Firebird specials in table structure for varchar and UTF-8

Nov 15
2016

In my Visual Objects applications based on DBFs I have a routine that checks the structure of the DBF tables, compares them with the structure that is required and adjusts the structure.

I’m now working on a similar functionality for my Firebird based .NET applications. To retrieve the list of the currently existing user tables, I’m using such a statement:

select rdb$relation_name as tablename from rdb$relations
where rdb$view_blr is null and 
(rdb$system_flag is null or rdb$system_flag = 0)

and to retrieve the actual structure of such a table I use the following statement:

select rf.rdb$field_position as fieldpos, 
TRIM(rf.rdb$field_name) as fieldname, 
f.rdb$field_type as fieldtype, 
f.rdb$field_length as fieldlen,
f.rdb$field_scale as fielddec, 
(f.rdb$null_flag = 1) OR (rf.rdb$null_flag = 1) as canbenull 
from rdb$relation_fields rf 
JOIN rdb$fields f ON f.rdb$field_name = rf.rdb$field_source 
WHERE upper( rf.rdb$relation_name ) = upper( 'mytable' ) 
order by rf.rdb$field_position

To have no limitations with foreign languages, I have opted to use the UTF-8 character set for all varchar columns.

And now I was very surprised: the select statement returned 4 times the actual field width: for a field defined as varchar(20) the query returned 80 as field length.

My conclusion is very simple: since every UTF-8 character can take up to 4 bytes, Firebird reserves 4 bytes for every character.

I have now found the following StackOwerflow article about this:
http://stackoverflow.com/questions/822837/firebird-utf8-varchar-size

Firebird 3.0, Embedded server and the .NET provider

Nov 15
2016

For local data, the embedded server of Firebird is very welcome because it does not needs any configuration.

With Firebird 2.5, a special DLL (fbembed.dll) was needed, but Firebird 3.0 has added this functionality into the standard fbclient.dll.
So, if using the embedded server of Firebird 3.0 instead of the one of Firebird 2.5, you need to specify this in the connection string. It is the best to use the ClientLibrary property of the FBConnectionStringBuilder (namespace FirebirdSql.Data.FirebirdClient).

The following files need to be put in your application directory (please make attention to use the correct bitness – 64 bit files for a 64 bit application and 32 bit files for a 32 bit application):
fbclient.dll
ib_util.dll
plugins\engine12.dll

Normally, you should NOT need to put a firebird.conf file in you application directory.

References:
http://stackoverflow.com/questions/37444411/unable-to-connect-firebird-3-0-embedded-version
http://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-engine.html

SideBySide or Registration-Free Activation of Vulcan.NET/X# components

May 06
2016

In this post I will try to list some things that are needed for registration-free use of Vulcan.NET or X# components in Visual Objects applications. Some of these issues have costed me several days of tries.

First, a background article from MSDN: How to: Configure .NET Framework-Based COM Components for Registration-Free Activation

Then: a big “Thank You” to Meinhard Schnoor who has helped me a lot not only with this issue, but often also with other issues.

How SxS (how I will call it to shorten) works basically? I’ll try to simplify at maximum what is needed and where the potential problems stay.

Normally, COM works only with “registered” components. This has some issues:

  • you can only register components from your local machine, not from a network share
  • you need administrative rights to register the component
  • the component registration is global for the machine, you cannot use different versions of the component
  • With SxS things are different:

  • components are called from the program directory without installation
  • every application can use the proper component version
  • of course no administration rights are needed
  • Unfortunately, SxS is very bad documented, and there are not many helpful articles and tools available. And if something goes wrong, it may be very difficult to find out the “why”. sxstrace.exe normally is a help, but not every time. Manifest caching can also disturb.

    Manifests

    Manifests are the basic mechanism for SxS, they are needed for both the component and the executable. The manifest for the component needs to specify the available classes and GUIDs, and the manifest for the executable needs to specify the name of the component. At loading time, the loader first reads the manifest for the executable and then the manifest for the component. If something goes wrong, you exe will not start. Look first in the event viewer and then use sxstrace to see the errors. Because of manifest caching it can be necessary to change the time stamp of your executable (best: regenerate it). Manifests can be standalone (name of the exe or dll with added .manifest extension), or built into the executable. My recommendation would be to use standalone manifests for development and embedded manifests for deployment.

    GUIDs

    Every class and also the interfaces need their own GUID – generate a new one with the guidgen tool (You’ll need the Visual Studio Developer Command Prompt for this). Please pay attention to use the right GUIDs!

    Versions

    The entire system is very sensible to versioning! You need to use the correct version in every place: manifests and component itself (an AssemblyInfo.prg with the correct AssemblyVersionAttribute and the AssemblyFileVersionAttribute entries is needed – otherwise SxS will give no errors, but don’t load the component! This alone has costed me several days of tries.

    Naming

    Correct naming is the next potential issue: this is not only important for the name of the DLL and for the name of the classes, but also for the namespace used in the component. Name the component the same as the used namespace (and use a namespace!) to save you troubles (with naming errors the registered assembly may work, the SxS loading will fail silently at runtime).

    Any error in one of the components will cause the failure of the entire loading mechanism.

    Getting started with the AdWords API

    Mar 17
    2016

    Actually, I have a project where I have to retrieve Google AdWords statistic data and store it locally to generate reports.

    Unfortunately, I lost a lot of time to find out how to start. Therefore you can find here the most important things from the view of a Windows programmer.

    1) request an API token. Google ask something from you, like the use you make, if your software will be distributed, if you plan only read access or also to write. You have also to write a short project description. And if you don’t use your API token for more as 3 months, they will ask you the reason. More informations are here:
    https://developers.google.com/adwords/api/docs/signingup

    2) decide how you will authenticate. OAuth2 is not very simple to understand. Unfortunately, I had opted first for a service authentication. This one is very complicated and you need a Google Apps domain for it, i.e. one of your domains must be registered for a Google cloud service like GMail or Apps. So my recommendation is to use the APPLICATION authmode.

    3) on the developers console https://console.developers.google.com you need then to request a new client ID – as type I opted for “OTHER”, as my project is neither a web project, nor a mobile app.

    4) for the authentication you need basically 4 things: your API token, a client ID, a client secret and a refresh token. The API token you should have from step 1, client ID and client secret come from step 3, and the refresh token will be described later.

    5) generate an AdWords account that has read-only access to the Adwords accounts you need – if you are a developer like me, ask the Adwords guys of your company.

    6) download the client libraries here https://github.com/googleads/googleads-dotnet-lib. I would recommend to download the entire project as ZIP and build the client libraries yourself – open the Visual Studio solution file and build all. In this solution you will find a lot of useful things: not only the complete source code of the client libraries, but also samples in C# and VB.NET

    7) the OAuth2 flow is not so easy to implement – I have used the OAuthTokenGenerator application from the client libraries from step 6. This application (beware: you have to start this application with elevated rights as it uses internally the httpListener class!) gives you the last pieces needed for authentication: the refresh token

    8) use the samples from the client libraries to understand how the entire API works. It is very well documented, and the fact that you have the sources is very helpful. For the authentication best you use the app.config file (yourApplication.exe.config), copy it from the sample in the client library and replace the authentication data

    9) last error I made: the AdWords account I used had no campaigns in it since it was the account of the company itself, I had to use one of the customer accounts and set it not in the .config file, but in my program. How this can be accomplished, is documented in the app.config file.

    I have to make a compliment to Google: they are very helpful, the API is well documented and you have the full sources to it.

    httpListener class in Vulcan.NET/X#

    Mar 05
    2016

    This is code for a Vulcan.NET or X# Listener class (ported over from C#):


    // Application : HttpListener
    // httpListener.prg , Created : 16.02.2016 08:37
    // User : Wolfgang
    // C# code see here:
    // http://mikehadlow.blogspot.it/2006/07/playing-with-httpsys.html
    //
    // to make it run without admin rights you need something like this one:
    // netsh http add urlacl url="http://*:8080/" user=[username] listen=yes

    #using System
    #using System.Net
    #using System.Text
    #using System.IO
    #using VulcanHttpListener.riedmann.it

    begin namespace VulcanHttpListener.riedmann.it

    function Start() as void
     local oProgram as MainProgram

     oProgram := MainProgram{}
     oProgram:Start()

     return

    class MainProgram
     protect _oListener as HttpListener

     constructor()

      _oListener := HttpListener{}

     return

     method Start() as void

      _oListener:Prefixes:Add( "http://*:8080/" )
      _oListener:Start()
      Console.WriteLine( "Listening on port 8080, hit enter to stop" )
      _oListener:BeginGetContext( AsyncCallback{ self:GetContextCallback }, null )
      Console.ReadLine()
      _oListener:Stop()

     return

     method GetContextCallback( oResult as IAsyncResult ) as void
      local oContext as HttpListenerContext
      local oRequest as HttpListenerRequest
      local oResponse as HttpListenerResponse
      local oSB as StringBuilder
      local oString as string
      local oBuffer as byte[]
      local oOutput as Stream

      oContext := _oListener:EndGetContext( oResult )
      oRequest := oContext:Request
      oResponse := oContext:Response

      oSB := StringBuilder{}
      oSB:Append( e"\n" )
      oSB:AppendFormat( e"HttpMethod: {0}\n", oRequest:HttpMethod )
      oSB:AppendFormat( e"URI: {0}\n", oRequest:Url:AbsoluteUri )
      oSB:AppendFormat( e"Local path: {0}\n", oRequest:Url:LocalPath )
      oSB:Append( e"\n" )
      foreach cKey as string in oRequest:QueryString:Keys
       oSB:AppendFormat( e"Query: {0} = {1}\n", cKey, oRequest:QueryString[cKey] )
      next
      oSB:Append( e"\n" )

      oString := oSB:ToString()
      oBuffer := System.Text.Encoding.UTF8:GetBytes( oString )
      oResponse:ContentLength64 := oBuffer:Length
      oOutput := oResponse:OutputStream
      oOutput:Write( oBuffer, 0, oBuffer:Length )

      _oListener:BeginGetContext( AsyncCallback{ self, @GetContextCallback() }, null )

      return

    end class

    end namespace

    Windows service caveats with Process.Start

    Feb 29
    2016

    These days, I was working on a webservice that worked directly with http.sys.
    This service was planned to wait for requests, and launch programs depending on the request.
    Firstly, I planned to use the local system account, and launch the external programs as specific user. This program should be started from a network share, using UNC paths.
    Unfortunately, that does not work, Process.Start cannot do that:
    http://blogs.msdn.com/b/winsdk/archive/2013/11/12/runas-verb-process-start-doesn-t-work-from-a-localsystem-net-service.aspx

    My next decision was to add a user for this purpose (to run the Windows service). Unfortunately, Process.Start ignores the essential settings for a Windows service

    oInfo:UseShellExecute := false
    oInfo:CreateNoWindow := true
    oInfo:ErrorDialog := false
    oInfo:WindowStyle := ProcessWindowStyle.Hidden

    The page from MSDN (https://msdn.microsoft.com/library/0w4h05yb%28v=vs.100%29.aspx) states:
    “If the UserName and Password properties of the StartInfo instance are set, the unmanaged CreateProcessWithLogonW function is called, which starts the process in a new window even if the CreateNoWindow property value is true or the WindowStyle property value is Hidden.”
    At the moment I’m not changing the user from the service, but executing the external program under the service account.

    I had also another small issue: since I was reading the password from an ini file, I thought the StartInfo:PasswordInClearText property was enough. On my development machine it worked, but crashed on the production server, an SBS 2011 (based on Windows Server 2008 R2) because with the .NET Framework 4 this property simply does not exists.

    Another issue: if you plan to start a process as another user from a service: this user needs local logon rights (on a server OS this is a manual change)

    Event sample

    Feb 21
    2016

    Today, trying to understand also events in Vulcan.NET/X#, I wrote the following sample that should give an idea how events work in Vulcan.NET and X#.

    #using EventSample

    begin namespace EventSample

    function Start( ) as void
      local oClass2 as Sample2Class

      System.Console.WriteLine(“Start of the SampleEvent application”)

      oClass2 := Sample2Class{}
      oClass2:RegisterEvent()

    return

    public delegate SampleHandle( cParameter as string ) as void

    class SampleClass
      public event oSampleEvent as SampleHandle

    constructor()

      return

    method CallSampleEvent() as void

      oSampleEvent( “Hi there” )

      return

    end class

    class Sample2Class

    constructor()

      return

    method RegisterEvent() as void
      local oClass as SampleClass

      oClass := SampleClass{}
      oClass:oSampleEvent += self:method2
      oClass:CallSampleEvent()

      return

    public method method2( cParameter as string ) as void

      System.Console.WriteLine( “method2:” + cParameter )

      return

    end class

    end namespace

    Delegates sample

    Feb 21
    2016

    Hi,

    when working with X# and Vulcan.NET, I have created this particular sample. It should help understand how delegates work (and that they are immutable):

    #using DelegateSample

    begin namespace DelegateSample

    public delegate SampleDelegate( cParameter as string ) as void

    function Start( ) as void
      local oSample as SampleClass
      local oSample2 as Sample2Class
      local oDelegate as SampleDelegate
      local oDelegate2 as SampleDelegate

      System.Console.WriteLine(“Start SampleDelegate”)

      oSample := SampleClass{}
      oSample2 := Sample2Class{}

      oDelegate := SampleDelegate{ oSample:Method1 }
      oDelegate += oSample:Method2
      oDelegate += oSample2:Method3

      oDelegate( “Hi” )
      oDelegate2 := oDelegate

      oDelegate -= oSample:Method2
      oDelegate -= oSample2:Method3

      oDelegate( “Hott” )
      oDelegate2( “Hups” )

      return

    class SampleClass

      constructor()

      return

      public method Method1( cParameter as string ) as void

        System.Console.WriteLine( “method1, parameter ” + cParameter )

        return

      public method Method2( cParameter as string ) as void

        System.Console.WriteLine( “method2, parameter ” + cParameter )

        return

    end class

    class Sample2Class

      public method Method3( cParameter as string ) as void

        System.Console.WriteLine( “method3, Sample2Class, parameter ” + cParameter )

        return

    end class

    end namespace