Looping through an array in an embedded procedure in firebird

[ztminhas]

Good Morning,

I would appreciate some assistance for looping through a psql array:

Sub Main
Dim strSQL As String
	strSQL = "execute block (name varchar(50)[5000] = ?)  as " & _
"declare LOCNAME_ID int = 0; " & _
"Begin " & _
REM LOOP THROUGH EACH ITEM OF THE NAME ARRRAY TO MAKE THE FOLLOWING INSERT:
"	INSERT INTO TABLE1(NAME) VALUES(:name); " & _
"End"
	con = getDbConnection(ThisDatabaseDocument)
	set fp =con.prepareStatement(strSQL)
	fp.setArray(
1,
REM HOW SHOULD I SETUP THIS ARRAY
)
	fp.executeUpdate()
	fp.Close
	fp =Nothing
End Sub

[ztminhas]

Hello

I made some changes with new code:

Option Explicit
public locCon As object
Sub Main1
Dim stmt as Object 
Dim strSQL As String
Dim Vals as Variant
	strSQL = "execute block (name varchar(5)[25] = ?) as declare I INTEGER; Begin I= 0; WHILE (I<26) DO INSERT INTO TABLE1(NAME) VALUES(:name[:I]); I=I+1; End End"
	Vals = Array("A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z")
	openLocal("reportage")
	set stmt =locCon.prepareStatement(strSQL)
	stmt.setBlob(1,Vals)
	stmt.executeUpdate()
	stmt.Close
	stmt =Nothing
End Sub

Private Sub openLocal(context$)
	getFBConnection(context)
End Sub

Private Sub getFBConnection(context$)
Dim DatabaseContext As Object
	DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
	DataSource = DatabaseContext.getByName(context)
	locCon= DataSource.getConnection("","")
End Sub

So when it reaches the line:

	stmt.setArray(1,Vals)

I get the error message:

BASIC runtime error.
An exception occurred 
Type: com.sun.star.sdbc.SQLException
Message: firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Token unknown - line 1, column 31
*[
caused by
'isc_dsql_prepare'

I would appreciate any help offered.

Thank you

[RobertG]

Seems you try something with arrays, which doesn’t work in Firebird.
Documentation Firebird: Array Note the last sentence in this documentation:

If the features described are enough for your tasks, you might consider using arrays in your projects. Currently, no improvements are planned to enhance support for arrays in Firebird.

I have tried to get any data from an array with Firebird. Got the same error through Tools → SQL: Token [ is unknown.

[ztminhas]

Thank you very much. For the problem I am working on I need arrays. Time to find a different portable database.

[ztminhas]

Hello,

Since this is a problem which I need to resolve (I must use arrays, my code must be fast), and since postgres is out, I am back to embedded FireBird. Here is something one might consider as a workaround:

Option Explicit
public locCon As object
Sub main2
Dim x As Long, idx As Long, lim As Integer
Dim sql$
	openLocal("reportage")
	executeDDLLocal("RECREATE TABLE NUMBERLIST (NUMVAL NUMERIC(9,3) NOT NULL, CT TIMESTAMP DEFAULT 'NOW')")
	sql = "INSERT INTO NUMBERLIST(NUMVAL) GETVALS"
	idx = 999999
	lim = 256
	Dim daArray(0 to lim-1) As Long
	For x = 0 To idx-1   
		If x Mod lim = 0 Then
			If x<>0 Then
				sql  =join(daArray,");INSERT INTO NUMBERLIST(NUMVAL) VALUES(")
				sql = "INSERT INTO NUMBERLIST(NUMVAL) VALUES(" & sql & ");"
				sql = "EXECUTE BLOCK AS BEGIN " & sql & " END"
				executeDDLLocal(sql)
				sql=""
				If idx-x >= lim-1 Then
					ReDim daArray(0 to lim-1) As Long
				else
					ReDim daArray(0 to idx-x-1) As Long
				End If
				daArray(x Mod lim)=x+1
			Else
				daArray(x Mod lim)=x+1
			End If
		Else
			daArray(x Mod lim)=x+1
		End If
	Next x
	sql  =join(daArray,");INSERT INTO NUMBERLIST(NUMVAL) VALUES(")
	sql = "INSERT INTO NUMBERLIST(NUMVAL) VALUES(" & sql & ");"
	sql = "EXECUTE BLOCK AS BEGIN " & sql & " END"
	executeDDLLocal(sql)
End Sub

Private Sub openLocal(context$)
	getFBConnection(context)
End Sub

Private Sub getFBConnection(context$)
Dim DatabaseContext As Object
	DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
	DataSource = DatabaseContext.getByName(context)
	locCon= DataSource.getConnection("","")
End Sub

public Sub executeDDLLocal(ddl As String)
	executeDDL(ddl,locCon)
End Sub

Private Sub executeDDL(ddl As String,con As Object)
Dim locStmt As Object
	locStmt= con.createStatement()
	locStmt.execute(ddl)
	closeDBObject(locStmt)
End sub

private Sub closeDBObject(dbObject As Object)
	dbObject.close()
	dbObject = Nothing
End Sub

testing this code, I consistently end up inserting about 23 thousand rows per second from my office workstation. On my personal laptop, i end up inserting between 8100 to ten thousand rows per second. Fast enough for me.

[ztminhas]

another variation

Sub main2
Dim x As Long, idx As Long, lim As Integer
Dim sql$
Dim sqlList As New Collection
	openLocal("reportage")
	executeDDLLocal("RECREATE TABLE NUMBERLIST (NUMVAL NUMERIC(9,3) NOT NULL, CT TIMESTAMP DEFAULT 'NOW')")
	sql = "INSERT INTO NUMBERLIST(NUMVAL) GETVALS"
	idx = 999999
	lim = 256
	Dim daArray(0 to lim-1) As Long
	For x = 0 To idx-1   
		If x Mod lim = 0 Then
			If x<>0 Then
				sql  =join(daArray,");INSERT INTO NUMBERLIST(NUMVAL) VALUES(")
				sql = "INSERT INTO NUMBERLIST(NUMVAL) VALUES(" & sql & ");"
				sql = "EXECUTE BLOCK AS BEGIN " & sql & " END"
				sqlList.add sql
				sql=""
				If idx-x >= lim-1 Then
					ReDim daArray(0 to lim-1) As Long
				else
					ReDim daArray(0 to idx-x-1) As Long
				End If
				daArray(x Mod lim)=x+1
			Else
				daArray(x Mod lim)=x+1
			End If
		Else
			daArray(x Mod lim)=x+1
		End If
	Next x
	sql  =join(daArray,");INSERT INTO NUMBERLIST(NUMVAL) VALUES(")
	sql = "INSERT INTO NUMBERLIST(NUMVAL) VALUES(" & sql & ");"
	sql = "EXECUTE BLOCK AS BEGIN " & sql & " END"
	sqlList.add sql
	For Each sql In sqlList
		executeDDLLocal(sql)
	Next sql
	ThisDatabaseDocument.store()
End Sub