Hello everyone,
I’m working on libre office basic macro who does a lot of things like inserting data in a database LibreOffice Base.
First, I created (manually) my empty database with all the tables and attributes . I set the primary keys as an integer who increments automatically
The first fonction(“DataExists”) checks if a value already exists in the table before adding it. The second insert the data in the table, and the third retrieves the Id (primary key) based on the name attribute.
The Insertion works correctly but it seems that the operation is not commit cause the last fonction retrieves an empty table. Here are the codes. Thanks for your help to understand why the insertion is not saved immediatly :
Function DataExists(myDatabase As Object, sTableName As String, sColumnName As String, sValue As String) As Boolean
Dim queryResults As Variant
' Requetes SQL
queryResults = myDatabase.GetRows("SELECT COUNT(*) FROM """ & sTableName & """ WHERE """ & sColumnName & """ = '"& sValue &"'")
CountResult = queryResults(0)(0)
MsgBox CountResult
'Verification
If Not IsEmpty(queryResults) Then
DataExists = (CountResult > 0)
Else
DataExists = False
End If
End Function
Sub InsertDataRegDepSect(tableName As String, columnName As String, ColumnValue As String)
Dim myDatabase As Object
GlobalScope.BasicLibraries.LoadLibrary(“ScriptForge”)
'Ouverture de la base des données
Set myDatabase = CreateScriptService("Database",sDatabaseURL,"",False)
' Insert into regions
If Not DataExists(myDatabase, tableName, columnName, ColumnValue) Then
myDatabase.runSql("INSERT INTO """ & tableName & """ ("""& columnName & """) VALUES ('" & ColumnValue & "')")
'myDatabase.Commit()
MsgBox "Data inserted successfully."
Else
MsgBox ColumnValue & " already exists"
End If
myDatabase.closeDatabase()
'myDoc.store()
'myDoc.close(True)
End Sub
Function GetID(tableName As String, columnName As String, value As String) As String
Dim myDatabase As Object
GlobalScope.BasicLibraries.LoadLibrary(“ScriptForge”)
' Ouverture de la base des données
Set myDatabase = CreateScriptService("Database", sDatabaseURL, "", False)
' Exécuter la requête SQL pour obtenir l'ID
Dim sql As String
If tableName <> "Region" Then
tableName = LCase(tableName)
End If
sql = "SELECT Id_" & tableName & " FROM """ & tableName & """ WHERE """ & columnName & """ = '" & value & "'"
Dim result As Variant
result = myDatabase.getRows(sql)
' Fermer la base de données
'myDatabase.closeDatabase()
If UBound(result) >= 0 Then
GetID = result(0)(0)
Else
GetID = ""
End If
End Function