Anhother Questions about Sqlite

[drj]

Hi,

I see that almost all the examples you show are for accessing an SQL database and displaying data directly in a ListView or other view element.

In my case I just want to read the SQL data into my data structures in memory and then do calculations on this data. The code I am converting calls an SQL Dll directly to access the data.

I have a lot of code to convert to your system that performs IO to an SQLite database so for maintenance I would like to access the data in a similar manner by perhaps replacing the calls I am making to get and set the data to equivalent ones in your system.

While I can understand that with your system it there may be easier ways to perform this , but if I can do this replacement at a low level my conversion and maintenance job will be minimized.

Can you help me?

Thanks Jerry



Public Sub ReadFunctions()


Equation = " "

Formula = " "

Answertxt = " "

Dim rowid As Integer
rowid = 1


myQuery = "select * FROM functions WHERE rowid=?"


SQLOK = Prepare2(db, myQuery, myQuery.Length, init_statement, IntPtr.Zero)


Dim errmsg As String

If (Prepare2(db, myQuery, -1, init_statement, IntPtr.Zero) <> SQLITE_OK) Then

errmsg = "Error: failed to prepare statement with message ." + GetErrmsg(db).ToString
errmsg = errmsg

Return

End If



Try


SQLOK = BindInt(init_statement, 1, rowid)

SQLOK = SqlStep(init_statement)

If SQLOK = SQLITE_ROW Then

DecimalPlaces = ColumnDouble(init_statement, 1)
xEntry = ColumnDouble(init_statement, 2)
yEntry = ColumnDouble(init_statement, 3)

xEntry = xEntry
yEntry = yEntry


SciIn = ColumnDouble(init_statement, 4)
SciOut = ColumnDouble(init_statement, 5)

Angle = ColumnDouble(init_statement, 6)
ButtonClicked = ColumnDouble(init_statement, 7)

Equation = ColumnString(init_statement, 8)
If Equation = "" Then Equation = " "
Formula = ColumnString(init_statement, 9)
If Formula = "" Then Formula = " "
Answertxt = ColumnString(init_statement, 10)
If Answertxt = "" Then Answertxt = " "



xhi = ColumnDouble(init_statement, 11)
xlo = ColumnDouble(init_statement, 12)
xincr = ColumnDouble(init_statement, 13)

rootshi = ColumnDouble(init_statement, 14)
rootslo = ColumnDouble(init_statement, 15)
rootsformula = ColumnString(init_statement, 16)
If rootsformula = "" Then rootsformula = " "
derivativex = ColumnDouble(init_statement, 17)
derivativeformula = ColumnString(init_statement, 18)
If derivativeformula = "" Then derivativeformula = " "

integralhi = ColumnDouble(init_statement, 19)
integrallo = ColumnDouble(init_statement, 20)
integralformula = ColumnString(init_statement, 21)
If integralformula = "" Then integralformula = " "
integralsteps = ColumnDouble(init_statement, 22)

End If

SQLOK = Finalize(init_statement)

SQLOK = SQLOK

Return


Catch e1 As Exception


SQLOK = SQLOK
SQLOK = SQLOK

Return

End Try







End Sub

Public Sub SaveFunctions()





Dim myQuery As String

Dim rowid As Integer

rowid = SelectedSaveCase
rowid = 1



myQuery = "UPDATE Functions SET decimals=?,x=?,y=?, sinput=?, soutput=?,radians=?,operation=?,Equation=?,Formula=?,Answer=?,xhi=?,xlo=?,xincr=?,rootshi=?,rootslo=?,rootsFormula=?,derivativex=?,derivativeFormula=?,integralhi=?,integrallo=?,integralFormula=?,integralsteps=? WHERE rowid=? "

Dim errmsg As String

If (Prepare2(db, myQuery, -1, update_statement, IntPtr.Zero) = SQLITE_OK) Then

Else

errmsg = "Error: failed to prepare statement with message '%s'." + GetErrmsg(db).ToString

Return

End If


SQLITE_TRANSIENT = New IntPtr(-1)


BindDouble(update_statement, 1, DecimalPlaces)
BindDouble(update_statement, 2, xEntry)
BindDouble(update_statement, 3, yEntry)
BindDouble(update_statement, 4, SciIn)
BindDouble(update_statement, 5, SciOut)
BindDouble(update_statement, 6, Angle)
BindDouble(update_statement, 7, ButtonClicked)
BindText(update_statement, 8, Equation, -1, SQLITE_TRANSIENT)
BindText(update_statement, 9, Formula, -1, SQLITE_TRANSIENT)
BindText(update_statement, 10, Answertxt, -1, SQLITE_TRANSIENT)
BindDouble(update_statement, 11, xhi)
BindDouble(update_statement, 12, xlo)
BindDouble(update_statement, 13, xincr)

BindDouble(update_statement, 14, rootshi)
BindDouble(update_statement, 15, rootslo)
BindText(update_statement, 16, rootsformula, -1, SQLITE_TRANSIENT)
BindDouble(update_statement, 17, derivativex)
BindText(update_statement, 18, derivativeformula, -1, SQLITE_TRANSIENT)
BindDouble(update_statement, 19, integralhi)
BindDouble(update_statement, 20, integrallo)
BindText(update_statement, 21, integralformula, -1, SQLITE_TRANSIENT)
BindDouble(update_statement, 22, integralsteps)



BindInt(update_statement, 23, rowid)


Dim success As Integer

success = SqlStep(update_statement)

Reset(update_statement)


If (success <> SQLITE_DONE) Then

success = success

Else

success = success
End If


success = Finalize(update_statement)

success = success




End Sub

 

[Erel]

I recommend you to use DBUtils module. Specifically DBUtils.ExecuteMemoryTable.

 

[drj]

I see it in Doc but have a question

Hi,

You recommended I use ExecuteMemoryTable to read data into memory and I agree but I have a question in the documentation.

The doc says ExecuteMemoryTable9SQL as SQL, Query as String , StringArgs() as String, Limit as int) as List

I can see that if I am retrieving a number of records where the StringArgs come into play.

But lets say that there are a number of columns but only one record in the Table.

Would the access be like this?

Dim Lt as list

List = ExecuteMemoryTable(SQL as SQL , Null,0)

i.e. the columns of data would be stored in List

If I have 5 columns then

col 1 data data1= list(1)

col2 data data2= list(2)

i.e., the string of arrays is only needed if I have a number of records coming back from the data base.

Do I have this right? what does List return?

Thanks I am getting there

 

[Erel]

Please use [ code ] [ /code ] tags (without spaces) when posting code.

Each item in the list is an array of strings. Even if there is only one column.

Dim columns() As String = List.Get(0) 'get first row
Log(Columns(0)) 'print first column