Will database instance close when out of scope?

Databases
1

For a web application, database connection should be made when SQL activity is required and close it when it is done. So I plan to make a function under WebSession:

LoadTable(Loc_Sql as string) as RecordSet

  dim LocDB as new mySQLDatabase //MySQLCommunityServer
  LocDB.Host = app.HostName
  LocDB.Port = app.Port
  LocDB.UserName = app.UserName
  LocDB.Password = app.Password
  if Not LocDB.Connect Then
    MsgBox "Connect Error: " + LocDB.ErrorMessage
  else
    LocDB.SQLExecute("set names utf8 collate utf8_general_ci")
    LocDB.SQLExecute("use " + app.UnicornDatabaseName)
    
    dim rs as RecordSet
    rs = LocDB.SQLSelect(Loc_Sql)
    if rs = nil then
      MsgBox "SQL Error: " + LocDB.ErrorMessage
    end if
    
    return rs
  end if
End Select

Question: will LocDB close by itself after the rs is returned? I tried to add LocDB.close before “return rs”, the calling module receives an empty rs.

2

rs is declared in the if bloack and does not exist outside of it, so return rs ring an error bell.

3

No, the function works fine by now, only if I added LocDB.close right before “return rs” causes rs = nil

4

Hi Tony:

You are right, the trouble does not comes from the return rs line.

the code you share miss some line:

End Select

End something it does not have open. aka: you do not have any Select Case line shared.

LoadTable(Loc_Sql as string) as RecordSet

Probably miss Dim

Loc_Sql is not declared before used.

OK, I stop here: nearly evey line have an error when I try to compile it (Xojo 2017r1.1).

Please, share correct code so maybe someone can help.

Nota: to really help, some have to put the code somewhere (either create a brand new project and add plenty useless files that belong to this project to the boot disk or add code to a current project…).

5

Sorry, I have copied the End Select by mistake… Loc_Sql is a parameter passed into the method.

6

Actually the code works fine in my project, just want to know if I do not add LocDB.close before return will cause memory leakage or not.

7

I do not see any mem leaks and i work a lot with mysql and mssql here.

8

This is just me: when a .Close exists, I use it (even if the documentation says this is optional). I checked: the docs says nothing.

9

DB.Close will destroy the recordset, at least with mysql. If you don’t close the database, then after the database object goes out of scope it will live until the recordset also goes out of scope, at which point both will be cleaned up.