Ask Your Question
0

Sql within a base macro using variables gives syntax error

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

Ardee gravatar image

updated 2020-04-01 02:36:32 +0200

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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2020-04-01 00:28:06 +0200

Ratslinger gravatar image

updated 2020-04-01 01:37:09 +0200

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:

image description

Here is a print of the line posted above:

image description

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.

edit flag offensive delete link 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.

Ardee gravatar imageArdee ( 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.

Ratslinger gravatar imageRatslinger ( 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).

Ardee gravatar imageArdee ( 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.

Ardee gravatar imageArdee ( 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.

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

Please stop marking questions as [Solved].

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

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

Thank You!

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

learning, getting there...….

Ardee gravatar imageArdee ( 2020-04-01 02:48:35 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 39 times

Last updated: Apr 01