> all is perfectly fine when the SQL statement has only one UDF in it
Just a correction on this. Have now also seen problems when there is only
one UDF in the SQL statement.
It is not entirely predictable, so sometimes get crash and on other
occasions I don't.
RBS
On Fri, Dec 25, 2015 at 12:31 PM, Bart Smissaert <bart.smissaert at gmail.com>
wrote:
> Thanks for clearing that up.
> I know all this has very little to do with SQLite, but people using SQLite
> with VB6 or VBA might be interested in this.
> What puzzles me that all is perfectly fine when the SQL statement has only
> one UDF in it, but there is a serious problem
> when there are two in that same statement.
>
> I made a simple procedure that finds string x in string y and clears that
> string y, starting at the point where string x occurs:
>
> Sub ClearStartAtFixedStringX(ByVal lPtr_ObjContext As Long, _
> ByVal lArgCount As Long, _
> ByVal lPtr_ObjSQLite3_Value As Long)
> Dim i As Long
> Dim lPtr1 As Long
> Dim lPtr2 As Long
> Dim lPos As Long
> Dim lBytes1 As Long
> Dim arrBytes1() As Byte
> 10 On Error GoTo ERROROUT
> 'field value to alter
> '--------------------
> 20 lPtr1 = MemLong(lPtr_ObjSQLite3_Value)
> 30 LogData strLogFile, vbCrLf & "ClearStartAtFixedStringX line 20,
> lPtr1: " & lPtr1
> 40 lBytes1 = sqlite3_value_bytes(lPtr1)
> 50 If lBytes1 = 0 Then
> 60 sqlite3_result_null lPtr_ObjContext
> 70 Exit Sub
> 80 End If
> 90 LogData strLogFile, "ClearStartAtFixedStringX line 90, lBytes1: "
> & lBytes1
> 100 lPtr2 = sqlite3_value_text(lPtr1)
> 110 LogData strLogFile, "ClearStartAtFixedStringX line 110, lPtr2: " &
> lPtr2
> 120 ReDim arrBytes1(lBytes1 - 1) As Byte
> 'just CopyMemory API might be faster here
> 130 For i = 0 To lBytes1 - 1
> 140 arrBytes1(i) = MemByte(lPtr2 + i)
> 150 Next i
> 'this shows that for normal ANSI characters we have one byte
> 'per character here for the SQLite UTF-8 string
> '-----------------------------------------------------------
> 160 LogData strLogFile, "ClearStartAtFixedStringX line 160,
> BytesAsString(arrBytes1): " & _
> BytesAsString(arrBytes1)
> 170 If bDoneArrBytes2 = False Then
> 'this will fill up arrBytes2 and set lBytes2
> 'as the string to find is fixed we only need to do this once
> '-----------------------------------------------------------
> 180 On Error GoTo 0
> 190 MakeArrBytes2 lPtr_ObjSQLite3_Value + 4
> 200 On Error GoTo ERROROUT
> 210 End If
> 220 On Error GoTo 0
> 230 lPos = GetStringPosB(arrBytes1, arrBytes2, False)
> 240 On Error GoTo ERROROUT
> 250 LogData strLogFile, "ClearStartAtFixedStringX line 250, lPos: " &
> lPos
> 'string not found, so return original field string
> '-------------------------------------------------
> 260 If lPos = 0 Then
> 270 sqlite3_result_value lPtr_ObjContext, lPtr1
> 280 Exit Sub
> 290 End If
> 'lPos -1 because if string found at byte position 2 then we only
> want one byte
>
> '-----------------------------------------------------------------------------
> 300 LogData strLogFile, "ClearStartAtFixedStringX line 250,
> VarPtr(arrBytes1(0)): " & _
> VarPtr(arrBytes1(0))
> 310 sqlite3_result_text lPtr_ObjContext, VarPtr(arrBytes1(0)), lPos -
> 1, SQLITE_TRANSIENT
> 320 LogData strLogFile, "ClearStartAtFixedStringX line 310 (last line
> in UDF), error message: " & _
>
> cSQL.PointerToString(sqlite3_errmsg(lDBHandle)) & vbCrLf
> 330 Exit Sub
> ERROROUT:
> 340 cMsgBoxClass.MsgBoxDLL oExcel, _
> Err.Description, _
> "ClearStartAtFixedStringX error at line " &
> Erl, _
> lFormColour:=ColourMainForm,
> lButtonColour:=ColourButtons, _
>
> lActiveButtonColour:=ColourMsgBoxActiveButton
> End Sub
>
> I am testing this on a table with a text field holding this string:
> a(((bcdefghijklmnopqrstuvwyz
>
> This string data comes from Excel, so they are Unicode strings, 2 bytes
> per character.
> They are stored in SQLite as UTF-8.
>
> Now if I run this SQL:
> SELECT
> ClearStartAtFixedStringX(FIELD1,'(((') as x1
> FROM
> CLEAR_FROM
>
> Then all is perfectly fine, so it will produce the string: a
>
> But when I run instead this SQL:
> SELECT
> ClearStartAtFixedStringX(FIELD1,'(((') as x1,
> ClearStartAtFixedStringX(FIELD1,'(((') as x2
> FROM
> CLEAR_FROM
>
> I get a crash, producing the following message in Excel:
>
> Problem Event Name: APPCRASH
> Application Name: EXCEL.EXE
> Application Version: 12.0.6739.5000
> Application Timestamp: 5643f6e7
> Fault Module Name: sqlite3.dll
> Fault Module Version: 3.9.2.0
> Fault Module Timestamp: 5637af4b
> Exception Code: c0000005
> Exception Offset: 0005a319
> OS Version: 6.1.7601.2.1.0.256.48
> Locale ID: 2057
>
> This crash happens directly after the second run of the above callback
> procedure, although that procedure
> runs to full completion with no errors.
>
> My own debug output (LogData strLogFile etc.) up to that point is all fine:
>
> ClearStartAtFixedStringX line 20, lPtr1: 33418304
> ClearStartAtFixedStringX line 90, lBytes1: 28
> ClearStartAtFixedStringX line 110, lPtr2: 33539976
> ClearStartAtFixedStringX line 160, BytesAsString(arrBytes1):
> 97|40|40|40|98|99|100|101|102|103|104|105|106|107|108|109|110|111|112|113|114|115|116|117|118|119|121|122
> MakeArrBytes2, lBytes2: 3
> MakeArrBytes2, BytesAsString(arrBytes2): 40|40|40
> GetStringPosB line 120, UB1: 27
> GetStringPosB line 120, UB2: 2
> ClearStartAtFixedStringX line 250, lPos: 2
> ClearStartAtFixedStringX line 250, VarPtr(arrBytes1(0)): 126878440
> PointerToString, line 150 lBytes: 12
> ClearStartAtFixedStringX line 310 (last line in UDF), error message: not
> an error
>
> ClearStartAtFixedStringX line 20, lPtr1: 33418384
> ClearStartAtFixedStringX line 90, lBytes1: 28
> ClearStartAtFixedStringX line 110, lPtr2: 33541640
> ClearStartAtFixedStringX line 160, BytesAsString(arrBytes1):
> 97|40|40|40|98|99|100|101|102|103|104|105|106|107|108|109|110|111|112|113|114|115|116|117|118|119|121|122
> GetStringPosB line 120, UB1: 27
> GetStringPosB line 120, UB2: 2
> ClearStartAtFixedStringX line 250, lPos: 2
> ClearStartAtFixedStringX line 250, VarPtr(arrBytes1(0)): 126878440
> PointerToString, line 150 lBytes: 12
> ClearStartAtFixedStringX line 310 (last line in UDF), error message: not
> an error
>
>
> So, what goes wrong in sqlite3.dll after this second run of this UDF?
> What does sqlite3.dll actually do, directly running after the UDF?
> It should be the next sqlite3_step, but that doesn't appear in my debug
> log, so something goes
> wrong before that.
>
> Again, I will drop all this if it is deemed to be inappropriate as it has
> little to do with SQLite.
>
>
> Happy Xmas, RBS
>
>
>
>
>
>
>
> On Fri, Dec 25, 2015 at 12:29 AM, Richard Hipp <drh at sqlite.org> wrote:
>
>> On 12/24/15, Bart Smissaert <bart.smissaert at gmail.com> wrote:
>> > I have no problems at all with sqlite3_bind, but I understand that the
>> > sqlite3_result group of procedures is very similar to sqlite3_bind
>> group.
>> >
>> > In a UDF procedure (callback procedure in the ActiveX dll) I do things
>> like
>> > this:
>> >
>> > sqlite3_result_text lPtr_ObjContext, VarPtr(arrBytes1(0)), lPos - 1,
>> > SQLITE_TRANSIENT
>> >
>> > where arrBytes1 is a local variable, a Byte array.
>> > This goes out of scope once the callback procedure finishes.
>> > Could that be a problem?
>> >
>>
>> No. SQLITE_TRANSIENT causes SQLite to make its own private copy of the
>> string.
>> --
>> D. Richard Hipp
>> drh at sqlite.org
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
