09-25-2020 06:28 AM - last edited on 05-10-2023 06:06 AM by JackLacava
4/13/2020
I want to create a SQL Select Statement but I need to check whether the table in BI Blend exists first.
09-25-2020 06:57 AM
Dim sqlQuery As New Text.StringBuilder
sqlQuery.Append("Select Count(*) ")
sqlQuery.Append("From INFORMATION_SCHEMA.TABLES " )
sqlQuery.Append("Where 1=1 " )
sqlQuery.Append("AND TABLE_SCHEMA = 'dbo' " )
sqlQuery.Append("AND TABLE_NAME = 'YOUR TABLE NAME'")
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Connect To App DB, Execute SQL, obtain a DataTable With all the records returned By the SQL
' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim ds As Datatable
Using dbConn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
ds = BRApi.Database.ExecuteSql(dbConn, sqlQuery.ToString, True)
End Using
If ds.Rows.Count > 1 Then
brapi.ErrorLog.LogMessage(si, "Exist")
Else
brapi.ErrorLog.LogMessage(si, "Does not exists")
End If
sql.Append("And TRIM('M' FROM RIGHT(TABLE_NAME, 2)) <= " & monthNo & " ")
03-02-2022 06:54 PM
this should work also!
If (args.FunctionName.XFEqualsIgnoreCase("GetBiBlendTableName"))Then
Dim tableName As String = SharedStringConstants.Unknown
'Get the BI Blend Table Name and throw and error if the table does not exist.
Using dbConnApp As DbConnInfoApp = BRApi.Database.CreateApplicationDbConnInfo(si)
Dim biBlendInfo As StageBiBlendInfo = StageBiBlendInfoCrud.GetInfo(dbConnApp, False, si.WorkflowClusterPk)
tableName = biBlendInfo.BlendTableName
If tableName.XFEqualsIgnoreCase(SharedStringConstants.Unknown) Then
Throw New XFException(si, Nothing, Nothing, "BI-Blend Table Does NOT exist for current Workflow.")
End If
End Using
Return TableName