Multiple parameter sets in when executing prepared statement

English
, , , ,
#1

Hello,

As we know, preparedstatements in Java are allowed to add parameters in batches and then the whole batch can be executed, like this:

PreparedStatement statement =
        connection.prepareStatement("update emp set name = ? where id = ?");
    statement.setString(1, "FOO");
    statement.setInt(2, 1);
    statement.addBatch();
    statement.setString(1, "BAR");
    statement.setInt(2, 2);
    statement.addBatch();
    statement.executeBatch();

Is there something similar in Base / Basic that we can take advantage of?

Thank you for your assistance.

#2

Hello,

Yes this can be done in basic. Reference:

PreparedStatement Service Reference
XPreparedBatchExecution Interface

Have just tested using the HSQLDB embedded database with this code:

Sub preStmt
    if IsNull(Thisdatabasedocument.CurrentController.ActiveConnection) then
       Thisdatabasedocument.CurrentController.connect
    endif
    oStatement = Thisdatabasedocument.CurrentController.ActiveConnection.createStatement()
    oConnection = thisComponent.Drawpage.Forms(0).activeConnection()
    stSql = "update EXTERNALDOCUMENTS set LOCATION = ? where id = ?"
    oStmt = oConnection.prepareStatement(stSql)
    oStmt.setString(1, "here2")
    oStmt.setInt(2, 1)
    oStmt.addBatch()
    oStmt.setString(1, "there2")
    oStmt.setInt(2, 3)
    oStmt.addBatch()
    oResult = oStmt.executeBatch()
End sub
1 Like
#3

Thank you. This is what I was looking for.

#5

I could use a little bit of extra asistance. I am using an insert statement, but I keep getting function sequence error. Before I paste code, could you please provide some reason why this error might occur?

#6

Have no idea. My test (nearly same as posted code) worked first time. You need to post what you are doing.

#8

Hello,

Here is what i have now. This is the destination table which I am using to analyze the data. This table is embedded FireBird :

CREATE TABLE WB_CORP
(
ANALYSIS_DATE DATE  DEFAULT CURRENT_TIMESTAMP NOT NULL,
ANALYSIS_TIME TIME  DEFAULT CURRENT_TIMESTAMP NOT NULL,
W_ID         INTEGER NOT NULL,
W_LOC_ID       VARCHAR(4) NOT NULL,
W_NUM            INTEGER NOT NULL,
BORE_NUM           NUMERIC(2,0) NOT NULL,
BORE_NAME VARCHAR(20) NOT NULL,
FULL_W_NAME              VARCHAR(19) ,
FULL_UWI           VARCHAR(11) NOT NULL,
W_STA_DT            DATE,
UTM_ZN                 NUMERIC(2,0),
UTM_X              NUMERIC(8,2),
UTM_Y              NUMERIC(9,2),
CORD_QLTY                 VARCHAR(13),
W_ELEV                NUMERIC(6,2),
W_TD           INTEGER,
HAS_DPN                VARCHAR(3),
PRIMARY KEY (ANALYSIS_DATE,FULL_UWI),
UNIQUE (ANALYSIS_DATE,W_ID, BORE_NAME),
UNIQUE (ANALYSIS_DATE,W_ID, BORE_NUM),
UNIQUE (ANALYSIS_DATE,W_ID, FULL_W_NAME)
);

now the source data is coming from oracle. So I have two connections (code in Basic):


Private Sub putFbEmbeddedFromOracle()
  insSQL = "INSERT INTO WELLBORES_EPPR(W_ID,W_LOC_ID, W_NUM,BORE_NUM, BORE_NAME, FULL_W_NAME, FULL_UWI, W_STA_DT, UTM_ZN, UTM_X, UTM_Y, CORD_QLTY, W_ELEV, W_TD, HAS_DPN) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
  
  selSQL = "W_ID,W_LOC_ID, W_NUM,BORE_NUM, BORE_NAME, FULL_W_NAME, FULL_UWI, W_STA_DT, UTM_ZN, UTM_X, UTM_Y, CORD_QLTY, W_ELEV, W_TD, HAS_DPN FROM ORA_TAB"
  getOraData(insSQL, selSQL)
End Sub

public function getOraData( insSQL As String, selSQL As String)  As boolean
Dim fstmt as com.sun.star.sdbc.XPreparedStatement
Dim retVal As Boolean
	set rslt= openReadOnlyResultSet(selSQL, ocon)	
	Set fstmt = fcon.prepareStatement(insSQL)
	retVal =populateEpToFir(rslt,fstmt)
	closeAllButConnections()
	getOraData=retVal

End function

private Function populateEpToFir(oResult as com.sun.star.sdbc.XResultSet, firStmt As com.sun.star.sdbc.XPreparedBatchExecution ) As Boolean

Dim cTypeName As String, cName As String
Dim cScale As Integer,cPrecision As Integer,i As Integer
Dim oRsMetaData As com.sun.star.sdbc.XResultSetMetaData
Dim fRsMetaData As Object
Dim cVal As Variant
Dim cycle As Integer
Dim execBatchAt As  Integer

On Error GoTo print_error
	execBatchAt=20
	GlobalScope.BasicLibraries.LoadLibrary("Tools")
	set oRsMetaData = oResult.getMetaData()
	
	while oResult.next()
	cycle = 1+ cycle Mod execBatchAt
	for i = 1 to oRsMetaData.getColumnCount()
			cTypeName = oRsMetaData.getColumnTypeName(i)
			cName = oRsMetaData.getColumnName(i)
			cPrecision = oRsMetaData.getPrecision(i)
			cScale =   oRsMetaData.getScale(i)
			Select Case cTypeName
				Case "NUMERIC","DECIMAL","NUMBER"
					Select Case cPrecision
						Case Is <=38
							Select Case cScale
								Case is <=0 
									cVal = oResult.getInt(i)
									firStmt.setDouble(i,cVal)
									GoTo next_item									
								Case Is >0 
									cVal = oResult.getDouble(i)
									firStmt.setDouble(i,cVal)
								End Select 
						Case 126
							select Case cScale
								Case -127
									cVal = oResult.getFloat(i)
									firStmt.setFloat(i,cVal)
								Case else
									cVal = oResult.getDouble(i)
									firStmt.setDouble(i,cVal)
							End Select
						Case Else
							Select Case cScale
								Case -127
									cVal = oResult.getDouble(i)
									firStmt.setDouble(i,cVal)
								Case else
									cVal = oResult.getDouble(i)
									firStmt.setDouble(i,cVal)
								End select
					End Select 
					GoTo next_item
				Case  "BIGINT", "LONG"
					cVal = oResult.getLong(i)
					firStmt.setDouble(i, cVal)	
					GoTo next_item					
				Case "DOUBLE"
					cVal = oResult.getDouble(i)
					firStmt.setDouble(i, cVal)	
					GoTo next_item					
				Case "INTEGER", "SMALLINT"
					cVal = oResult.getInt(i)
					firStmt.setInt(i, cVal)	
					GoTo next_item					
				Case "FLOAT"
					cVal = oResult.getFloat(i)
					firStmt.setFload(i, cVal)	
					GoTo next_item					
				Case "BOOLEAN"
					cVal = oResult.getBoolean(i)
					firStmt.setBoolean(i, cVal)	
					GoTo next_item					
				Case "TIME"
					cVal = timeToSerial(oResult.getTime(i))
					firStmt.setTime(i, cVal)	
					GoTo next_item					
				Case "TIMESTAMP"
					cVal =timeStampToSerial( oResult.getTimeStamp(i))
					firStmt.setTimeStamp(i, cVal)						
					GoTo next_item					
				Case "DATE"
				cVal = oResult.getDate(i)
				if(validDate(cVal)) Then 
					firStmt.setDate(i, cVal)
				Else
					cVal = dateToSerial(cVal)
					firStmt.setNull(i,0)
				End if
				GoTo next_item					
				Case "VARCHAR","VARCHAR2","NCHAR", "CHAR"
					cVal = oResult.getString(i)
					firStmt.setString(i,cVal)
					GoTo next_item					
				Case "SERIAL"
					cVal = oResult.getInt(i)
					firStmt.setInt(i, cVal)
					GoTo next_item					
			End Select
next_item:
		next i
		firStmt.addBatch()
		If cycle = execBatchAt then
			firStmt.executeBatch()
			firStmt.clearParameters()			
		End if
Wend
	firStmt.executeBatch()
	firStmt.clearParameters()			
	populateEpToFir= true	
Exit function
print_error:
	Tools.WriteDbgString( "Error " & Err & ": " & Error$ + chr(13) + "At line : " + Erl + chr(13) + Now & ":::::" & cName &"," & cTypeName  &"," & cPrecision  &"," & cScale  & "," & cVal)
	populateEpToFir= false				
End function

My error occurs when i am calling firStmt.addBatch() with the error message “function sequence error”

#9

All my testing was done with HSQLDB embedded and works without issue. Converted same to Firebird embedded (still in experimental stage) and can duplicate the error.
.
Only remedy other than changing to HSQLDB embedded is to report as Bug > Bugzilla
.
It is my fault for not asking but please always post DB used and LO version and related with question. It will save everyone time and effort.

1 Like
#10

I have decided to use HSQLDB as a short term work around. I am researching my code to see what other way I can accomplish this in FireBird.

#11

I seem to recall prepared statements being a problem in Firebird also. This may be part of the issue and possibly the only method to be used in Firebird is non batch statements with variables possibly in a loop to complete you task.
.
I would hesitate to use HSQLDB embedded for anything but testing. Preference would be HSQLDB split DB with a more current DB version.

1 Like
#12

Thank you for your assistance. I am very thankful to have helpful folks like you on here providing me support as I slowly learn the intricacies of LibreOffice

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.