Problem with connection to database (function GM_DBTestConnection) in C#

ApliesAplies Global Mapper UserPosts: 35Trusted User
edited May 2013 in SDK
Hello,

Firstly, sorry for this duplicate thread but it can help someone.

I'm trying to connect me to a spatial database Oracle 10.2 .
I create a connection string with the function GM_DBMakeConnectionString and i get this :
"<gm_conn_str>:name=X,type=2,host=X.X.X.X,port=X,d atabase=X,user=X,password=X"

I think its good but when i try to call the function GM_DBTestConnection with this string, it displays a popup :
"Database connection failed : in <unamed>"

The connection between my PC and the database is confirmed because i tried to connect me with the Oracle client with the same user, password... The problem is just with SDK.

Code of my function :
unsafe public string ConnectionToDatabase(String server, String port, String database, String user, String password)
        {
            GlobalMapperDLL.GM_DBConnectionParms_t connectionParameters;
            connectionParameters.mConnectionName = Marshal.StringToHGlobalAnsi("test");
            connectionParameters.mDbType = GlobalMapperDLL.GM_db_type_t8.GM_DB_TYPE_ORACLE;
            connectionParameters.mServer = Marshal.StringToHGlobalAnsi(server);
            connectionParameters.mPort = Marshal.StringToHGlobalAnsi(port);
            connectionParameters.mDbName = Marshal.StringToHGlobalAnsi(database);
            connectionParameters.mUserName = Marshal.StringToHGlobalAnsi(user);
            connectionParameters.mPassword = Marshal.StringToHGlobalAnsi(password);

            // Allocate a large buffer
            IntPtr connectionStringPointer = Marshal.AllocCoTaskMem(1024);

            LastGMError = GlobalMapperDLL.GM_DBMakeConnectionString(connectionStringPointer, (uint)1024, ref connectionParameters);

            if (LastGMError == GlobalMapperDLL.GM_Error_t32.GM_Error_None)
            {
                // Copy the filename to our own string and free the dynamically allocated buffer
                string connectionString = Marshal.PtrToStringAnsi(connectionStringPointer);
                Marshal.FreeCoTaskMem(connectionStringPointer);
                GlobalMapperDLL.GM_DBTestConnection(connectionString);
                return connectionString;
            }
            else
            {
                return null;
            }
        }

Code of the type GM_DBConnectionParms_t in C# (I add it in GlobalMapperDLLWraper.cs) :
public struct GM_DBConnectionParms_t
        {
            public GM_db_type_t8 mDbType;
            public IntPtr mConnectionName;
            public IntPtr mServer;
            public IntPtr mPort;
            public IntPtr mDbName;
            public IntPtr mUserName;
            public IntPtr mPassword;
        } ;

Code of the declaration of the function GM_DBTestConnection in C# (I add it in GlobalMapperDLLWraper.cs):
// Test the input connection string to see if it can be used to connect to the database
        [DllImport(DLLFileName, EntryPoint = "GM_DBTestConnection")]
        public static extern GM_Error_t32 GM_DBTestConnection
             ( 
             String         aConnectionStr       // IN:  Connection parameter string
             );

Code of the declaration of the function GM_DBMakeConnectionString in C# (I add it in GlobalMapperDLLWraper.cs):
// Make a connection string from the input connection parameters
        [DllImport(DLLFileName, EntryPoint = "GM_DBMakeConnectionString")]
        unsafe public static extern GM_Error_t32 GM_DBMakeConnectionString
             (
             IntPtr                             aConnectionStr,   // OUT: The connection string buffer
             uint                               aBufferLength,    // IN:  The length of the connection string buffer
             ref GM_DBConnectionParms_t         aConnectionParms  // IN:  The connection parameters
             );


Best regards,

Tristan C.

Comments

  • bmg_bobbmg_bob Global Mapper Programmer Posts: 2,264
    edited April 2013
    The Global Mapper SDK (GlobalMapperInterface.dll) requires an Oracle file called oci.dll in order to run, so we include a copy of that file as part of the installation. When you connect to an Oracle database, however, you need to use the version of oci.dll that is part of your Oracle client instead of the one that is included with the Global Mapper SDK. Here is what you need to do to make that happen:
    1. Make sure that you have the Oracle client software installed.
    2. Make sure that the directory that contains oci.dll is in the system path. I think this is standard for most Oracle client installations.
    3. Make sure that the Oracle client matches Global Mapper SDK in that if you are using 32-bit Global Mapper SDK, you need to use the 32-bit Oracle client, and if you are using 64-bit Global Mapper SDK, you need to use the 64-bit Oracle client.
    4. In the Global Mapper SDK runtime directory (i.e., the directory where your program will find GlobalMapperInterface.dll), rename the file "oci.dll" to "oci_original.dll".
    5. Run your Global Mapper SDK client program. GlobalMapperInterface.dll should now find your copy of oci.dll instead of the original one.
    When you get the message box that says, "Database connection failed : in <unamed>", it means that the Oracle client can not be found.

    I hope this helps.

    Cheers,

    Bob
  • ApliesAplies Global Mapper User Posts: 35Trusted User
    edited April 2013
    It works, thanks!!

    Best regards,

    Tristan
  • ApliesAplies Global Mapper User Posts: 35Trusted User
    edited April 2013
    Hello,

    I need help again.
    The connection with the database works. Now i want to get a layer from a table. Like you said me in a previously post, I use the function "GM_LoadLayerListEx" :

    public IntPtr GetLayerFromDatabaseWithTableName(String connectionString, String nameTable)
    {
    IntPtr layer;
    uint nbLayers;

    LastGMError = GlobalMapperDLL.GM_LoadLayerListEx(connectionString, out layer, out nbLayers, GlobalMapperDLL.GM_LoadFlags_t32.GM_LoadFlags_HideProgress, nameTable);

    return layer;
    }

    I get a pointer not null but when i try to draw this layer with the other function of SDK (like i do with other created/loaded layers), i have nothing (the bitmap is not null but its empty when its displayed). Moreover, when i try to update the current view rectangle from the structure GM_LayerInfo_t of the layer (to use it in the function GM_DrawLayer), i have a nullPointerException. Code :

    GlobalMapperDLLWrapper.GM_LayerInfo_t firstLayerInfo = new GlobalMapperDLLWrapper.GM_LayerInfo_t();
    IntPtr firstLayerInfoPtr = GlobalMapperDLL.GM_GetLayerInfo(layers[0]);
    firstLayerInfo = (GlobalMapperDLLWrapper.GM_LayerInfo_t)Marshal.PtrToStructure(firstLayerInfoPtr, typeof(GlobalMapperDLLWrapper.GM_LayerInfo_t)); //nullPointerException here


    Is there a "GM_LayerInfo_t" structure for layers loaded from database?
  • ApliesAplies Global Mapper User Posts: 35Trusted User
    edited April 2013
    Ok, it works, my bad.
    I forgot to iterate on the pointer return by the function GM_LoadLayerListEx even only one layer into.

    However, its very long to import a table of 400 lines from spatial database (1min30 about). Is this normal? Is there a way to increase the performance?

    Best regards,

    Tristan
  • bmg_bobbmg_bob Global Mapper Programmer Posts: 2,264
    edited April 2013
    I agree that your performance seems kind of slow. I have never tested using a C# client, but I don't imagine that would add too much overhead. I have a few questions for you:

    Can you narrow down what step in the process is taking so long?
    Have you tried displaying the same data from your database in a different client application?
    Is the database on a network? (The slowest part of a query like this is sending the result data across the network.)
    If so, are you going through a VPN, or something like that, when you access the database?

    Cheers,

    Bob
  • ApliesAplies Global Mapper User Posts: 35Trusted User
    edited April 2013
    Hello,

    With C# debugger i can see that it's GM_DBGetTableList and GM_LoadLayerListEx functions which take so long time.
    The database is on local network.
    And there is no VPN or something like that.

    For example, the operation to import a table with 712 rows to display world map (coastline) take one minute before display the dialog box to choose projection system and its the same just to import list of table name's.

    Best regards,

    Tristan
  • bmg_bobbmg_bob Global Mapper Programmer Posts: 2,264
    edited April 2013
    Hi Tristan,

    I used Global Mapper to load a table with 2600 features (world map) and another with 1100 features (US map), and both loaded within a few seconds. Our Oracle database is also a network database, though there could be numerous differences between the configurations of our two databases.

    Do you have a way to load the same data from your Oracle database using another application? I suppose even using Global Mapper would provide some insight. Have you run a release version of your application instead of a debug version?

    Cheers,

    Bob
  • ApliesAplies Global Mapper User Posts: 35Trusted User
    edited April 2013
    Hello,

    I would try with Global Mapper (in your application) to import a layer from my database but i have this message : "It appears that Global Mapper cannot communicate with the Oracle client. Please see the help for information about using your Oracle client with Global Mapper".
    The path to Oracle client is in the environment variable PATH.
    I need a license or its just a problem with the path of Oracle client?

    Best regards,

    Tristan
  • ApliesAplies Global Mapper User Posts: 35Trusted User
    edited April 2013
    I traced the requests of SDK on my database. I have 450 tables and when I request just one table (one layer), I see a request on each table (each layers) :

    SELECT COUNT(*)
    FROM
    ALL_SDO_GEOM_METADATA u, TABLE(u.diminfo) t WHERE u.table_name =
    'NAMETABLE
    HIDE' AND u.column_name = 'GEOMETRY'

    and

    SELECT SRID
    FROM
    ALL_SDO_GEOM_METADATA WHERE TABLE_NAME = UPPER('
    NAMETABLEHIDE') AND
    COLUMN_NAME = UPPER('GEOMETRY')


    These two requests are executed for each table (replace NAMETABLEHIDE obviously), even when I request just one. Is it normal or not?
    These two requests takes 0.2 second for each table : 0.2 * 450 = 90 seconds.

    Thank you for your patience and for your help.
    Best regards,

    Tristan
  • bmg_bobbmg_bob Global Mapper Programmer Posts: 2,264
    edited April 2013
    Hi Tristan,

    Thank you for this trace information. When opening a single table, the code should be getting the SRID only for the requested table, not for all of them (that should only happen when getting the full list of tables). I will investigate to see if there is a bug in there somewhere.

    Can you post the call you are making to request that a single table be loaded?

    Cheers,

    Bob
  • ApliesAplies Global Mapper User Posts: 35Trusted User
    edited April 2013
    Hello,

    I call the function "LastGMError = GlobalMapperDLL.GM_LoadLayerListEx(connectionString, out layer, out nbLayers, GlobalMapperDLL.GM_LoadFlags_t32.GM_LoadFlags_HideWarnings, nameTable);" to get a layer from database.

    Moreover, when I want to get list of table name, I need to call the function "GM_DBGetTableList" and after "GM_DBTableList_GetTableName". I think the first function get not only list of table but the features of table too(dimensions for example), and this take much time. When I traced the request to get the list of all table name on my database, I saw lots of requests on each table too and I don't know if its normal.

    Best regards,

    Tristan
  • bmg_bobbmg_bob Global Mapper Programmer Posts: 2,264
    edited April 2013
    Hi Tristan,

    Thanks for the information. As you have noted, GM_DBGetTableList gets a variety of descriptive information about the tables, but that information is not needed -- only the table names are needed. I have made some changes so that the extra information is only retrieved when opening a table to read its contents.

    I also found some improvements to be made when reading a table.

    I am in the process of testing these changes. We will get you a maintenance build once this testing is complete.

    Cheers,

    Bob
  • global_mapperglobal_mapper Administrator Posts: 17,238
    edited April 2013
    Tristan,

    I've incorporated Bob's changes into a new build for you to try. I have placed a new build at http://www.bluemarblegeo.com/downloads/global-mapper/global_mapper14.zip with the latest changes for you to try. Simply download that file and extract the contents into your existing v14.xx installation folder to give it a try. If you are using the 64-bit v14 version there is a new build at http://www.bluemarblegeo.com/downloads/global-mapper/global_mapper14_64bit.zip .

    Thanks,

    Mike
    Global Mapper Guru
    gmsupport@bluemarblegeo.com
    Blue Marble Geographics for Coordinate Conversion, Image Reprojection and Vector Translation
  • ApliesAplies Global Mapper User Posts: 35Trusted User
    edited May 2013
    Hello,

    I extracted the contents (just the file "QuickPDFDLL.DLL") into the directory of compilation (debug and realease) but there is no change. Get the list of table name take one minute about and get a layer too. Moreover, when i trace the requests on my database, i see the same useless requests than before.
    Are you sure you have given me the right release?

    Best regards,

    Tristan
  • global_mapperglobal_mapper Administrator Posts: 17,238
    edited May 2013
    Tristan,

    I apologize I made new application releases and not the SDK, which is what you need. I have placed a new SDK build at http://www.globalmapper.com/GlobalMapperSDK_v14_latest_beta.zip for you to try.

    Thanks,

    Mike
    Global Mapper Guru
    gmsupport@bluemarblegeo.com
    Blue Marble Geographics for Coordinate Conversion, Image Reprojection and Vector Translation
  • ApliesAplies Global Mapper User Posts: 35Trusted User
    edited May 2013
    I have very good performance.
    Now, import the table's data from my database take less than one second. The transformation of this data in layer of the SDK depends on size of the layer (more than one minute for the bigger), but its not a problem cause there is a popup to display the progress.
    Import the list of all table's name take several seconds (3 or 4 seconds).

    Those performances are very good !

    Thank you for you patience and for your help.

    Tristan
  • ApliesAplies Global Mapper User Posts: 35Trusted User
    edited May 2013
    Hello,

    I have just a little question. The function "GM_DBGetTableList" return all table name or I must call the function "GM_DBTableList_GetTableName" on the result of the first?

    Best regards,

    Tristan
  • bmg_bobbmg_bob Global Mapper Programmer Posts: 2,264
    edited May 2013
    Hi Tristan,

    I am glad to hear that the performance has improved. Thank you for your help.
    Aplies wrote: »
    I have just a little question. The function "GM_DBGetTableList" return all table name or I must call the function "GM_DBTableList_GetTableName" on the result of the first?

    GM_DBGetTableList returns a list of all tables in the database. You can get the name of one table in the list by calling GM_DBTableList_GetTableName, and passing it the index of the table name that you want. Here is a code snippet that gets the name of the first table in the list:
    // Get the list of tables to load
    CString aDBConnectionString; //connection string goes here"
    CString theTableName;
    GM_DB_TableList_t32 theTableList = NULL;
    GM_Error_t32 theErr = GM_DBGetTableList( &theTableList, aDBConnectionString );
    
    if ( GM_Error_None == theErr )
    {
      uint32 theCount = 0;
      theErr = GM_DBTableList_GetCount( &theCount, theTableList );
    
      if ( theCount > 0 )
      {
        // For the purposes of this example, only get the first table in the list.
        char theTempTableName[_MAX_PATH];
        theErr = GM_DBTableList_GetTableName( theTempTableName, 0, theTableList );
        theTableName = theTempTableName;
      }
    
      // Free the table list
      GM_DBTableList_Free( &theTableList );
      theTableList = NULL;
    }
    

    I hope this helps.

    Cheers,

    Bob
Sign In or Register to comment.