How to add a column in a SQL expression using a user defined function through VB net (odbc connescti-CodePudding

I am new to VB.net and I would like to convert and display UnixStamp data in a new column.

Variant DatGridView = datasource is Dataset. I can create empty columns within an SQL query (DataGridView_Dataset), unfortunately I can't use direct data conversion into new columns using my own function. Error see.SQL Error Code The function works independently. see. Working UnixStampFunction

I got the result of 56,000 sentences in 7 seconds, without getting date and time values from UnixTimeStamp

Is there a solution for using udf in SQL Statement?

DataGridView variant - odbcExecuteRead Solution

Using the given code is not a problem to display eg 10 sentences (10 sentences result), but if the records are more than about 100 (around 50 thousand by month), an error like this will be displayed (Managed Debug Helper ContextSwitchDeadlock: The module CLR could not go out of context COM 0xb45680 to context 0xb455c8 for 60 seconds.).

Unchecking the ContextSwitchDeadlock option by Debug > Windows > Exception Settings in VS 2019 I got the result of 56 000 record in awfull 228 seconds.

Is it possible to optimize the code or is it possible to use another solution?

Code:

Public Class Form1
Public strDateTime, strDate, strTime As String
Public x As Integer =0
Public y As Integer =0

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Try

        Dim dgvDT As New DataGridView
        Dim odbcConn As OdbcConnection
        Dim odbcComm As OdbcCommand
        Dim odbcAdapter As New Odbc.OdbcDataAdapter
        Dim odbcDataset As New DataSet
        Dim odbcDataTable As DataTable
        Dim strConn, strSQL, strSQL1 As String


        dgvDT.Location = New Point(382, 2)
        dgvDT.Width = 360
        dgvDT.Height = 600

        Me.Controls.Add(dgvDT)
        strConn = "Driver=Firebird/InterBase(r) driver;User=;Password=;DataSource=...." ' 
        odbcConn = New OdbcConnection(strConn)
        odbcConn.Open()

        strSQL = "SELECT TEST.UNIXTIMESTAMP, " & "'dd.mm.yyyy" & "'" & "AS Date_ , " & "'hh:mm:ss" & "'" & "AS Time_ " _
                    & "From TEST " _
                    & "Where TEST.UNIXTIMESTAMP > 1646092800 " _ '1.3.2022
                    & "Order By TEST.ID "

        strSQL1 = "SELECT TEST.UNIXTIMESTAMP, UnixTimestampToDateOrTime(TEST.UNIXTIMESTAMP,1) As Date_, " & "'hh:mm:ss" & "'" & "AS Time_ " _
                    & "From TEST " _
                    & "Where TEST.UNIXTIMESTAMP > 1646092800 " _  '1.3.2022
                    & "Order By TEST.ID "

        odbcComm = New OdbcCommand(strSQL, odbcConn)
        'odbcComm = New OdbcCommand(strSQL1, odbcConn)

        odbcAdapter.SelectCommand() = odbcComm
        odbcAdapter.Fill(odbcDataset, "TEST")


        odbcDataTable = odbcDataset.Tables("TEST")
        dgvDT.DataSource = odbcDataTable
        dgvDT.Columns(0).HeaderText = "UnixTimeStamp"
        dgvDT.Columns(1).HeaderText = "Date"
        dgvDT.Columns(2).HeaderText = "Time"
        dgvDT.Visible = True

    Catch ex As Exception

        MessageBox.Show("Error:  " & ex.Message, "Error")

    End Try

End Sub

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click

    Try
        Dim dgvDT1 As New DataGridView
        Dim odbcConn1 As OdbcConnection
        Dim odbcComm1 As OdbcCommand
        Dim odbcDR As OdbcDataReader
        Dim x As Integer = 0
        Dim y As Integer = 0
        Dim strConn1, strSQL, strSQL2 As String


        dgvDT1.Location = New Point(382, 2)
        dgvDT1.Width = 360
        dgvDT1.Height = 600


        For i As Integer = 0 To 2
            Dim dgvNC As New DataGridViewTextBoxColumn
            dgvNC.Name = "Column" & i.ToString
            dgvDT1.Columns.Add(dgvNC)
        Next

        dgvDT1.Columns(0).HeaderText = "UnixTimeStamp"
        dgvDT1.Columns(1).HeaderText = "Date"
        dgvDT1.Columns(2).HeaderText = "Time"
        dgvDT1.ReadOnly = True
        dgvDT1.AllowUserToAddRows = False
        dgvDT1.AllowUserToDeleteRows = False


        strSQL2 = "SELECT TEST.UNIXTIMESTAMP " _
                    & "From TEST " _
                    & "Where TEST.UNIXTIMESTAMP > 1646092800 " _
                    & "Order By TEST.ID "

        strConn1 = "Driver=Firebird/InterBase(r) driver;User=;Password=;DataSource="

        odbcConn1 = New OdbcConnection(strConn1)
        odbcConn1.Open()

        odbcComm1 = New OdbcCommand(strSQL2, odbcConn1)
        odbcDR = odbcComm1.ExecuteReader()


        While (odbcDR.Read()) 'And y <= 10
            dgvDT1.Rows.Add()
            dgvDT1.Rows(y).Cells("Column0").Value = (odbcDR.GetValue(0).ToString())
            dgvDT1.Rows(y).Cells("Column1").Value = (UnixTimestampToDateOrTime(odbcDR.GetValue(0), 1))
            dgvDT1.Rows(y).Cells("Column2").Value = (UnixTimestampToDateOrTime(odbcDR.GetValue(0), 2))

            y = y   1

        End While

        Me.Controls.Add(dgvDT1)
        dgvDT1.Visible = True

    Catch ex As Exception
        MessageBox.Show("Error:  " & ex.Message, "Error")
    End Try
End Sub

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    MsgBox(UnixTimestampToDateOrTime(1646092800, 1) & vbNewLine & UnixTimestampToDateOrTime(1646092800, 2))

End Sub

Public Function UnixTimestampToDateOrTime(ByVal _UnixTimeStamp As Long, ByRef _Parameter As Integer) As String

    strDateTime = New DateTime(1970, 1, 1, 0, 0, 0).AddSeconds(_UnixTimeStamp).ToString()
    strDate = strDateTime.Substring(0, strDateTime.IndexOf(" "))
    strTime = strDateTime.Substring(strDateTime.IndexOf(" "), strDateTime.Length - strDateTime.IndexOf(" "))

    If _Parameter = 1 Then
        Return (strDate)
    Else
        Return (strTime)
    End If

End Function

End Class

CodePudding user response：

You write:

I would like to convert and display UnixStamp data in a new column.

Yes you can. But i do not believe that would make your applicatio nany faster.

However I would outline the strategy i personally would had used getting data conversion without UDF.

Removing UDF, i believe, would enhance your database in general. But would not fix performance problems. Due to amount of information i could not format it as mere comment.

So, about removing UDF from the query.

Link to Firebird 2.5 documentation: https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref25-psql-triggers

Link to Firebird 3 documentation: https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref30/firebird-30-language-reference.html

The key difference would be that FB3 has "stored functions" and even DETERMINISTIC ones, and FB2 - only "stored procedures".

I would avoid using UDFs here: they are declared obsolete, they make deployment more complex, and they would reduce your flexibiltiy (for example, you can not use Firebird for Linux/ARM if you only have UDF DLL for Win64)

So, the approach i speculate, you can use.

You would have to find an algorythm, how to "parse" UNIX date/time into separate values for day, month, etc.
You would have to implement that algorythm in Procedural SQL stored function (in FB3, make it DETERMINISTIC) or stored procedure (FB2, make it selectable by having SUSPEND command, after you set value for the output parameter and before exit) - see the Ch. 7 of the manual.

For assembling values of DATE or TIME or DATETIME types you would have to use integer to string (VARCHAR) to resulting type coersion, see Ch. 3.8 of the manual.

Example:

select
  cast( 2010 || '-' || 2 || '-' || 11  AS DATE ),
  cast(   23 || ':' || 2 || ':' || 11  AS TIME )
from rdb$database

CAST       | CAST    
:--------- | :-------
2010-02-11 | 23:02:11

db<>fiddle here

You would have add the converted columns to your table. Those columns qwould be read-only, but they would change "unix time" to Firebird native date and/or type.
Read Ch. 5.4.2. of the manual, about ALTER TABLE <name> ADD <column> command.
Read 5.4.1. CREATE TABLE of the manual about Calculated fields ( columns, declared using COMPUTED BY ( <expression> ) instead of actual datatype. Those columns you would have to creat, and here is where difference between FB3 and FB2 kicks in.
in FB3 i believe you would be able to directly use your PSQL Function as the expression.
in FB2 i believe you would have to use a rather specific trampoline, to coerce a stored procedure into expression:

ALTER TABLE {tablename}
ADD {columnname} COMPUTED BY
(
  (
     SELECT {sp_output_param_name} FROM {stored_proc_name}( {table_unixtime_column} )
  )
)

In you Visual Basic application you would read those read-only converted columns instead of original value columns

CodePudding user response：

Now, this would address the perfomance problem. Again, writing this aas an answer, because it is too large to fit as a comment.

No, while UDF has a number of problems - those are not about performance, but about being "old-school" and prone to low-level problems, such as memory leaks. UDFs can trigger problems if used in other places in query, by prohibiting SQL optimizer to use fast, indexed codepaths, but it is not your case. You only use your UDF in the result columns list of SELECT - and here should be no prerformance troubles.

Your application/database therefore should have other bottlenecks.

first of all, you use Where TEST.UNIXTIMESTAMP > 1646092800 and Order By TEST.ID in your query. The obvious question is if your table does have index on those columns. If not, you force the database server to do full natural scan to apply where condition, and then use external, temporary file sorting. THAT can be really slow, and can scale badly as the table grows.

Use your database design tool of choice to check query plan of your select. Does Firebird use indexed or non indexed access paths.

There are articles online how to read Firebird's query. I don't instantly know English language ones though. Also, it would be specific to your database design tool, how to get it.

Sorry, there is no silver bullet. That is where learning about databases is required.

Read Data Definition Language / Create Index chapter of the documentation.

Link to Firebird 2.5 documentation: https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref25/firebird-25-language-reference.html#fblangref25-psql-triggers

Link to Firebird 3 documentation: https://www.firebirdsql.org/file/documentation/html/en/refdocs/fblangref30/firebird-30-language-reference.html

Check your database structure, if UNIXTIMESTAMP and ID have index on them or not. In general, indices speed some (not all) reading queries and slow down (slightly) all writing queries.

You may decide you want to add those indices if they do not exist yet.

Again, it would be dependent upon your database design tool, how to check for existing of the indexes. It also would depend on your data and your applications which kind of indexes is needed or not. That is not what someone else can decide for you.

i also have a lot of suspicion about odbcAdapter.Fill(odbcDataset, "TEST") command. Basically, you try to read all the data in one go. And you do it via ODBC connection, that is not natural for C#.

Usually desktop application only read first 100 or so rows. People would rarely actually read anything after first page or two. Humans are not machines.

Try to somehow connect your visual grid to the select query without reading ALL the table. There should be way.

Additionally, there is free Firebird .Net Provider - this should work natively with VB.Net and should be your first choice, not ODBC.

There also is commercial IBProvider, based on native OLE DB technology it should be worse choice than .Net Provider, but it can work too and has some code examples for VB.Net, and i suppose it is still better mantained than ODBC driver.

you may also change Firebird configuration and allow it using more RAM for cache. This may somewhat relax problems of index-less selecting and sorting. But only somewhat relax and offset, not solve. You can find articles about it on www.ib-aid.com