Forum Discussion

S_Seydel's avatar
S_Seydel
Contributor
5 years ago

ado recordsets wrong with datatype text

Hello,

 

for the last 3-4 years our test object had a MySQL database, lately this was changed to a MariaDb database.

On the MariaDb database when a table column is datatype text, mediumtext or longtext an ado recordset opened from my javascript script in TestComplete always contains the first tupel two times and the last tupel is missing.

When I run the same code, with a different connectionstring of course, against a MySQL database the recordset's content is correct.

 

My code:

  //  Opens a connection to the used database.    
  function openDatabaseConnection()
  { 
    //  Check if a connection is open. If a connection is open, close it and set it to false.
    if (connection)
    {
      connection.Close();
      connection = false;
    }

    //  Build the connection string (MariaDB)
    let connectionString = "Driver={MariaDB ODBC 3.1 Driver};" +
                           "TCPIP = 1;" +  
                           "SERVER= 192.168.178.33;" +
                           "UID= TestUser;" +
                           "PWD= TestPassword;" +
                           "DATABASE= testMariaDb;" +
                           "CHARSET=utf8";

    //  Build the connection string (MySQL)     
//    let connectionString = "Provider=MSDASQL;" + 
//                           "Driver={MySQL ODBC 8.0 Unicode Driver};" + 
//                           "Server= 192.168.178.26;" +
//                           "Database= testMariaDb;" +
//                           "Uid= TestUser;" +
//                           "Pwd= TestPassword;";
                                           
    var connection = Sys.OleObject("ADODB.Connection");
    connection.ConnectionString = connectionString;
    connection.Open();
    return connection;
  }

// Get the collection of tupels we want to work with. function openRecordset() { // Open the database connection. let connection = openDatabaseConnection(); // Check if a recordset is open. If a recordset is open, close it and set it to false. if (recordset) { recordset.Close(); recordset = false; } // Define the database table to get the recordset from. let table = "testTable"; // Additional parameters we need for the open method (ADO recordset). // CursorTypeEnum is adOpenDynamic. let cursorTypeEnum = 2; // LockTypeEnum is adLockOptimistic. let lockTypeEnum = 3; // CommandTypeEnum is adCmdUnknown. let commandTypeEnum = 8; var recordset = Sys.OleObject("ADODB.Recordset"); // All is set, now let's open the recordset. recordset.Open(table, connection, cursorTypeEnum, lockTypeEnum, commandTypeEnum); // Fill the recordset's content into an array to inspect it while debugging. let recordsetArray = new Array(recordset.GetRows()); return recordset; }

The database table to get the recordset from:

The recordset's content using a MySQL database:

The recordset's content using a MariaDb database:

I can't figure out where the problem is and would be very thankful for any help.

 

Best Regards

S_Seydel

    • sonya_m's avatar
      sonya_m
      SmartBear Alumni (Retired)

      Hi S_Seydel , I see that you are working with the Support Team on this. Please keep us informed of your results. Thank you:smileyhappy:

      • S_Seydel's avatar
        S_Seydel
        Contributor

        Hello,

         

        Support suggested to configure an ODBC data source on the windows system and to make sure the checkbox for dynamic cursor usage is checked:

        Then the connection string has to be changed to:

        let connectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=configuredDataSource;";

        This changes the behaviour, but there are still wrong values in the recordset.

        At this point Support endorsed my anxiety that the error is not derived from TestComplete but from the mechanism that is used to connect to the database.

         

        Unfortunately my final conclusion is that adoDB does not work properly with mariaDB databases.

        Therefore there is no solution to accept.

         

        Kind regards

        S_Seydel