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
>
