Code from Calc to do Index Design on Base

[lonk]

Option Explicit
Dim db, oStatement, oResult As Variant
Dim sSQL As String
Function ConnectDatabase(dbFilename$) As Object
'	Dr. Mark Alexander Bain
	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.getParent().flush
	db.Close
	db.Dispose()
End Sub
'Database in my computer is at :  file:///home/id00003/Documents/NewDatabase.odb
Sub Test
	CreateAscendingOrDescendingIndexTo1Field("file:///home/id00003/Documents/NewDatabase.odb", "ProductSetRequisition3", "ProductSetRequisitionNumber3", "Ascending")
End Sub
Sub CreateAscendingOrDescendingIndexTo1Field(pFirebirdDatabaseName$, pTableName$, pFieldName$, pAscendingOrDescending$)

	On Local Error GoTo CloseConn
	db = ConnectDatabase(pFirebirdDatabaseName)
	oStatement 	= db.CreateStatement
	Dim sIndexName$ : sIndexName =  "Index" & Right(pFieldName, 1)
	
	If 		UCase(Mid(pAscendingOrDescending, 1, 1)) = "A" Then
			sSQL = "CREATE ASCENDING INDEX"
	ElseIf 	UCase(Mid(pAscendingOrDescending, 1, 1)) = "D" Then
			sSQL = "CREATE DESCENDING INDEX"
	EndIf
	sSQL = 	sSQL & " " 	& sIndexName & " " & _
			"ON" & " " 	& pTableName & _ 
			"(" 		& pFieldName & _
			")"
	oResult = oStatement.ExecuteQuery(sSQL)
	db.getTables().refresh
	DisconnectDatabase(db)
	Exit Sub
CloseConn:
	MsgBox "Error " & Err & ": " & Error$ & " (line : " & Erl & ")"
	DisconnectDatabase(db)
	Stop
End Sub

I manually used Tools > SQL on table ProductSetRequisition.
create ascending index "PSRNIndex" on "ProductSetRequisition"("ProductSetRequisitionNumber")
Index was created successfully.
But when I used code on table ProductSetRequisition3, I got an error message:

What is wrong with code ?

NewDatabase.ods (34.0 KB)
NewDatabase.odb (6.2 KB)

LibreOffice:
Version: 7.3.4.2 / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 4; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Ubuntu package version: 1:7.3.4-0ubuntu0.22.04.1
Calc: threaded

Database: Embedded Firebird

OS: Ubuntu 22.04 LTS

[Ratslinger]

Hello @lonk,

I guessed your problem from the start but wanted to test to be safe.

Here is the print of what you create in the macro:

Now taking this and running in menu Tools->SQL... you will get an error. The reason is that table and field do not exist. What you have is converted to upper case. If you wish to use mixed case, as that is how the table is created, you must surround with quotes.

Modifying this portion of your code:

	If 		UCase(Mid(pAscendingOrDescending, 1, 1)) = "A" Then
			sSQL = "CREATE ASCENDING INDEX """
	ElseIf 	UCase(Mid(pAscendingOrDescending, 1, 1)) = "D" Then
			sSQL = "CREATE DESCENDING INDEX """
	EndIf
	sSQL = 	sSQL & sIndexName & """ " & _
			"ON" & " """	& pTableName & """"& _ 
			"(""" 		& pFieldName & _
			""")"

the macro ran with a result in Base of: