We will be migrating from Ask to Discourse on the first week of August, read the details here

 Ask Your Question

Sql within a base macro using variables gives syntax error

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

edit retag close merge delete

1 Answer

Sort by » oldest newest most voted

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.

more

Comments

@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.

( 2020-04-01 01:04:23 +0200 )edit

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.

( 2020-04-01 01:10:26 +0200 )edit

@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).

( 2020-04-01 01:23:21 +0200 )edit

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.

( 2020-04-01 01:55:55 +0200 )edit

@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.

( 2020-04-01 02:14:19 +0200 )edit

Please stop marking questions as [Solved].

See -> How to use the Ask site for further information.

( 2020-04-01 02:24:38 +0200 )edit

Thank You!

( 2020-04-01 02:30:50 +0200 )edit

learning, getting there...….

( 2020-04-01 02:48:35 +0200 )edit

Stats

Asked: 2020-03-31 23:49:43 +0200

Seen: 205 times

Last updated: Apr 01 '20