Using Basic macro functions in SQL statements?

(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?

MORE EXAMPLES

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))
Else
CommonFirstNameString = Trim(Nz([NickName]))
End If
Else
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?)")
Else
If ...
edit retag 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?

( 2017-06-04 23:30:56 +0200 )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.

( 2017-06-05 05:47:53 +0200 )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.

( 2017-06-05 23:58:44 +0200 )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?

( 2017-06-07 18:43:09 +0200 )edit

Sort by » oldest newest most voted

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.

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.

( 2017-06-05 23:53:21 +0200 )edit

@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":

SELECT *  FROM MYTABLE WHERE ID = :my_id


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
Thisdatabasedocument.CurrentController.connect
endif
oConnection = Thisdatabasedocument.CurrentController.ActiveConnection
oStmt = oConnection.prepareCall(stQuery)
oStmt.setInt(1, "125")
oResult = oStmt.executeQuery()
oResult.next()
MsgBox oResult.getInt(1)
oResult.close()
oStmt.close()
END SUB


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.

more

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