BASIC+Firebird: Create table using SQL statement

[lonk]

The new table does not exist, but the database has been changed and needs to be saved. And View>Refresh Tables menu does not work. What should be added more ?

Sub CreateTableFruit
	Dim sFileName$, sPath$, dbf$, sSQL$
	Dim db, oStatement, oRowSet	As Object
	sPath	=	Environ("HOME") & "/" & "Documents/LibreOfficeAskLO/0023_CreateATableInFirebirdViaSQL" & GetPathSeparator()
	sFileName 	= 	"firebird1.odb"
	On Local Error GoTo CloseConnection
	dbf			= "file://" & sPath & sFileName
	db 			= ConnectDatabase(dbf)
	oStatement	= db.createStatement()
	sSQL 		= "CREATE TABLE " & """TableFruit""" & " (" & """FruitCode""" & " VARCHAR(5) " & "PRIMARY KEY" & ", " & """FruitName""" & " VARCHAR(255)" & ")"
	oStatement.executeQuery(sSQL)
'	db.getTables().refresh()
	RefreshTables(dbf, db)
	DisconnectDatabase(db)
	MsgBox "Finished"
	Exit Sub
CloseConnection:
	MsgBox "Error " & Err & ": " & Error$ & " (line : " & Erl & ")"
	DisconnectDatabase(db)
End Sub

Function ConnectDatabase(dbFilename$) As Object
	Dim dbContext 	As Object 	: dbContext 	= createUNOService("com.sun.star.sdb.DatabaseContext")
	Dim oDataSource As Object 	: oDataSource 	= dbContext.GetByName(dbFilename)
	ConnectDatabase = oDataSource.GetConnection("","")'>>("Username","Password")
End Function

Sub DisconnectDatabase(db)
	db.Close
	db.Dispose()
End Sub

Sub RefreshTables(sURL$, oCon)
	Dim oDoc   'Document to refresh.
	Dim oDisp  'Dispatch helper.
	Dim oFrame 'Current frame.
	oDoc = FindComponentWithURL(sURL, False)
	If NOT IsNULL(oDOC) AND NOT IsEmpty(oDoc) Then
		oDisp 	= createUnoService("com.sun.star.frame.DispatchHelper")
		oFrame 	= oDoc.getCurrentController().getFrame()
		oDisp.executeDispatch(oFrame,".uno:DBRefreshTables", "", 0, Array())
	End If
End Sub

This image, as above, is from using db.getTables().refresh

And RefreshTables(dbf, db) gives an error message, Error35: Sub-procedure or function procedure not defined.(line 15).

0023_CreateATableInFirebirdViaSQL.ods

firebird1.odb

[Ratslinger]

Hello,

With db.getTables().refresh() you are simply getting the connection refreshed with table access. Has nothing to do with the Base display.

With:

RefreshTables(dbf, db)

you are getting errors because you did not copy all routines from the Pitonyak document. You are missing:

Function FindComponentWithURL(sName$, bLoadIfNotFound As Boolean)

and

Function GetDocURL(oDoc) As String

As for the database being saved (and update when Base is not open) this post → BASIC|Calc connects Base(Firebird): Why must table of database be opened for adding new records? and the related bug report → tdf#128607 give the missing line:

db.getParent().flush

That can go into this Sub:

Sub DisconnectDatabase(db)
    db.getParent().flush
    db.Close
    db.Dispose()
End Sub

With those changes, all appears to work whether the Base file is open or not.

[lonk]

Dear @Ratslinger,

Thank you so much.

Andrew concerned and mentioned in his AndrewBase.odt that it,(db.getTables().refresh), sometimes worked when adding a table.

That is in OpenOffice, and is it stable in LibreOffice now ?

[Ratslinger]

@lonk,

My small amount of tests did not show that statement doing anything in regard to the question. Did not look at it too closely. Felt it wasn’t needed to answer this question.

In general, did not experience any problem adding the table or refreshing as per the code presented.

Edit:

In going back to the document & reading that section, do not recall seeng the results stated. Seemingly the dispatch command in RefreshTables(dbf, db) was the replacement.