Ask Your Question

Using Basic macro functions in SQL statements?

asked 2017-06-04 22:12:37 +0100

EasyTrieve gravatar image

updated 2017-06-17 20:14:03 +0100

(I guess it can't be done in LO, at least not yet. ... As @Ratslinger has hinted, you can mess with the queries under a report or the like, but this is not really using functions in queries, and also as @peterwt has reminded us you can use SQL functions, but these are not basic functions, and so you can't edit and debug them like you can with basic.)

Say you have a BASIC macro function called sLastFirst(first,last) that you want to use to format names as "Last, First".

Function sLastFirst (sFirst As String, sLast As String) As String
      sLastFirst = sLast & ", " & sFirst
End Function

Is there any way you can use it in SQL like this:

SELECT sLastFirst(`First`,`Last) As Name From Names;

Is there any way to make this work, or do I instead have to rewrite it into a SQL function?


To help others further understand the need for what I am asking for, here are a bunch of functions that I used in Access to help w/ my SQL. This is very hard I think to do with stored procedures, although it might be faster if you can ever get it to work.

    '=== NAMES ===============================================================
' --- Just first part of name
Function FirstNameString(First As Variant, NickName As Variant, Middle As Variant)
FirstNameString = Trim(Nz([First])) & _
IIf(Nz([Middle]) <> "", " " & Trim(Nz([Middle])), "") & _
IIf(Nz([NickName]) <> "", " " & Chr(34) & Trim(Nz([NickName])) & Chr(34), "")
End Function

' --- Just first part of name; Return First or Nick; Give priority to First
Function CommonFirstNameString(First As Variant, NickName As Variant, Middle As Variant) As String
If Nz(First) = "" Then
    If Nz(NickName) = "" Then
    CommonFirstNameString = Trim(Nz(Middle))
    CommonFirstNameString = Trim(Nz([NickName]))
    End If
    CommonFirstNameString = Trim(Nz([First]))
End If
' Broken - returns nick when both exist
'  If Nz(NickName) = "" Then
'    CommonFirstNameString = Trim(Nz([First]))
'  Else
'    CommonFirstNameString = Trim(Nz([NickName]))
'  End If
End Function

' --- Full name
'     Pack together name components with appropriate spaces and projects for nickname
'     note: the call for this must make sure that the parameters aren't null.
'           the easiest way to do this is to add '& ""' to each parameter to
'           force it to be a non-null string.   - hwj
Function FormattedName(First As Variant, NickName As Variant, Middle As Variant, Last As Variant)
FormattedName = FirstNameString(Nz([First]), Nz([NickName]), Nz([Middle])) & " " & Trim(Nz([Last]))
End Function

' --- Full name, but with only middle initial
Function ShortFormattedName(First As Variant, NickName As Variant, Middle As Variant, Last As Variant)
ShortFormattedName = FirstNameString(Nz([First]), Nz([NickName]), Left$(Nz([Middle]), 1)) & " " & Trim(Nz([Last]))
End Function

' --- "Last, First" style name
Function LastFirstName(First As Variant, NickName As Variant, Middle As Variant, Last As Variant)
LastFirstName = Trim(Nz([Last])) & ", " & FirstNameString(First, NickName, Middle)
End Function

    '=== PHONES ===============================================================
' --- Print phone if not private
Function PublicPhone(Pub As Variant, Phone As Variant)
If (Nz([Pub]) = "") Then
    PublicPhone = IIf(Nz([Phone]) = "", "___-____", "(Permission?)")
    If ...
edit retag flag offensive close merge delete


Even if you could (which you can't), why would you do something in two steps when it can be done in one?

Ratslinger gravatar imageRatslinger ( 2017-06-04 23:30:56 +0100 )edit

To reuse code. This is a simple example, but I have a bunch of much more extensive functions to deal with names and address and other data structures, and format them correctly in a variety of ways and dealing with any nulls.

EasyTrieve gravatar imageEasyTrieve ( 2017-06-05 05:47:53 +0100 )edit

...And to make it easy to prompt for user input and then use it for record selection, not just as simple text, but via things like pull down lists of dates based on the available dates, to select from. And this by setting SQL criteria via fields in a dialog form.

Now I know I can still do this with a macro which builds the sql and executes it, but that is much harder than allowing it inside a query in the GUI.

EasyTrieve gravatar imageEasyTrieve ( 2017-06-05 23:58:44 +0100 )edit

@Ratslinger, ".. If you want to set up SQL in GUI for user prompts, do it with parameters. Then just call it in a macro, fill in the parameters from whatever prompts you have and execute it however you want." But I want to select record IDs (integers) to filter the query, and those are not so easily typed into a parameter box, as for example 4672 is sort of obscure when I really mean 'xyz' company?

EasyTrieve gravatar imageEasyTrieve ( 2017-06-07 18:43:09 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-06-05 12:57:32 +0100

peterwt gravatar image

updated 2017-06-05 12:58:20 +0100

You can use stored procedures/functions for the database engine that you are using. For HSQLDB you could use:-

SELECT IFNULL("First",'')||','||IFNULL("Last",'') AS "Name" FROM "Names"

which will cater for nulls.
You may be able to create your own stored procedures/functions depending on the database engine.

edit flag offensive delete link more


Thanks, but I was already aware of stored procedures, "or do I instead have to rewrite it into a SQL function? " Sorry, I probably should have used the words, "stored procedures". FWIW, and the reason I asked this Q is because I find stored procedures hard to debug, hard to use, and Basic is much easier. Have been spoiled on Access where I can use my VBA as SQL functions. Hope LO can do this someday.

EasyTrieve gravatar imageEasyTrieve ( 2017-06-05 23:53:21 +0100 )edit

answered 2017-06-07 22:21:43 +0100

Ratslinger gravatar image

@EasyTrieve This answer is only in response to the comments regarding the use of Queries with parameters.

You mention it is easier to set up in GUI. OK, set this up with parameters then call from basic and fill in the parameters. This doesn't mean the user is prompted for the input. It allows you to create & test the statement in the GUI and then (sometimes you have to alter your thinking) you fill in the parameters through code.

Given this SQL statement saved as "MyQuery":


This code:

SUB QueryContent
    DIM oDatabaseFile AS OBJECT
    DIM oQuery AS OBJECT
    DIM stQuery AS STRING
    oDatabaseFile = ThisComponent.Parent.CurrentController.DataSource
    oQuery = oDatabaseFile.getQueryDefinitions()
    stQuery = oQuery.getByName("MyQuery").Command
    stQuery = Replace(stQuery, ":my_id","?")
    if IsNull(Thisdatabasedocument.CurrentController.ActiveConnection) then
    oConnection = Thisdatabasedocument.CurrentController.ActiveConnection
    oStmt = oConnection.prepareCall(stQuery)
    oStmt.setInt(1, "125")
    oResult = oStmt.executeQuery()
    MsgBox oResult.getInt(1)

Will return the record with ID = 125. With this you can use the GUI and not have to use the "harder" macro method.

Note: In the future (and you have done this multiple times) you should not quote something from a deleted comment or answer from anyone. It usually was deleted for a purpose. In this case I wanted time to verify my comment and have something to present.

edit flag offensive delete link more


Interesting. Thanks. I also found that I could write to .Command.

EasyTrieve gravatar imageEasyTrieve ( 2017-06-17 19:14:40 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-06-04 22:12:37 +0100

Seen: 976 times

Last updated: Jun 17 '17