Add a record of data into new database...

[BlueVision]

Hi folks, my first steps into SQLlight via DBUtils. Fighting since hours with a strange thing. Following User's Guide Chapters 3 & 4 I managed to create a db-file on SD-card into a folder created by my application. So far so good. Currently I have no viewer tryed for the database itself, so I am simply checking the size of the database after putting the first record into it. No error when compiling or during runtime raises up. Only the time of change of the databasefile is corresponding to the moment when my application puts the data into the DB. Filesize is exactly the same after putting a record into the database and this is telling me that the record was not written into the DB.
Maybe I forgot a variable to declare, but in this case the application would not run, all variables are declared in Process Globals:

Dim SQL1 As SQL
Dim ID As Int
Dim DBDirName As String
Dim DBFileName As String
Dim DBTableName1 As String

So the creation of the database is done within the same activity with the following code and runs fine. It's within a loop, deciding to put it to internal storage or to external SD-Card and if it's already exist, not to touch it.

Sub Activity_Create(FirstTime As Boolean)
DBFileName = "My Database.db"
If File.ExternalWritable=True Then
If File.IsDirectory(File.DirRootExternal,"Own Database/") = False Then
File.MakeDir(File.DirRootExternal,"Own Database/")
End If
DBDirName = File.DirRootExternal & "/Own Database"
Else
If File.IsDirectory(File.DirInternal,"Own Database/") = False Then
File.MakeDir(File.DirInternal, "Own Database/")
End If
DBDirName = File.DirInternal & "/Own Database"
End If
If FirstTime Then
SQL1.Initialize(DBDirName,DBFileName, True)
Dim M As Map
M.Initialize
M.Put("ID", DBUtils.DB_INTEGER)
M.Put("Text1", DBUtils.DB_TEXT)
M.Put("Text2", DBUtils.DB_TEXT)
M.Put("Text3", DBUtils.DB_TEXT)
M.Put("Text4", DBUtils.DB_TEXT)
M.Put("Text5", DBUtils.DB_TEXT)
M.Put("Text6", DBUtils.DB_TEXT)
M.Put("Text7", DBUtils.DB_TEXT)
M.Put("Text8", DBUtils.DB_TEXT)
M.Put("Timestamp", DBUtils.DB_TEXT)
DBUtils.CreateTable(SQL1, DBTableName1, M, "ID")
End If
Activity.LoadLayout ("MainLayout")
Activity.Title = ProgName & ProgVersion
End Sub

Then application starts and raises a panel from time to time, forcing the user to input data. After giving an OK-button a go, the following routine should save the users input into the database then

Sub BAddRecord_Click
Dim Maps As List
Dim MP As Map
Maps.Initialize
MP.Initialize
MP.Put("ID",Null)
MP.Put("Text1",Label1.Text)
MP.Put("Text2",Label2.Text)
MP.Put("Text3",AutoCompleteEditText1.Text)
MP.Put("Text4",AutoCompleteEditText2.Text)
MP.Put("Text5",AutoCompleteEditText3.Text)
MP.Put("Text6",AutoCompleteEditText4.Text)
MP.Put("Text7",AutoCompleteEditText5.Text)
MP.Put("Text8",AutoCompleteEditText6.Text)
Dim Now As Long
Now = DateTime.Now
MP.Put("Timestamp",DateTime.Date(Now) & " - " & DateTime.Time(Now))
Maps.Add(MP)
DBUtils.InsertMaps(SQL1,DBTableName1,Maps)
PAddRecord.Visible = False 'Hides the Input-Panel
End Sub

So, in my thinking, if there would be a conflict with the predefined format of my database, during runtime I would get an error. Wrong syntax, the compiler would complain. But when giving the routine a run, the panel is closing, the input-data went to Nirvana... Any idea?

 

[mangojack]

Hi ... have not tested to see if fixes problem , but you have declared variable for table name, but have not assigned any value ?
ie. DBTableName1 = "MyTable"

Dim DBTableName1 As String

DBUtils.InsertMaps(SQL1,DBTableName1,Maps)

Also ,when you create directory no need to include backslash

File.MakeDir(File.DirInternal, "Own Database/")

Replace with ..

File.MakeDir(File.DirInternal, "Own Database")

You might also be interested in this for creating,editing,viewing databases
http://sourceforge.net/projects/sqlitebrowser/

Cheers mj

 

[Erel]

If it still doesn't work then please upload your project (File - Export as zip).

 

[mangojack]

BlueVision .. Have had a chance to test. By assigning a value to variable " DBTableName1"
your database appears to be created correctly

Cheers mj

 

[BlueVision]

Thanks for responding...
I managed to create the folder and the file in it, as I wrote already in the first post. You are right, I dimmed the DBTableName and forgot then to give it a name. Wrote already with Klaus several mails about that. But the real problem is not creating the database, filling it with data is my problem. I set a breakpoint at line 85 of the main. Stepping from line to line then, I can see how the DBUtils-routine is creating the map step by step. But when it comes to writing the data to the Database, no errormessage and no data in my database. I stripped down my program to this essential thing to show you the effect. I am really a little bit lost. As I wrote previously, the filesize has to increase after putting a record into the database in my opinion, but it remains at exactly 12288 Bytes after writing the record to it. Whats my mistake?

 

[BlueVision]

Sorry, forgot the file...

 

[BlueVision]

Ok, hunting for nothing as I see now. I watched the database with a viewer now. Data is inside. But can somebody tell me about that filesize-thing? This is crazy. Is there space reserved for some records when creating the database or is my ES-File-Explorer telling me wrong things?
And because of that filsize-thing I invested a whole weekend of search for an error that does not exist. Great. :sign0013: :BangHead:

 

[mangojack]

So all is OK ? .. Good . Cant comment on the file size as only learning SQL (not through DBUtils) myself. To me it just seemed to add another layer of complexity.Quite surprised at how simple SQL appears to be at this basic stage. After all these years of shrugging it off because of over the top ,complex tutorials. Many thanks to B4A for opening the door.

Cheers mj