Android Question Changing name of "name.db" in SQL. (Solved)

Roger Daley · May 28, 2019

Hi All,

I have a query re: SQL db file.

In my project I attempted to cut corners by using an existing DataBase file but came to grief when I changed it's name. To try to track the problem I used Klaus' SQLiteLight2 example.
In the IDE Files tab I changed persons.db to persons1.db and then changed all occurrences of persons.db in the code.
When I open the APP I get the following Log:

** Activity (main) Pause, UserClosed = true **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
main_executehtml (java line: 452)
android.database.sqlite.SQLiteException: no such table: persons1 (code 1): , while compiling: SELECT FirstName As [First name], LastName As [Last name], City FROM persons1

Which is telling me persons1 does not exist. It does exist.

I can't see why a name change such as this should cause problems unless I miss changing the name somewhere. I am sure I have not missed anything.

Regards Roger

The two Subs involved are below:

B4X:

Private Sub ShowTable
    Private Query As String
    
    Query = "SELECT FirstName As [First name], LastName As [Last name], City FROM persons1"
    'depending if the filter is active or not we add the filter query at the end of the query
    'the filter query is defined in the Filter Activity
    If Filter.flagFilterActive = False Then
        btnFilter.Text = "Filter"    'change the text in the Filter button
    Else
        Query = Query & Filter.Query
        btnFilter.Text = "UnFilter"    'change the text in the Filter button
    End If
    'displays the database in a table
    wbvTable.LoadHtml(ExecuteHtml(SQL1, Query, Null, True))
    ReadDataBaseRowIDs
End Sub

B4X:

Private Sub ExecuteHtml(SQL As SQL, Query As String, StringArgs() As String, Clickable As Boolean) As String
   Private ResultSet1 As ResultSet
   If StringArgs <> Null Then
       ResultSet1 = SQL.ExecQuery2(Query, StringArgs)
   Else
       ResultSet1 = SQL.ExecQuery(Query)
   End If
   Private sb As StringBuilder
   sb.Initialize
   sb.Append("<html><body>").Append(CRLF)
   sb.Append("<style type='text/css'>").Append(HtmlCSS).Append("</style>").Append(CRLF)
   sb.Append("<table><tr>").Append(CRLF)
   For i = 0 To ResultSet1.ColumnCount - 1
       sb.Append("<th>").Append(ResultSet1.GetColumnName(i)).Append("</th>")
   Next
   
   sb.Append("</tr>").Append(CRLF)
   
   Private row As Int
   row = 0
   Do While ResultSet1.NextRow
       If row Mod 2 = 0 Then
           sb.Append("<tr>")
       Else
           sb.Append("<tr class='odd'>")
       End If
       For i = 0 To ResultSet1.ColumnCount - 1
           sb.Append("<td>")
           If Clickable Then
               sb.Append("<a href='http://").Append(i).Append(".")
               sb.Append(row)
               sb.Append(".stub'>").Append(ResultSet1.GetString2(i)).Append("</a>")
           Else
               sb.Append(ResultSet1.GetString2(i))
           End If
           sb.Append("</td>")
       Next
       sb.Append("</tr>").Append(CRLF)
       row = row + 1
   Loop
   
   ResultSet1.Close
   sb.Append("</table></body></html>")
   Return sb.ToString
End Sub

DonManfred · May 28, 2019

Upload a small project which shows the issue. Make sure to include the Databasefile used.
Hard to help with this less information you provide.

Roger Daley · May 28, 2019

DonManfred,

Small project uploaded

Roger

DonManfred · May 28, 2019

Roger Daley said:
FROM persons1"

The Database does NOT contains a Table persons1! There is only a Table persons.

Note that the tables inside are NOT related to the databasefilename used. you can name it willi.db but the table inside is still persons. Create a table persons1 if you want to have a table with this name. Or rename the table (if sqlite support renaming a table).

Roger Daley · May 28, 2019

Thanks DonManfred,

I now see the problem.
Is there an easy way to change the name of the Table?

Roger

DonManfred · May 28, 2019

Roger Daley said:
Is there an easy way to change the name of the Table?

i do not use sqlite...
But Google search is working. It is not a B4A Question. It is a question about SQLite for which you should check the SQLite Documentation.

Roger Daley · May 28, 2019

Thanks DonManfred,

Documentation already in hand.

Roger

Mahares · May 28, 2019

Roger Daley said:
Documentation already in hand.

B4X:

starter.SQL1.ExecNonQuery("ALTER TABLE persons RENAME TO persons1")

klaus · May 28, 2019

Roger Daley said:
Is there an easy way to change the name of the Table?

From the first post you wanted to change the name of the database.
Here you ask to change the table, these are different things.
And then I suppose that you would also change the column names.

For this, you should have used SQLiteLight3 instead ot SQLiteLight2.
SQLiteLight3 is almost the same program as SQLiteLight2.
The big difference is that all names like:
SQLDataBasePath, SQLDateBaseName, SQLTabelName, ColNames, ColAliasNames and ColDataTypes
are variables and not hard coded like in SQLiteLight2.

You can easily change the program for another database!

Attached a project demonstrating it.

In the Starter module you have
Public DBIndex = 0 As Int
Change the value to 1 loads another database.
Look at all If DBIndex = 0 Then lines.
You'll see what you should change in the code to adapt it to another database.

Roger Daley · May 28, 2019

Hh

klaus said:
From the first post you wanted to change the name of the database.
Here you ask to change the table, these are different things.
And then I suppose that you would also change the column names.

For this, you should have used SQLiteLight3 instead ot SQLiteLight2.
SQLiteLight3 is almost the same program as SQLiteLight2.
The big difference is that all names like:
SQLDataBasePath, SQLDateBaseName, SQLTabelName, ColNames, ColAliasNames and ColDataTypes
are variables and not hard coded like in SQLiteLight2.

You can easily change the program for another database!

Attached a project demonstrating it.

In the Starter module you have
Public DBIndex = 0 As Int
Change the value to 1 loads another database.
Look at all If DBIndex = 0 Then lines.
You'll see what you should change in the code to adapt it to another database.

Thanks Klaus,
I was confused about database/table DonMafred lit the light bulb on that one.
All just part of the learning process. Like always once you know, you look back and find it should have been obvious.

Thanks for the explanation on SQL3, if I knew then what I know now I would have used it.

In the mean time I have found a dB editor and tweaked the names etc. All good.

Regards Roger

PS A point of interest, the App I am trying to insert the data base was derived from your GPS example. It probably means that you can have half the profits.
0/2=?

Android Question Changing name of "name.db" in SQL. (Solved)

Roger Daley

Well-Known Member

DonManfred

Expert

Roger Daley

Well-Known Member

Attachments

DonManfred

Expert

Roger Daley

Well-Known Member

DonManfred

Expert

Roger Daley

Well-Known Member

Mahares

Expert

klaus

Expert

Attachments

Roger Daley

Well-Known Member

Similar Threads

Android Question Changing name of "name.db" in SQL. (Solved)

Well-Known Member

Expert

Well-Known Member

Attachments

Expert

Well-Known Member

Expert

Well-Known Member

Expert

Expert

Attachments

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency