SQLTable4 Error

[cyang]

It's odd, the following example code work on the G1 phone, but not on the emulator (sdk 1.6) . I tried to compile and run this example on emulator. Any idea what I forgot to do ?

Anyway, this is the error message.

An Error has occurred in sub:
main_activity_create (B4A line:
65)
SQL1.Initialize(File.DirDefaultExternal, "test1.db",
True)
ANdroid.database.sqlite.SQLiteException:unable to
open database file Continue?

Here is the source:

'version 4.0
Sub Process_Globals
Dim SQL1 As SQL
End Sub

Sub Globals
Dim scvList As ScrollView
Dim Header As Panel
Dim Table As Panel
Dim SelectedItems As Map

Dim NumberOfColumns, RowHeight, RowHeight_1 As Int
Type RowCol (Row As Int, Col As Int)
Dim LineColor As Int
Dim ColumnWidth(3) As Int
Dim ColumnWidth_1(3) As Int
Dim ColLineWidth, RowLineWidth As Int
Dim SelectedRow As Int
Dim SelectedRowColor As Int : SelectedRowColor=Colors.LightGray

'Table settings
NumberOfColumns = 3 'will be overwritten when loading from CscvList file.
ColLineWidth = 1dip
RowLineWidth = 1dip
RowHeight_1 = 30dip
RowHeight=RowHeight_1+RowLineWidth
' LineColor = Colors.Black
LineColor = Colors.Blue

Dim HeaderColor(3) As Int
HeaderColor(0)=Colors.RGB(0,255,255)
HeaderColor(1)=Colors.RGB(0,255,255)
HeaderColor(2)=Colors.RGB(0,160,160)

Dim HeaderFontColor(3) As Int
HeaderFontColor(0)=Colors.Red
HeaderFontColor(1)=Colors.Red
HeaderFontColor(2)=Colors.Yellow

Dim CellColor(3) As Int
CellColor(0) = Colors.White
CellColor(1) = Colors.White
CellColor(2) = Colors.Yellow

Dim FontColor(3) As Int
FontColor(0) = Colors.Black
FontColor(1) = Colors.Black
FontColor(2) = Colors.Red

Dim FontSize(3) As Float
FontSize(0) = 12
FontSize(1) = 12
FontSize(2) = 16

Dim Alignment(3) As Int
Alignment(0) = Gravity.CENTER 'change to Gravity.LEFT or Gravity.RIGHT for other alignments.
Alignment(1) = Gravity.CENTER
Alignment(2) = Gravity.CENTER_VERTICAL
End Sub

Sub Activity_Create(FirstTime As Boolean)
Dim i As Int

If FirstTime Then
SQL1.Initialize(File.DirDefaultExternal, "test1.db", True)
End If

scvList.Initialize(0)
' scvList.Color=Colors.Transparent
scvList.Panel.Color=Colors.Black
Table = scvList.Panel
Table.Color = LineColor
Activity.AddView(scvList, 5%x, 10%y, 90%x, 80%y)
ColumnWidth(0) = ColLineWidth ' hides the first column
ColumnWidth(1) = scvList.Width / 2 ' sets the remaining column widths 1/2 screen width
ColumnWidth(2) = scvList.Width / 2
' ColumnWidth(0) = scvList.Width / 3 ' sets the column widths 1/3 screen width
' ColumnWidth(1) = scvList.Width / 3
' ColumnWidth(2) = scvList.Width / 3
For i=0 To NumberOfColumns-1
' sets the label width to the column width minus column line width
ColumnWidth_1(i) = ColumnWidth(i)-ColLineWidth
Next
SelectedRow = -1
SelectedItems.Initialize

CreateSQLTable
FillSQLTable
ReadSQLTable
End Sub

Sub CreateSQLTable
SQL1.ExecNonQuery("DROP TABLE IF EXISTS table1")
SQL1.ExecNonQuery("CREATE TABLE table1 (Code TEXT , First TEXT, Name TEXT)")
End Sub

Sub FillSQLTable
' Fills a SQL data base
Dim i As Int

SQL1.BeginTransaction
Try
For i = 1 To 100
SQL1.ExecNonQuery2("INSERT INTO table1 VALUES (?, ?, ?)", Array As Object(1000+i, "First"&i, "Name"&i))
Next
SQL1.TransactionSuccessful
Catch
Log(LastException.Message)
End Try
SQL1.EndTransaction
End Sub

Sub ReadSQLTable
' Reads the SQL data base
Dim i As Int
Dim Cursor1 As Cursor

Cursor1 = SQL1.ExecQuery("SELECT Code, First, Name FROM table1")

Dim row(NumberOfColumns) As String
row(0)="Code"
row(1)="First name"
row(2)="Last name"
SetHeader(row)

For i = 0 To Cursor1.RowCount - 1
Dim row(NumberOfColumns) As String
Cursor1.Position = i
row(0)=Cursor1.GetString("Code")
row(1)=Cursor1.GetString("First")
row(2)=Cursor1.GetString("Name")
AddRow(Row)
Next
Cursor1.Close
End Sub

Sub Cell_Click
Dim rc As RowCol
Dim l As Label
Dim txt As String

l = Sender
rc = l.Tag
SelectRow(rc.Row)
' Activity.Title = "Cell clicked: (" & rc.Row & ", " & rc.Col & ")"
' Activity.Title = "Code = "&GetView(rc.row,0).Text
txt="Codes = "
For i=0 To SelectedItems.Size-1
If i=0 Then
txt=txt&SelectedItems.GetValueAt(i)
Else
txt=txt&" / "&SelectedItems.GetValueAt(i)
End If
Next
Activity.Title = txt
End Sub

Sub Header_Click
Dim l As Label
Dim col As Int
l = Sender
col = l.Tag
Activity.Title = "Header clicked: " & col
End Sub

Sub SelectRow(Row As Int)
' remove the color of previously selected row

' single selection
' If SelectedRow > -1 Then
' For col = 0 To NumberOfColumns - 1
' GetView(SelectedRow, Col).Color = CellColor
' Next
' End If
' SelectedRow = Row
' For col = 0 To NumberOfColumns - 1
' GetView(Row, Col).Color = SelectedRowColor
' Next

' multi selection
If SelectedItems.ContainsKey(Row) Then
For col = 0 To NumberOfColumns - 1
GetView(Row, Col).Color = CellColor(col)
Next
SelectedItems.Remove(Row)
Else
SelectedItems.Put(Row,GetView(Row, Col).Text)
For col = 0 To NumberOfColumns - 1
GetView(Row, Col).Color = SelectedRowColor
Next
End If
End Sub

Sub GetView(Row As Int, Col As Int) As Label
' Returns the label in the specific cell
Dim l As Label
l = Table.GetView(Row * NumberOfColumns + Col)
Return l
End Sub

Sub AddRow(Values() As String)
' Adds a row to the table
Dim ColWidth As Int

If values.Length <> NumberOfColumns Then
Log("Wrong number of values.")
Return
End If
Dim lastRow As Int
lastRow = NumberOfRows
ColWidth=0
For i = 0 To NumberOfColumns - 1
Dim l As Label
l.Initialize("cell")
l.Text = values(i)
l.Gravity = Alignment(i)
l.TextSize = FontSize(i)
l.TextColor = FontColor(i)
l.Color=CellColor(i)
Dim rc As RowCol
rc.Initialize
rc.Col = i
rc.Row = lastRow
l.Tag = rc
' Table.AddView(l, ColumnWidth(i)*i, RowHeight * lastRow, ColumnWidth_1(i), RowHeight_1)
Table.AddView(l, ColWidth, RowHeight * lastRow, ColumnWidth_1(i), RowHeight_1)
ColWidth=ColWidth+ColumnWidth(i)
Next
Table.Height = NumberOfRows * RowHeight
End Sub

Sub SetHeader(Values() As String)
' Set the headers values
Dim ColWidth As Int

If header.IsInitialized Then Return 'should only be called once
header.Initialize("")
ColWidth=0
Log("Head")
For i = 0 To NumberOfColumns - 1
Dim l As Label
l.Initialize("header")
l.Text = values(i)
l.Gravity = Alignment(i)
l.TextSize = FontSize(i)
l.Color = HeaderColor(i)
l.TextColor = HeaderFontColor(i)
l.Tag = i
' header.AddView(l, ColumnWidth(i) * i, 0, ColumnWidth_1(i), RowHeight_1)
header.AddView(l, ColWidth, 0, ColumnWidth_1(i), RowHeight_1)
ColWidth=ColWidth+ColumnWidth(i)
Next
Activity.AddView(header, scvList.Left, scvList.Top - RowHeight, scvList.Width, RowHeight)
End Sub

Sub NumberOfRows As Int
Return Table.NumberOfViews / NumberOfColumns
End Sub

Sub SetCell(Row As Int, Col As Int, Value As String)
' Sets the value of the given cell
GetView(Row, Col).Text = value
End Sub

Sub GetCell(Row As Int, Col As Int) As String
' Gets the value of the given cell
Return GetView(Row, Col).Text
End Sub

Sub ClearAll
' Clears the table
For i = Table.NumberOfViews -1 To 0 Step -1
Table.RemoveViewAt(i)
Next
Table.Height = 0
SelectedItems.Clear
' SelectedRow = -1
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

 

[Erel]

I've moved your question to the questions sub-forum.

Your storage card is probably not writable. Do you have a storage card at all?
The storage card can also be locked when you connect the device to the computer.

Try changing File.DirDefaultExternal to File.DirInternal.

 

[cyang]

Hi Erel,

Actually, the code works on the Google G1 phone (1.6 SDK) without any modifications at all. It was the Android virtual emulator that caused the error message. I created an Android Virtual Device (SDK 1.6) and compile & Run on this virtual device. There should be no SD card right?

Ok. After change to File.DirInternal, then it works! But on Eclipse+Java, I don't have to tell the code whether it's internal or external, it just works!. Does b4a has a more generic method to work on both actual device as well as emulator? Thanks for your help.

 

[klaus]

Just for your information, I developped this example with the emulator, and it works with DirDefaultExternal.
When you initiallzed the Emulator did you add under hardware New, Sd card support?

Best regards.

 

[cyang]

Klaus,

No, I did not. After I added 50 MB SD then it works. Thanks.

 

[klaus]

You should also have a look at this example.
ScrollView example with multiselection and SQL.
It's an evolution of SQLTable4.

Best regards.