[vb.net]SQLITE_LIMIT_ zetten

[Damic]

Mijn vraag
Ik loop tegen de standaard limiet aan van het aantal toegelaten variabelen die mogen worden gebruik in een geparametriseerde statement (deze limiet is 999), deze kun je verhogen maar dit gaat niet via de standaard procedures (of ik heb het nog niet gevonden). Op stackoverflow liep ook iemand tegen hetzelfde probleem aan maar dan in c#.

Relevante software en hardware die ik gebruik
Visual Studio 2019 Community

Wat ik al gevonden of geprobeerd heb
Info: https://sqlite.org/c3ref/c_limit_attached.html
Op stackoverflow kwam ik dit tegen: https://stackoverflow.com...-max-compound-select-in-c weliswaar voor c# maar dat kan omgezet worden naar vb.net

C#:

[DllImport("sqlite3.dll")]
static extern int sqlite3_limit(IntPtr dbHandle, int id, int newVal);
private void SetLimit(SqliteConnection conn, int newLimit)
{
   object sqlVar = conn.GetType().GetProperty("_sql", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(conn);
   IntPtr dbHandle = (IntPtr) sqlVar.GetType().GetProperty("handle", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(sqlVar);
   sqlite3_limit(dbHandle, 9, newLimit);
}

Visual Basic .NET:

Imports System.Reflection
Imports System.Runtime.InteropServices

<DllImport("sqlite3.dll")>
    Private Shared Function sqlite3_limit(ByVal dbHandle As IntPtr, ByVal id As Integer, ByVal newVal As Integer) As Integer
    End Function

Private Sub SetLimit(ByVal conn As SqliteConnection, ByVal newLimit As Integer)
    Dim sqlVar As Object = conn.[GetType]().GetProperty("_sql", BindingFlags.Instance Or BindingFlags.NonPublic).GetValue(conn)
    Dim dbHandle As IntPtr = CType(sqlVar.[GetType]().GetProperty("handle", BindingFlags.Instance Or BindingFlags.NonPublic).GetValue(sqlVar), IntPtr)
    sqlite3_limit(dbHandle, 9, newLimit)
End Sub

Als ik setlimit() aanroep en de eerste lijn uitvoe, krijg ik een System.NullReferenceException

Visual Basic .NET:

    Public Function Replace(Tablename As String, Columns As String, data As Dictionary(Of Integer, List(Of String))) As Integer
        Replace = -1
        If data.Count < 1 Then Exit Function
        Try
            Using cmd As New SQLiteCommand("REPLACE INTO " & Tablename & " (" & Columns & ") VALUES @vars;", New SQLiteConnection(dbConnection))
                PrepareValues(Columns, data, cmd)
                cmd.Connection.OpenAsync()
                'cmd.Connection.Open()
                Using trans As SQLiteTransaction = cmd.Connection.BeginTransaction
                    Replace = cmd.ExecuteNonQuery()
                    trans.Commit()
                End Using
                If bdebug Then SqlString(cmd, Replace)
                cmd.Parameters.Clear()
                cmd.Connection.Close()
            End Using

        Catch e As Exception
            Throw New Exception(e.Message)
        End Try
    End Function

    ''' <summary>
    ''' prepare the data values to get parametrized in the sqlitecommand
    ''' </summary>
    ''' <param name="Columns">Column names between '' and seperated by comma</param>
    ''' <param name="Data">the data</param>
    ''' <param name="Command">SQLitecommand</param>
    Private Sub PrepareValues(ByRef Columns As String, ByRef Data As Dictionary(Of Integer, List(Of String)), ByRef Command As SQLiteCommand)
        Try
            Dim values As String = "" ', key As String = ""
            Dim s() = Columns.Split(New Char() {","c})
            Dim counter As Integer = 0
            For Each val As KeyValuePair(Of Integer, List(Of String)) In Data
                If s.Count = val.Value.Count Then
                    values &= "("
                    For i = 0 To val.Value.Count - 1
                        'key = val.Key & i.ToString
                        values &= "@" & counter & ","
                        Command.Parameters.AddWithValue("@" & counter, val.Value(i))
                        counter += 1
                    Next i
                    values = values.Substring(0, values.Length - 1) & "),"
                End If
            Next
            If counter >= 999 Then SetLimit(Command.Connection, counter + 5)
            Command.CommandText = Command.CommandText.Replace("@vars", values.Substring(0, values.Length - 1))
            values = Nothing
            s = Nothing
        Catch ex As Exception
            Command.Parameters.Clear()
            MessageBox.Show(ex.Message, "sqLite: PrepareValues() error")
            Debug.WriteLine(ex.ToString)
        End Try
    End Sub

Hoe krijg ik die 0-ref weg of hoe kan ik toch zonder de limiet te veranderen de sql uitvoeren (al is het in stukken)??

[farlane]

Damic schreef op zondag 4 augustus 2019 @ 10:34:
Mijn vraag
Ik loop tegen de standaard limiet aan van het aantal toegelaten variabelen die mogen worden gebruik in een geparametriseerde statement (deze limiet is 999) ....

Ik vermoed dat je datamodel het probleem is. Waarom moet je meer dan 999 parameters kunnen passen?

[Damic]

Omdat er in de UK 657 radio streams zijn en ik die allemaal in 1 group heb staan en ik dus de groupid ook telkens moet meegeven.

edit: data komt uit json web return

Visual Basic .NET:

                    Dim dicgrpval As New Dictionary(Of Integer, List(Of String))
                    Dim dicStation As New Dictionary(Of Integer, List(Of String))
                    For i = 0 To ThisToken("stations").Count - 1
                        'do the loop
                        dicgrpval.Add(i, New List(Of String) From {GroupId, CInt(ThisToken("stations").Item(i).Item("id"))})
                        dicStation.Add(i, New List(Of String) From {ThisToken("stations").Item(i).Item("id").ToString, ThisToken("stations").Item(i).Item("name").ToString, ThisToken("stations").Item(i).Item("location").ToString, ThisToken("stations").Item(i).Item("www").ToString, ThisToken("stations").Item(i).Item("stream").ToString, CInt(ThisToken("stations").Item(i).Item("type")), ThisToken("stations").Item(i).Item("description").ToString, CInt(ThisToken("stations").Item(i).Item("state")), CInt(ThisToken("stations").Item(i).Item("lastupdate"))})
                    Next
                    dbdata.Replace("tblGroupStation", "'GroupId','StationId'", dicgrpval)
                    dbdata.Replace("tblStations", "'StationId','StationName','location', 'wUrl', 'sUrl', 'StationType', 'StationDes', 'StationState', 'StationLastUpdate'", dicStation)

[ Voor 81% gewijzigd door Damic op 04-08-2019 11:59 ]

[The Eagle]

die vars in een tussentabel wegpennen, en vervolgens met een nested select aanroepen. Dan mag de DB het afhandelen.

[MrMonkE]

Als je tegen dit soort limieten aanloopt ben je meestal diep in 'bad practice territory'

[Damic]

@The Eagle mmmh eens kijken hoe ik dat kan uitvoeren
@MrMonkE tja dat kan best zijn ik ben dan ook geen gevorderde sql kenner eerder beginner

Ondertussen niet stil gezeten en een tussen oplossing gevonden, het stuk dat ik alles in de dictionary stak gewoon checken of ik niet aan een bepaalde teller waarde zit, zoja in de db steken en dictonary legen

Visual Basic .NET:

                    For i = 0 To ThisToken("stations").Count - 1
                        'do the loop
                        dicgrpval.Add(i, New List(Of String) From {GroupId, CInt(ThisToken("stations").Item(i).Item("id"))})
                        If dicgrpval.Count >= 455 Then '999/2 columns-1
                            dbdata.Replace("tblGroupStation", "'GroupId','StationId'", dicgrpval)
                            dicgrpval.Clear()
                        End If

                        dicStation.Add(i, New List(Of String) From {ThisToken("stations").Item(i).Item("id").ToString, ThisToken("stations").Item(i).Item("name").ToString, ThisToken("stations").Item(i).Item("location").ToString, ThisToken("stations").Item(i).Item("www").ToString, ThisToken("stations").Item(i).Item("stream").ToString, CInt(ThisToken("stations").Item(i).Item("type")), ThisToken("stations").Item(i).Item("description").ToString, CInt(ThisToken("stations").Item(i).Item("state")), CInt(ThisToken("stations").Item(i).Item("lastupdate"))})
                        If dicStation.Count >= 110 Then '999/9columns-1
                            dbdata.Replace("tblStations", "'StationId','StationName','location', 'wUrl', 'sUrl', 'StationType', 'StationDes', 'StationState', 'StationLastUpdate'", dicStation)
                            dicStation.Clear()
                        End If
                    Next

[farlane]

Damic schreef op zondag 4 augustus 2019 @ 11:55:
Omdat er in de UK 657 radio streams zijn en ik die allemaal in 1 group heb staan en ik dus de groupid ook telkens moet meegeven.

edit: data komt uit json web return

Visual Basic .NET:

                    Dim dicgrpval As New Dictionary(Of Integer, List(Of String))
                    Dim dicStation As New Dictionary(Of Integer, List(Of String))
                    For i = 0 To ThisToken("stations").Count - 1
                        'do the loop
                        dicgrpval.Add(i, New List(Of String) From {GroupId, CInt(ThisToken("stations").Item(i).Item("id"))})
                        dicStation.Add(i, New List(Of String) From {ThisToken("stations").Item(i).Item("id").ToString, ThisToken("stations").Item(i).Item("name").ToString, ThisToken("stations").Item(i).Item("location").ToString, ThisToken("stations").Item(i).Item("www").ToString, ThisToken("stations").Item(i).Item("stream").ToString, CInt(ThisToken("stations").Item(i).Item("type")), ThisToken("stations").Item(i).Item("description").ToString, CInt(ThisToken("stations").Item(i).Item("state")), CInt(ThisToken("stations").Item(i).Item("lastupdate"))})
                    Next
                    dbdata.Replace("tblGroupStation", "'GroupId','StationId'", dicgrpval)
                    dbdata.Replace("tblStations", "'StationId','StationName','location', 'wUrl', 'sUrl', 'StationType', 'StationDes', 'StationState', 'StationLastUpdate'", dicStation)

Hoe ziet je datamodel er uit dan?

[Damic]

Tabel Stations (StationId,StationName,...)
Tabel Groups (GroupId,GroupName)
Tabel GroupsStations (GroupId,StationId)

[The Eagle]

Maak eens groepen op basis van het karakter van het radiostation (klassiek, pop, etc) icm het land dan. Doen ze bij RDS en DAB ook

[Damic]

Er staan al een paar aparte groepen in zoals: Christmas/Dance/Jazz/RnB/... het was gewoon op dom geluk dat ik de UK tegenkwam die problemen gaf.