SQLite.au3 Error

[Vovsla]

Sometime i got SQLite Error

How can i fix it?

[water]

Welcome to AutoIt and the forum!

Can you please post your code? That makes helping much easier.

[jchd]

Welcome to the forum Vosla.

Please realize your question is impossible to answer lest sarcasms like "Don't run that f*cking broken script!" and the like.

Post your code (using the square blue little icon, "Code") and the detailled error(s) you get. Then only, someone -possibly me-  may have a look at your actual problem.

[Vovsla]

I thought that you'll try to find error in standard SQLite library.

Script don't broken, it works. Error appears from time to time.

All code is huge. Here is function. During use this function appears error from time to time.

I can post all code, but then probably i'll have to explain how use my script. I haven't any manual for my program. Do you need all code?

expandcollapse popup

Func Button11Click()
$UserName=GUICtrlRead($List1)
If $UserName='' Then
    MsgBoxEx(64, $FormCaption, 'Не выбран пользователь.')
    Return 0
EndIf

For $Num=0 To 6
_GUICtrlHeader_SetItemFormat(_GUICtrlListView_GetHeader($ListView2), $Num, BitAND(_GUICtrlHeader_GetItemFormat(_GUICtrlListView_GetHeader($ListView2), $Num), BitNOT(BitOR($HDF_SORTDOWN, $HDF_SORTUP))))
Next

$SelectItem=_GUICtrlListView_GetItemTextArray($ListView1)
If $SelectItem[1]='' And _GUICtrlListView_GetItemCount($ListView1)=1 Then
_GUICtrlListView_SetItemSelected($ListView1, 0)
$SelectItem=_GUICtrlListView_GetItemTextArray($ListView1)
EndIf

If $SelectItem[1]='' Then
    MsgBoxEx(16, $FormCaption, 'Не выбран продукт')
    Return 0
EndIf

$Gramm=StringRegExpReplace(StringStripWS(GUICtrlRead($Input22), 3), '[^0-9]', '.')
$FoodUnit=StringRegExpReplace(StringStripWS(GUICtrlRead($Input4), 3), '[^0-9]', '.')

$UnitWeght=-1

Select
    Case $Gramm<>''
    $Unit=$Gramm
    $UnitPart=StringSplit($Unit, '.')

    Case $FoodUnit<>''
    $Unit=$FoodUnit
    $UnitPart=StringSplit($Unit, '.')
    $UnitWeght=$SelectItem[2]

    Case Else
    MsgBoxEx(16, $FormCaption, 'Должен быть указан вес продукта или количество единиц.')
    Return 0
EndSelect


Select
Case StringIsDigit($UnitPart[1])=0
MsgBoxEx(48, $FormCaption, 'Вес продукта или количество единиц должно быть указано в цифрах, десятичные указываются после точки.')
Return 0

Case $UnitPart[0]=2 And (StringIsDigit($UnitPart[1])=0 Or StringIsDigit($UnitPart[2])=0)
MsgBoxEx(48, $FormCaption, 'Вес продукта или количество единиц должно быть указано в цифрах, десятичные указываются после точки.')
Return 0

Case $UnitPart[0]>2 And StringIsDigit($UnitPart[2])=0
MsgBoxEx(48, $FormCaption, 'В поле ввода веса или единицы продукта содержится более одного десятичного разделителя.')
Return 0
EndSelect

ToolTipEx('Добавление продукта в употребленные.')
If $UnitWeght<>-1 Then $Unit=$Unit*$UnitWeght

$CaloriesUsed=Round($SelectItem[3]/100*$Unit, 2)
$ProteinUsed=Round($SelectItem[4]/100*$Unit, 2)
$FatUsed=Round($SelectItem[5]/100*$Unit, 2)
$CarbohydrateUsed=Round($SelectItem[6]/100*$Unit, 2)

$Date=DateConvert(GUICtrlRead($Date2))
$CurrentDate=$Date[2]&'/'&$Date[1]&'/'&$Date[0]

_SQLite_Exec(-1, 'BEGIN;')
_SQLite_GetTable2d (-1, "SELECT MAX (Num) FROM UsedFood WHERE UserName='"&$UserName&"' AND DATE='"&$CurrentDate&"' ;", $aResult, $iRows, $iColumns)


$Num=1
If UBound($aResult)>1 Then $Num=$aResult[1][0]+1
_SQLite_Exec (-1, "Insert into UsedFood values ('"&$UserName&"', '"&$CurrentDate&"', '"&$Num&"', '"&$SelectItem[1]&"', '"&$Unit&"', '"&$CaloriesUsed&"', '"&$ProteinUsed&"', '"&$FatUsed&"', '"&$CarbohydrateUsed&"' );" )

_SQLite_GetTable2d (-1, "SELECT SUM(CaloriesUsed), SUM(ProteinUsed), SUM(FatUsed), SUM(CarbohydrateUsed) FROM UsedFood WHERE UserName='"&$UserName&"' AND DATE='"&$CurrentDate&"' ;", $UsedArr, $iRows, $iColumns)

_SQLite_Exec(-1, "Update Users SET CaloriesUsed='"&$UsedArr[1][0]&"', ProteinUsed='"&$UsedArr[1][1]&"', FatUsed='"&$UsedArr[1][2]&"', CarbohydrateUsed='"&$UsedArr[1][3]&"' WHERE UserName='"&$UserName&"' AND DATE='"&$CurrentDate&"' ;" )

If Not _SQLite_GetTable2d (-1, "SELECT CaloriesMAX-CaloriesUsed, ProteinMAX-ProteinUsed, FatMax-FatUsed, CarbohydrateMax-CarbohydrateUsed FROM Users WHERE UserName='"&$UserName&"' AND DATE='"&$CurrentDate&"' ;", $aResult, $iRows, $iColumns)=$SQLITE_OK Then SQLMessage(_SQLite_ErrMsg(), _SQLite_ErrCode(), '3')
_SQLite_Exec(-1, 'COMMIT;')

GUICtrlSetData($Label36, Round($UsedArr[1][0], 2))
GUICtrlSetData($Label41, Round($UsedArr[1][1], 2))
GUICtrlSetData($Label42, Round($UsedArr[1][2], 2))
GUICtrlSetData($Label43, Round($UsedArr[1][3], 2))

GUICtrlSetData($Label10, Round($aResult[1][0], 2))
GUICtrlSetData($Label11, Round($aResult[1][1], 2))
GUICtrlSetData($Label12, Round($aResult[1][2], 2))
GUICtrlSetData($Label13, Round($aResult[1][3], 2))

If $aResult[1][0]<0 Then GUICtrlSetColor($Label10, 0xFF0000)
If $aResult[1][0]>=0 Then GUICtrlSetColor($Label10, 0x000000)
If $aResult[1][1]<0 Then GUICtrlSetColor($Label11, 0xFF0000)
If $aResult[1][1]>=0 Then GUICtrlSetColor($Label11, 0x000000)
If $aResult[1][2]<0 Then GUICtrlSetColor($Label12, 0xFF0000)
If $aResult[1][2]>=0 Then GUICtrlSetColor($Label12, 0x000000)
If $aResult[1][3]<0 Then GUICtrlSetColor($Label13, 0xFF0000)
If $aResult[1][3]>=0 Then GUICtrlSetColor($Label13, 0x000000)

CountMAX($SelectItem, $aResult)

_GUICtrlListView_InsertItem($ListView2, '', 0)
_GUICtrlListView_SetItemText($ListView2, 0, $Num&'|'&$SelectItem[1]&'|'&$Unit&'|'&$CaloriesUsed&'|'&$ProteinUsed&'|'&$FatUsed&'|'&$CarbohydrateUsed, -1)
_GUICtrlListView_Scroll($ListView2, 0, -9999999)

GUICtrlSetData($Input22, '')
GUICtrlSetData($Input4, '')
ToolTipEx('')
EndFunc

[jchd]

Which error? Where?

Which version do you use?

One sure thing: $UserName = "O'Connor" will make your SQL fail. Use _SQLite_FastEscape($UserName) instead.

[Vovsla]

Screenshot with error was attachment to first message.

Error message:

Line 737  (File "C:\Program Files (x86)\AutoIt3\Include\SQLite.au3"):
$aResult[$i][$j] = $aDataRow[$j]
$aResult[$i][$j] = ^ ERROR
Error: Array variable has incorrect number of subscripts or subscript dimension range exceeded.

Autoit v3.3.8.1

Error after this string

_SQLite_GetTable2d (-1, "SELECT MAX (Num) FROM UsedFood WHERE UserName='"&$UserName&"' AND DATE='"&$CurrentDate&"' ;", $aResult, $iRows, $iColumns)

Thank you for tip about _SQLite_FastEscape

[jchd]

Sorry but the attachment in the first post is an image of a single line in cyrillic which I can't read at all but certainly is not produced by anything I know of.

Anyway I can't see offhand a reason for this error occuring more or less randomly. A simple test didn't turn any occurence of such issue.

Let's try to simplify your context. Are you positive that:

  o) you don't have any asynchronous function attempting to use the SQLite UDF while the function posted has control

  o) your DB is not corrupt

  o) the DB is local (on the same computer running your application)

  o) your version of sqlite3.dll is not corrupt

  o) no other process is changing the schema while your function has control (unlikely that SQLite wouldn't catch the situation)

Can you reproduce the issue with a simpler setup?

Does the error pop up while running the following:

expandcollapse popup

#include <SQLite.au3>
#include <SQLite.DLL.au3>
#include <Date.au3>

_SQLite_Startup()
_SQLite_Open("food.db3")
_SQLite_Exec(-1,    "drop table if exists Users;" & _
                    "CREATE TABLE [Users] (" & _
                        "[EntryId] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, " & _
                        "[UserName] CHAR, " & _
                        "[Date] CHAR, " & _
                        "[Num] INTEGER);")

Local $date0 = '2013/06/01'

_SQLite_Exec(-1, "begin;")

For $i = Asc('A') To Asc('Z')
    For $j = 1 To Random(0, 10, 1)
        _SQLite_Exec(-1, "insert into users(username, date, num) " & _
                            "values ('" & Chr($i) & "', '" & _DateAdd('D', Random(0, 30, 1), $date0) & "', " & $j & ");")
    Next
Next

_SQLite_Exec(-1, "commit;")

Local $aResult, $iRows, $iCols

For $i = 1 To 10000
    _SQLite_Exec(-1, "begin;")
    _SQLite_GetTable2d(-1, "select max(num) from users " & _
                                "where username = '" & Random(Asc('A'), Asc('Z'), 1) & "' " & _
                                    "and date = " & _DateAdd('D', Random(0, 30, 1), $date0) & ";", $aResult, $iRows, $iCols)
    _SQLite_Exec(-1, "commit;")
Next

_SQLite_Close()
_SQLite_Shutdown()

If nothing gives a clue, can you post the DB somewhere and PM me a no-registration, no-addware download link (e.g. dropbox)? Anonymize names if you feel so inclined.

[Vovsla]

1) I don't have any asynchronous function.
After the appearance of error program is stopped, but from time to time after this error when i run program error appearance immediately after run program.  
2)I think not. How i can test it?
3)DB is local
4)How i can test sqlite3.dll?
5)No other process make changing

No error pop up while running your program

Edited July 8, 2013 by Vovsla

[jchd]

OK as I said I think something gets clearer (but not completely to be honest.)

Using some third-party SQLite DB manager (I strongly recommend SQLite Expert, even in free version, but there are others floating around), open your sample DB and run the following SQL statements (much faster than writing a dedicated program to investigate/experiment with a DB):

select distinct date, cast(date as text), typeof(date) from usedfood;

The resultset shown displays dates under another form. There is no native DATE type in SQLite. You probably want dates as CHAR (identical to TEXT).

But now there is a more worrisome issue. Let's leave out the date column:

SELECT rowid, MAX (Num) FROM UsedFood WHERE UserName = 'Вова';

produces one row with result = 0 while it's clear we have values displayed which are > 0

Let's dig deeper:

select distinct typeof(num) from usedfood

shows we have a mix of text, integer and real values!

select rowid, typeof(num), cast(num as text), * from usedfood where typeof(num) = 'text'

displays rows where Num is a string, and probably a TIME string, given the format used. This is certainly not what you want.

select rowid, typeof(num), cast(num as text), * from usedfood where typeof(num) = 'real'
now displays rows where Num is a real, also probably a TIME as real.

That should give you some tracks to check.

Now it remains that there is a weird behavior in this situation. I'll look deeper into this shortly and keep you informed.

EDIT: I fixed typing errors above. BUT I still can't reproduce the error you see.

The DB you posted is not corrupt. Using SQLite Expert: Database > Check, which runs the same as _SQLite_QuerySingleRow($hDB, "pragma integrity_check;", $aRow)

You can use

_SQLite_QuerySingleRow($hDB, "select max(num) from usedfood where username = 'Вова' and date = '2011/09/30';", $aResult)

simpler than getting a useless 2D array with exactly one row (due to max() aggregating the resultset.)

Can you reproduce the issue with a simple code? If not, post your actual code and I'll look at it.

Edited July 8, 2013 by jchd

[Vovsla]

I use SQLabs SQLiteManager v3.2, i just open DB with this manager and don't make any changes

String to create table "UsedFood"
_SQLite_Exec(-1, "Create Table IF NOT EXISTS UsedFood (UserName VARCHAR NOT NULL, Date DATE NOT NULL, Num INTEGER NOT NULL, FoodName VARCHAR NOT NULL, GramUsed NUMERIC NOT NULL, CaloriesUsed NUMERIC NOT NULL, ProteinUsed NUMERIC NOT NULL, FatUsed NUMERIC NOT NULL, CarbohydrateUsed NUMERIC NOT NULL);")
here Date in format DATE not like text.

When run string
SELECT rowid, MAX (Num) FROM UsedFood WHERE UserName = 'Вова';
the program allows the value = 0,

After run strings below $Num anyway >0
$Num=1
If UBound($aResult)>1 Then $Num=$aResult[1][0]+1

Mix of text, integer and real values in typeof(num)
can in make error? i use this values only like text

In (Num) column i don't use time format any more
And i don't need in old data. Actual data for me it's a today

I can't use command
_SQLite_QuerySingleRow
because it return only one string. I can use one column, but i need quantity of strings

I some modify code to prevent appear value like 1234.12 in column (Num)

Thanks a lot for help

I can post all code if you need

[jchd]

I'm afraid it will take me hard time to understand your code and spot the source of your problems.

Also I won't have much time in the next few days but if you post your code and simple guidance to reproduce the problem I believe it can be fixed easily. Make sure that the failure occurs with the DB you posted or PM me a download link to the actual DB. I promise I'm not interessed to track people thru their cyrillic name.

[Vovsla]

It was the actual DB without any changes. I'll make translation buttons, labels etc and post code

[Vovsla]

jchd, what is your OS language?

[jchd]

French. Why the question?

[Colyn1337]

Looks to me like there's a problem downloading the SQLite dll.  Unless you manually include the dll, the built in functions check the internet and download it.

Try recompiling your code with the SQLite dll already in the directory the script is executing from.  That might fix your problem.

Cheers!

Edited July 17, 2013 by Colyn1337

[jchd]

What does this have to do with _SQLite_FetchData exploding right in the middle of the code?

I'm awaiting the OP to post the actual code to look at what happens in detail.

[Vovsla]

Question about OS because i use a date control for the GUI. I get name of month from GUI and convert it to number of month. Function is below

But during writing post i find information about $DTS_SHORTDATEFORMAT

;~ -----------------------------------------------------------------------------DateConvert----------------------------------------------------------------------------
Func DateConvert($Date)
$DatePart=StringSplit($Date, ' ')
If StringLen($DatePart[1])=1 Then $DatePart[1]='0'&$DatePart[1]

Select
Case $DatePart[2]='января'
     $DatePart[2]='01'
Case $DatePart[2]='февраля'
     $DatePart[2]='02'
Case $DatePart[2]='марта'
     $DatePart[2]='03'
Case $DatePart[2]='апреля'
     $DatePart[2]='04'
Case $DatePart[2]='мая'
     $DatePart[2]='05'
Case $DatePart[2]='июня'
     $DatePart[2]='06'
Case $DatePart[2]='июля'
     $DatePart[2]='07'
Case $DatePart[2]='августа'
     $DatePart[2]='08'
Case $DatePart[2]='сентября'
     $DatePart[2]='09'
Case $DatePart[2]='октября'
     $DatePart[2]='10'
Case $DatePart[2]='ноября'
     $DatePart[2]='11'
Case $DatePart[2]='декабря'
     $DatePart[2]='12'
 EndSelect
 _ArrayDelete($DatePart, $DatePart[0])
 _ArrayDelete($DatePart, 0)

Return $DatePart
EndFunc

[jchd]

And what is the question?

Can you post an example of input and do you really need an array (I presume it is [day, month]) as output?

[Vovsla]

I haven't any question. It was answer why i need your OS language.

But I have already found how make date control for the GUI independent from OS language.

[jchd]

Vovsla

It took me some time to look at your issue but I now believe I know why you're experiencing random issues.

You do have an asynchronous SQLite-active function as AdLib which you don't always disable inside functions which are themselves using SQLite.

Adding Consolewrite markers here and there in your code, I can produce the following trace:

  > Button11Click
    > ### AutoSearch
      > ListView1ItemClick
      < ListView1ItemClick
    < ### AutoSearch
  < Button11Click
 

So if ListView1ItemClick runs right in the middle of some SQLite operation initiated by the outer function (here Button11Click) you can expect all kind of problems, error 21 being the least. The problem is due to your use of the same connection to the DB for backgroud and foreground operations.

To avoid this situation, you can:

  1) disable the AdLib function inside SQLite-active functions

  2) open a second connection that you reserve to the AdLib function. To avoid SQLITE_BUSY you need to set a large enough timeout (beware of re-entrancy!) and wrap your foregroud operations inside IMMEDIATE transactions.

  3) adopt a less CPU-bound refresh algorithm by using semaphore(s)

  4) there are other ways to share the same SQLite connection between threads but I highly recommend against that workaround, especially in AutoIt context.

  5) using the WAL journaling mode which allows multiple readers (thru multiple distinct connections) AND one writer (with its own distinct connection).

Also, always beware of the lazy -1 to refer to the "last" connection: use handles instead. Finally remember that you can open as many connections as needed, to the same or distinct DBs, at any time.