Android Question SQLite, Column Names, and AHLocale

[tdocs2]

Greetings.

What a headline: SQLite, Column Names, and AHLocale

I want to take advantage of all the wonderful presentation tools to the end user that Webview and Tables (original by Erel and expanded by Klaus) offer. Using these tools, I can quickly put together a query and give the end user a nice looking table to look at and if it is a table, one that he/she can even sort. GREAT!

Issue:
1. End users who speak different languages.
2. The header contains the column names used to create the data base.
3. The Queries contain the column names.
4. A Spanish speaking user would want to see Apellido and not Surname or Last Name in the Table or Webview; a German speaking user, Nachname; Italian - Cognome. You get the drift...

What I think I know:
1. I could select 4 or 5 languages and create data bases with the native language column names.
This solves part of the problem.
2. What about queries? Column numbers, from what I have read, cannot be used in queries, and is not really desirable for maintenance. Thus, this is a problem.

Could the column name be a variable IN A QUERY?

SELECT * FROM Persons WHERE [Last Name] = "Smith"...

How could this Query be rewritten if:

Dim Col(2) as String

Col(2)="Last Name"

I am out of my league in this area. What are best practices related to this issue?

Any help will be welcomed.

Thank you.

 

[DonManfred]

You are going the wrong way. No matter on what database-engine.
YOU, as coder of the app and maintainer of the database, are the only one who need the table and fieldnames.
The enduser of your app just want to see the headercolumns in his language.

I would suggest to setup a table for the languages.
possibly better but more work at beginning would be somethink like

table languages
langid int autoinc index
lang_name varchar ("German", "English", "Italian",...)

table fieldnames
fid int autoinc
fname varchar "surname" <- The real fieldname. You should use a prefix in fieldname on each table to avoid duplicates from different tables

table langitems
id autoincrement
langid id of language (german, english)
fieldid int (one from table_fieldname)
value varchar -> Depending on languageid "Surname" or "Nachname" or "Cognome" or whatever

I´m really not familar with SQLite on Android. I´m using a MySQL database with a php-bridge to communicate. But this has disadvantages as you always need internetconnection.

So sadly i cannot help with sqlite statements to build this.

That´s just my 5 cent

 

[klaus]

Have a look at the SQLiteLight3 example in my signature.
This one uses the variables ColNames() for the column names (wich must be unique as already mentioned by DonManfred) and ColAliasNames() for the column header display.
So you could change the values of the ColAliasNames() variable to adapt to the language, using DonManfred's principle.

 

[tdocs2]

I wished there was a LIKE PLUS for the two posts from Don and Klaus. I was giving careful thought to characterize the two posts and I think I found it:

WOW and WOW!!!! (I Google Translated it WOW into several languages and it came up as WOW - did not try Hebrew, Greek, Korean, or Chinese).

Danke, Don. Thank you, Klaus.

It would have taken 30 pages of narrative to coin what Klaus referred to as the

DonManfred's principle

table languages
langid int autoinc index
lang_name varchar ("German", "English", "Italian",...)

table fieldnames
fid int autoinc
fname varchar "surname" <- The real fieldname. You should use a prefix in fieldname on each table to avoid duplicates from different tables

table langitems
id autoincrement
langid id of language (german, english)
fieldid int (one from table_fieldname)
value varchar -> Depending on languageid "Surname" or "Nachname" or "Cognome" or whatever

Three additional tables to globalize the DB in an app. I am still digesting how to apply it and I may ask follow up questions later.

@klaus

So you could change the values of the ColAliasNames() variable to adapt to the language

I have to experiment with this in the SQLite3 example to see the interaction of ColAliasNames() with Klaus and Erel Table Class or Webview. However, I did not want to wait until I started working with the SQLite3 example to express my gratitude to both of you, Don and Klaus.

Best wishes,

Sandy

 

[DonManfred]

As i wrote earlier. It May be a Bit more Work at beginning but later, im sure, it Would bei less work to add more languages

 

[tdocs2]

Have a look at the SQLiteLight3 example in my signature.
This one uses the variables ColNames() for the column names (wich must be unique as already mentioned by DonManfred) and ColAliasNames() for the column header display.
So you could change the values of the ColAliasNames() variable to adapt to the language, using DonManfred's principle.

Hello, Klaus.

I have been experimenting with SQLite3. Without yet implementing Don's principles, I have modified the ColAliasNames and they appear in the Edit and in the table headers by use of the "As" in the query.

1. What I have not been able to see, which is referenced by Don, is the Autoincrement for the ID. In the AddEntry Sub, I do not see how the ID is generated. How is the ID generated? (BTW, in the edit fields the text color was set to black on a black background - could not see the text.)

2. Newbie comment: In Main, there is no click event for the Webview. Instead, there is:
Sub wbvTable_OverrideUrl (Url AsString)
which I could have never figured out (only by trial and error). I guess I was used to the table class which does have a click event...

Thanks again.

Sandy

 

[Mahares]

1. What I have not been able to see, which is referenced by Don, is the Autoincrement for the ID. In the AddEntry Sub, I do not see how the ID is generated. How is the ID generated? (BTW, in the edit fields the text color was set to black on a black background - could not see the text.)

When you create your table from scratch you need to add the AUTOINCREMENT like this:

ColDataTypes(0) = "INTEGER PRIMARY KEY AUTOINCREMENT"

To see the text when adding a record, you can do this in the Edit activity:
Change this: edt(i).TextColor = Colors.Black
to this: edt(i).TextColor = Colors.Yellow

 

[tdocs2]

When you create your table from scratch you need to add the AUTOINCREMENT like this:

ColDataTypes(0) = "INTEGER PRIMARY KEY AUTOINCREMENT"

To see the text when adding a record, you can do this in the Edit activity:
Change this: edt(i).TextColor = Colors.Black
to this: edt(i).TextColor = Colors.Yellow

Thank you, Mahares. The AUTOINCREMENT clause hits the mark.

For keys with multiple fields: PRIMARY KEY(PersonID, TranID) and AUTOINCREMENT applies only to TranID, is the format:

 PRIMARY KEY(PersonID, TranID AUTOINCREMENT)

(I had already changed the edit text color in the SQLite3 app - I was giving Klaus a heads up...)

Best regards.

Sandy

 

[Mahares]

PRIMARY KEY(PersonID, TranID AUTOINCREMENT)

Since you are using AUTOINCREMENT, you cannot combine the two in the primary key. It has to be one or the other.
ColNames(0) = "PersonID"
ColNames(1) = "TranID"
You can only have one of these:
ColDataTypes(0) = "INTEGER PRIMARY KEY AUTOINCREMENT"
or
ColDataTypes(1) = "INTEGER PRIMARY KEY AUTOINCREMENT"

 

[tdocs2]

Since you are using AUTOINCREMENT, you cannot combine the two in the primary key. It has to be one or the other.
ColNames(0) = "PersonID"
ColNames(1) = "TranID"
You can only have one of these:
ColDataTypes(0) = "INTEGER PRIMARY KEY AUTOINCREMENT"
or
ColDataTypes(1) = "INTEGER PRIMARY KEY AUTOINCREMENT"

Thank you, Mahares.

If I understand correctly, if I have a primary key with 2 or more data fields (not uncommon), the AUTOINCREMENT feature cannot be used and basically has to be controlled by the developer by getting last record on file and then adding 1... The AUTOINCREMENT is still a nice feature for single key tables.

Best regards.

Sandy