Search code examples
mysqlodbcdrivermysql-workbenchlotusscript

LotusScript - How to fix LS:DO Error 720- ODBC could not complete the requested operation

I am working with LotusScript and trying to integrate my legacy project with MySQL. I have done this many times with other MySQL databases and everything worked fine till now.

Using MySQL-ODBC driver v.5.3 MySQL ODBC Unicode driver. But in my new task I am trying to connect to another remote mysql database. (I am saying again that everything is working fine with other databases)

My connection-test code:

    Dim mysqlConnection As New ODBCConnection
    Dim sqlQuery As New ODBCQuery
    Dim result As New ODBCResultSet

    Dim selectAllQuery As String

    Dim doc As NotesDocument

    Dim session As New NotesSession
    Dim generalDb As NotesDatabase  

    On Error Goto errblock

    Set generalDb = session.CurrentDatabase

    Call mysqlConnection.ConnectTo("Syncronization_agent","root","111111")

    If Not mysqlConnection.IsConnected Then
        Print "Connection is not working! Try again later!"
        Exit Sub
    Else 
        Print "Success connection to MySQL database - temp_data_db!"
    End If

    Set sqlQuery.Connection = mysqlConnection
    Set result.Query = sqlQuery

    selectAllQuery = "SELECT * FROM temp_data_db.temp_data_table WHERE id != ''"
    sqlQuery.SQL = selectAllQuery
    result.Execute

    result.Close(DB_CLOSE)
    mysqlConnection.Disconnect
errblock:
    If Cstr(Err) Then
        Print Cstr(Err) + " Message: " + Cstr(Error)
        If result.GetError <> DBstsSuccess Then
            result.Close(DB_CLOSE)
            Messagebox result.GetErrorMessage + Chr(13) + result.GetExtendedErrorMessage
            mysqlConnection.Disconnect
            Exit Sub
        End If
    End If

I am getting this, none-typical, error: enter image description here

I have debugged this code and this error comes in line with connectTo.

1. I have checked the ODBC driver connection-test and it outputs that connection to database is successful.

2. Tryed to run sql queries from MySQL Workbench to this database - SELECT,INSERT,UPDATE is working. (but in Workbench when I am trying to get db info and getting this errors (maybe this can create some problems or not..):

enter image description here

enter image description here

enter image description here

enter image description here

Question: How to fix this issue? Is there some solutions for this? Or some thoughts?

Solution

  • I have solved this issue!

    The problem wasn't in access or in the code!

    The problem occurs when I was creating my ODBC connection in Windows system with the long name!

    When I have changed the ODBC connection name (in the ODBC Administation tool) for more short one - everything started to work fine. p.s. I have tryed to rename this ODBC connection multiple times and had this error when the name was very long.