How do I make a rowset parameter Optional

Databases
1

I have this function I want to make rs = rowset optional so I can use this for SelectSQL and ExecuteSql?

    Public Function DoSQL(ByRef rs As RowSet, lsSql As String, lsMsg As String = "", ParamArray params() As Variant) as Boolean
  If lsSql.left(6) = "Select" Then
    
    Try
      rs = gDB.SelectSQL(lsSql, params)
    Catch err As DatabaseException
      If err.ErrorNumber = 2006 Or err.ErrorNumber = 2013 Then    ' server has gone away or connection lost
        Call OpenDB    ' reconnect to the database
        Try
          rs = gDB.SelectSQL(lsSql, params)
        Catch err2 As DatabaseException
          Call MessageDialog.Show(lsMsg + " Database error " + err2.Message)
          Return False
        End Try
      Else
        
        Call MessageDialog.Show(lsMsg + " Database error " + err.Message)
        Return False
      End
    End
    
    If rs = Nil Then
      Call MessageDialog.Show(lsMsg + " Database error: Nil Rowset")
      Return False
    End
    
  else
    Try
      gDB.ExecuteSQL(lsSql, params)
    Catch err As DatabaseException
      If err.ErrorNumber = 2006 Or err.ErrorNumber = 2013 Then    ' server has gone away or connection lost
        Call OpenDB    ' reconnect to the database
        Try
          gDB.ExecuteSQL(lsSql, params)
        Catch err2 As DatabaseException
          Call MessageDialog.Show(lsMsg + " Database error " + err2.Message)
          Return False
        End Try
      Else
        
        Call MessageDialog.Show(lsMsg + " Database error " + err.Message)
        Return False
      End
    End
  
  end
  Return True
  
End Function
2

Overload the function.

Public Function DoSQL(lsSql As String, lsMsg As String = "", ParamArray params() As Variant) as Boolean
  var rs as RowSet
  return DoSQL(rs, lsSql, lsMsg, params)
End Function

Public Function DoSQL(ByRef rs As RowSet, lsSql As String, lsMsg As String = "", ParamArray params() As Variant) as Boolean
 
  if params.Count = 1 and params(0).IsArray then
    params = params(0)
  end if

  // rest of your function
End Function

Or something like that.

3

Wow, I’ confused, So I have 2 functions and is there’s a rowset it skips the one without?

Man I’ve been doing this for over 20 years and still learning.

Thank you!

4

Yes, you can have multiple functions with the same name, they just have to take distinct, different parameters (its “signature”). Xojo will match up the one you mean based on that signature. In this example, if your first parameter is a string, it will choose the first one, and if a RowSet, the second one.

You can also have one function return a value and another with the same name that does not, which is a way to create Getter and Setter functions.

5

Now I get a runtime error on:

gDB.ExecuteSQL(lsSql, params)

Parameters cannot be arrays

6

Right, that’s why I included that additional bit of code to turn the supplied array into something ExecuteSQL expects. See my example code above.

7

I missed that!

Thank you so much it’s working as expected!

It’s truly appreciate, thanks again! :smiley:

1 Like
8

My pleasure.

BTW, that bit of code is good anytime you define a function that takes a ParamArray of Variant. It gives you the flexibility to call the function with each parameter listed individually, or as an array. (Assuming that an array is never a legal parameter in the first place.)

MyFunction(param1, param2, ...)
// or
MyFunction(myParamArray)
9

the only drawback of this is that you can’t gave 2 functions with the same names, same parameters and that return different values types. the compiler doesn’t allow that.

10

Right. If that’s the requirement, best to use different names.

11

Btw, I avoid overloading in all cases because the editor doesn’t show all the possibilities at the bottom of the window.

12

It would be noce if those where all shown maybe a feature request?

13

Already out there.

<https://xojo.com/issue/57796>

1 Like