How to getRowSet using simple form of SQL?

I usually wrote SQL like this:
sSQL = "SELECT " & """ID""" & ", " & """FirstName""" & ", " & """LastName""" & ", " & """DateOfBirth""" & ", " & """Salary""" & " FROM " & """Table1""" & " WHERE " & """ID""" & " = " & "'1'"
But for the very long SQL, it is very difficult to write like that.

Is it possible to write:
"SELECT ID, FirstName, LastName, DateOfBirth, Salary FROM Table1 WHERE ID = 1"
and how to getRowSet in Sub Test2 ?

Sub Test2

	Dim sSQL, dbf 				As String
	Dim db, oStatement 			As Object
	Dim lesQueries, descrQuery 	As Object

	On Local Error GoTo CloseConnection
	dbf			= "file:///home/id00003/Documents/LibreOffice/LibreOfficeAskLibreOffice/0033SimpleFormSQL/0033SimpleFormSQL.odb"
	db 			= ConnectDatabase(dbf)
	oStatement 	= db.CreateStatement
	MsgBox "Connected"	

	lesQueries = db.QueryDefinitions
	descrQuery = lesQueries.createInstance
	descrQuery.Command = "SELECT ID, FirstName, LastName, DateOfBirth, Salary FROM Table1 WHERE ID = 1"
'How to getRowSet in Sub Test2 ?
	MsgBox "Found"

	DisconnectDatabase(db)
	MsgBox "Disconnected"	
	Exit Sub

CloseConnection:
	MsgBox "Error " & Err & ": " & Error$ & " (line : " & Erl & ")"
	DisconnectDatabase(db)

End Sub

LibreOffice:
Version: 7.3.6.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.6-0ubuntu0.22.04.1
Calc: threaded
Base: Firebird

OS:
Ubuntu 22.04 LTS

0033SimpleFormSQL.odb (3.0 KB)
0033SimpleFormSQL.ods (13.4 KB)

Try in direct SQL (Tools → SQL):
SELECT "FirstName" FROM "Table1"
works well, while
SELECT FirstName FROM Table1
won’t work, because it will be interpreted by Firebird with uppercased fieldname and tablename:+
SELECT FIRSTNAME FROM TABLE1
So the field an the table couldn’t be found.

could be written as

sSQL = "SELECT ""ID"", ""FirstName"", ""LastName"", ""DateOfBirth"", ""Salary"" 
FROM ""Table1"" WHERE ""ID"" = 1"

and in your special case, because you are asking for the whole table:

sSQL = "SELECT * FROM ""Table1"" WHERE ""ID"" = 1"

Get a result of a query:

oConnection = oDatasource.ActiveConnection()
oSQL_Command = oConnection.createStatement()
oResult = oSQL_Command.executeQuery(sSQL)
WHILE oResult.next
inID = oResult.getInt(1)
stFirstName = oResullt.getString(2)
…
WEND

WHILE - WEND isn’t needed in you special case, because it is only one row. But there will be errors if there isn’t any row.

1 Like

@lonk,
once again you do not supply vital relevant detail.
I was wondering why the spreadsheet? you don’t mention the custom menu ‘LO Calc BASIC Test’.
RobertG has answered the question ‘How to getRowSet using simple form of SQL?’.
.
you wish to execute the query:
SELECT ID, "FirstName", "LastName", "DateOfBirth", "Salary" FROM "Table1" WHERE ID = 1
.
this query returns a single record WHERE ID = 1.
you need to tell how and from where the value of ID is obtained, it will obviously vary and impact the coding if indeed coding is actually required.
it’s unlikely that you will be executing via the spreadsheet unless of course the value of ID is taken from that spreadsheet.
you may use a parameter query(unsuitable for macro), filter table, manually insert into macro via the IDE or several other possible sources.
the source will determine initial coding.
.
you can paste this code into any module, it should function with the database open or closed.
the database is accessed only to retrieve table data.
it should be executed from the IDE.
destination was not indicated so is via msgbox, the formatting is not great due to proportional font.
hopefully this helps in finding a solution.

Sub ExecutSelectStatement
	'execute basic SQL select statement and show result set in msqbox
	'iID = the ID of the record you wish to select
	on error goto ErrorHandler
	
	dim oContext as object, oDB as object, oCon as object, oStatement as object
	dim oResult as object, oColumn as object
	dim sSQL as string, sTemp as string, sLabel as string
	dim iID as integer, i as integer

	iID = 1 'the ID of the record you wish to select
	
	oContext = createunoservice("com.sun.star.sdb.DatabaseContext")
	oDB = oContext.getbyname(converttourl("home/id00003/Documents/LibreOffice/LibreOfficeAskLibreOffice/0033SimpleFormSQL/0033SimpleFormSQL.odb"))
	oCon = oDB.getconnection("","")
	oStatement = oCon.createstatement

	sSQL = "select ID, ""FirstName"", ""LastName"", ""DateOfBirth"", ""Salary"" from ""Table1"" where ID = " & iID

	oResult = oStatement.executequery(sSQL)
	for i = 0 to oResult.columns.count -1
		oColumn = oResult.columns.getbyindex(i)
		sLabel = Pad(oColumn.name, 14)
		sTemp = sTemp & sLabel
	next
	sTemp = sTemp & chr$(13)
	
	while oResult.next
		sTemp = sTemp & Pad(oResult.getint(1), 14)
		sTemp = sTemp & Pad(oResult.getstring(2), 14)
		sTemp = sTemp & Pad(oResult.getstring(3), 14)
		sTemp = sTemp & Pad(oResult.getstring(4), 14)
		sTemp = sTemp & Pad(oResult.getdouble(5), 14)
	wend

	msgbox sTemp,, "Query Results"
	oCon.close
	exit sub

	ErrorHandler: 
		MsgBox "Error " & Err & ": " & Error$ & " (line : " & Erl & ")", 16, "Setup: macro code error"
End Sub

Function Pad(sString as string, iWidth as integer) as string
	dim sPadChar as string
	dim iLength as integer, i as integer
	sPadChar = " "
	iLength = len(sString)
	if iLength < iWidth then
		for i = 1 to iWidth - iLength
			sString = sString & sPadChar
		next
	else
		sString = left(sString, iWidth)
	end if
	Pad = sString
End Function
1 Like