Sql within a base macro using variables gives syntax error

[Ardee]

My environment:
Windows 10, LibreOffice Version: 6.4.0.3 (x64) HSQLDB Split Database

Scenario:
I have many macros from MSAccess using the DLookup function which I am converting to LO.
For efficiency and code clarity, I want to make a subroutine to do this.

Problem:
My problem is syntax, I cannot figure out how to use variables in the sql statement.

My Question:
What is the proper syntax for my sql line?

Error Details:
In the code below, I have rem’d out the different versions I tried.

Test #0 works. It is the usual syntax when used without variables in a subroutine.
The other versions are tests none of which work, giving following error messages whilst highhlighting line

sQuery = oStatement.ExecuteQuery(sSqlTask)

Test #1

BASIC runtime error. An exception occurred Type: com.sun.star.sdbc.SQLException Message: unexpected token: Coordinates.

Test #2

An exception occurred Type: com.sun.star.sdbc.SQLException Message: user lacks privilege or object not found: sSource.

Test #3

BASIC runtime error. An exception occurred Type: com.sun.star.sdbc.SQLException Message: user lacks privilege or object not found: sSource.

Test #4

BASIC runtime error. An exception occurred Type: com.sun.star.sdbc.SQLException Message: unknown token:

Here is the code

Sub TryLookUp
LookUpSql("Name ID","Coordinates","Coord ID",25)
End Sub

Sub LookUpSql (sSeekField As String,sSource As String,sWhereItem As String,sCompareItem AS String)
    Dim oStatement As Object
    Dim sQuery As Object
    Dim sSqlTask As String
    Dim sLookup As String
           If IsNull(ThisDatabaseDocument.CurrentController.ActiveConnection) Then
          ThisDatabaseDocument.CurrentController.connect
	       End If
    ' #0 sSqlTask = "SELECT distinct ""Name ID"" FROM ""Coordinates"" WHERE ""Coord ID""  = '" & sCompareItem & "'"
    ' #1 sSqlTask = "SELECT distinct '" & sSeekField & "' FROM '" & sSource & "' WHERE '" & sWhereItem & "'  = '" & sCompareItem & "'"
    ' #2 sSqlTask = "SELECT distinct sSeekField FROM sSource WHERE sWhereItem  = '" & sCompareItem & "'"
    ' #3 sSqlTask = "SELECT distinct ""sSeekField"" FROM ""sSource"" WHERE ""sWhereItem""  = '" & sCompareItem & "'"
    ' #4 sSqlTask = "SELECT distinct & 'sSeekField' FROM & 'sSource' & WHERE & 'sWhereItem' &  = '" & sCompareItem & "'"
    oStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.createStatement()
    sQuery = oStatement.ExecuteQuery(sSqlTask)
    sQuery.Next()  
    sLookup = sQuery.GetString(1)
    MsgBox ("Result= " & sLookUp)
End Sub

Thank you for assistance.

EDIT: Solved

For anyone interested here is a working example version of the code

Sub TryLookUp
LookUpSql("Name","Employees","PersonID",25)
End Sub

Sub LookUpSql (sSeekField As String,sSource As String,sWhereItem As String,sCompareItem AS String)
    Dim oStatement As Object
    Dim sQuery As Object
    Dim sSqlTask As String
    Dim sLookup As String
       If IsNull(ThisDatabaseDocument.CurrentController.ActiveConnection) Then
       ThisDatabaseDocument.CurrentController.connect
       End If
    sSqlTask = "SELECT distinct """ & sSeekField & """ FROM """ & sSource & """ WHERE """ & sWhereItem & """  = '" & sCompareItem & "'"
    oStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.createStatement()
    sQuery = oStatement.ExecuteQuery(sSqlTask)
    sQuery.Next()  
    sLookup = sQuery.GetString(1)
    MsgBox ("Result= " & sLookUp)
End Sub

[Ratslinger]

Hello,

When using anything except uppercase for your database the field and table names must be surrounded with quotes:

sSqlTask = "SELECT distinct """ & sSeekField & """ FROM """ & sSource & """ WHERE """ & sWhereItem & """  = '" & sCompareItem & "'"

Did not test this actual line but did get match to your Test #0 which you stated worked.

Edit:

Here is a print of your line:

Here is a print of the line posted above:

They are identical. What do you get when you do the same?

Do not see how yours are the same. Or there is something different in the code you are using from what is posted. In fact this is not even a valid line and will cause an error:

MsgBox (Result= " & sLookUp)

So there must be something different.

[Ardee]

@Ratslinger, thank you very much.
I tried it but unfortunately did not work.
I got following error
BASIC runtime error. An exception occurred Type: com.sun.star.sdbc.SQLException Message: user lacks privilege or object not found: 'Coordinates'.

EDIT:
It would be easy enough to make the variable names uppercase but I suppose that won’t help.

[Ratslinger]

If that did not work then neither did your Test #0 statement. “Coordinates” is the table name you provided. The error is stating there is no name as provided.

Also, it is not the variable names being uppercase but rather the table and field names being all uppercase.

[Ardee]

@Ratslinger, I assure you Test #0 works, I just retested to make sure I didn’t make a copy/paste error to the forum.

I’m still early enough in my overhaul that I should probably consider changing all table and field names to uppercase, (and while I’m at it, get rid of spaces in the names).

[Ardee]

Yes sorry for the MsgBox typo, I edited that for the forum version.
I’m using a split database but I have an embedded one as demo on my first problem you helped me with, I am putting the code in there for testing with other data to make sure. I will revert. Thank you.

[Ardee]

@Ratslinger I tested in another odb with another table, and your code works perfectly.
I discover I made a minor test change between posting here and trying you answer, which still worked for test#0 but not the others with variables including yours. Having corrected that, you solution works. Sorry about that.
Thank you very much, very pleased to have this issue solved.

[Ratslinger]

Please stop marking questions as [Solved].

See → How to use the Ask site for further information.

[Ratslinger]

Thank You!

[Ardee]

learning, getting there…….