Kobus
06-06-2009, 05:03 PM
How to create a SQLite program in PPL.
First install Mozilla Firefox and the SQLite Manager Add on which you can find here https://addons.mozilla.org/en-US/firefox/addon/5817
Start Firefox and go to Extra -> SQLite Manager. Create a new database and import existing data in tables or create tables yourself.
It’s very important to set a Primary Key, the difference in loading time is remarkable.
Open the Visual Form Builder in PIDE and add the following fields:
two ComboBoxes, a CheckBox and one Button.
Save the form and open the “sql_v3.ppl “ file from the PPl\Runtime\Lib.
Remove the “@” from the following lines: 131, 175 and 176.
Save this file under a different name in the Lib. Close the SQL_V3.ppl file and return to the form.
Select in the upper menu -> Form -> Initialization section code and type the following lines:
#include "sql_v3….ppl" (The name of your changed sql_v3.ppl file)
#include "swapi.pll"
Local(Aan$, c$, col$, d$, fs$, s$, r$, rc$, data$, row$, column$);
(this is a complete list of the variables that I used, so yours will probably be different).
Go back to your form and select Form -> Form creation code from the menu.
Type the following code:
Global(input1$, input2$, input3$, input4$, index$);
fs$ = FileSize(AppPath$ + "Wn.sql");
ProgressBar_SetRange(Progressbar129$, 0, (0, fs$ / 2));
s$ = sqlOpen(AppPath$ + "Wn.sql");
if (s$ == 0)
ShowMessage(SQLError$);
End;
rc$ = SqlGetTable(s$, "SELECT Name FROM Tessie a", &data$, &row$, &column$);
if (rc$ <> SQLITE_OK)
ShowMessage(SQLERROR$);
end;
ForEach(data$)
If(data$ != "")
ComboBox_Add(Input1$, data$);
End;
SQLClose(s$);
End;
DIM(Aan$, 1001);
Aan$ = 1;
While(Aan$ <1001)
ComboBox_Add(Input2$, Aan$);
Aan$++;
End;
The explanation for the code above:
The global variables contain the names of the 4 controls that are on the form. Why name them input1, 2, 3 and 4?
When you want to create a user friendly application for a mobile device it’s nice to have a control(button) to move to the next or previous field. The code for this will be explained later.
First I want to know the size of the database and use it for a progressbar. On opening the program it’s nice to use a progressbar in case it is a large database.
When you use AppPath$ + the database name and extension (in this example: Wn.sql) it doesn’t matter what the path on the users device is. So always use this in your code instead of a full path name.
If you don’t use the progressbar just remove these two lines, otherwise change the “Progressbar129$” variable into the name it has on your form.
Next we call the database with the command “sqlOpen”, the same way as we requested for the file size.
The next “if” statement shows us (and the user) an error message in case the database doesn’t exist; a typing error is easily made.
Then we call the table, in this example it’s named “Tessie” and the “a” after the table name is needed, don’t ask me why. In this example I only need the column “Name”; if you want to use more columns from the database just add the column name(s) from you database, separated by a comma.
Again we use a message to check if data is found in the table.
In this example I selected all records from the column “Name” and the user has to select one of them. With the ForEach statement the first ComboBox is filled with all the records from the database. The If(data$ != "") statement skips empty fields.
The ComboBox variable name has to be the same as it’s named on your form (on the form without the $).
It’s very important to close the connection with the database with SQLClose(s$).
As you may have noticed you don’t have to change anything in the SQL database. It’s just manipulated with this easy code in PPL.
For the second ComboBox I needed a range of numbers, so I used this code:
DIM(Aan$, 1001);
Aan$ = 1;
While(Aan$ <1001)
ComboBox_Add(Input2$, Aan$);
Aan$++;
End;
On the form the name of this ComboBox is Input2, the starting number is 1: Aan$ = 1);
and this ComboBox is also filled by the command ComboBox_Add.
The third control is a Checkbox and I needed it to standard be checked, so I added the following code in the Creation section: Button_SetCheck(Input3$, true);
We go back to the form to add and set some functionality and properties.
Rename the first ComboBox to Input1, and check the following settings in the upper Properties window:
- CBS_AUTOHSCROLL
- CBS_DROPDOWN
- CBS_HASSTRINGS
- CBS_SORT
The last three properties in the list are checked standard.
In the second ComboBox (renamed as well) check the same properties, except for CBS_Sort. That has a wrong effect for numbers.
Check the following properties for the (renamed) CheckBox:
BS_AUTOCHECKBOX
BS_CHECKBOX
BS_NOTIFY
You can always try what the effect of any property is and read about it on: http://msdn.microsoft.com/en-us/library/bb775793(VS.85).aspx and other pages of msdn
As always, save your work and run it. It might very well be that it doesn’t run because I almost forgot to mention that you need some dll’s.
Copy the sql…..dll files from Program Files\PLL\RUNTIME to the directory were your project is stored.
IMPORTANT: the sqlite3_ppc.dll file is in the PPL directory on your device. Copy it to the project’s directory on your device.
One other property that I changed was the height of the dropdown list (in the Creation section):
Control_Resize(Input1$, 280, 320);
Control_Resize(Input2$, 96, 320);
I added one event to the first ComboBox and actually I could use this for the second one as well:
Select the Combobox on the form and then select the “Events” tab on the right side of the screen.
Double click the “OnSetFocus” line and type the following code in the new screen (after the “HandleEventParms” line:
SendMessage(Input1$, CB_ShowDropDown, 1, 0);
For a user friendly form we add some code to the button:
Select the button on the form (rename it first when you want to use another name) and double click “OnLeftButtonDown” in the Event window.
Type this code in the new window:
index$++;
If(index$>3)
Index$ = 1;
End;
SetFocus(getvar("input"%index$%"$"));
The declaration of the variables was already done in the beginning, in the creation part.
The explanation:
When the user clicks the button on the screen the index is raised by 1. So the SetFocus command will move the focus to the next field. That is why it’s practical to name the fields as a series, from 1 and up. So, if you use more or less fields on your form change the line: If(index$>3)
Don’t forget to copy the .sql database file to the project directory on your device!
This was part one of SQLite, the second part will be about writing the selected data to a new table in the same SQL database.
Special thanks to Nicknack, who helped me with the SQL Manager and the Index issue.
Success and have fun,
Helen
First install Mozilla Firefox and the SQLite Manager Add on which you can find here https://addons.mozilla.org/en-US/firefox/addon/5817
Start Firefox and go to Extra -> SQLite Manager. Create a new database and import existing data in tables or create tables yourself.
It’s very important to set a Primary Key, the difference in loading time is remarkable.
Open the Visual Form Builder in PIDE and add the following fields:
two ComboBoxes, a CheckBox and one Button.
Save the form and open the “sql_v3.ppl “ file from the PPl\Runtime\Lib.
Remove the “@” from the following lines: 131, 175 and 176.
Save this file under a different name in the Lib. Close the SQL_V3.ppl file and return to the form.
Select in the upper menu -> Form -> Initialization section code and type the following lines:
#include "sql_v3….ppl" (The name of your changed sql_v3.ppl file)
#include "swapi.pll"
Local(Aan$, c$, col$, d$, fs$, s$, r$, rc$, data$, row$, column$);
(this is a complete list of the variables that I used, so yours will probably be different).
Go back to your form and select Form -> Form creation code from the menu.
Type the following code:
Global(input1$, input2$, input3$, input4$, index$);
fs$ = FileSize(AppPath$ + "Wn.sql");
ProgressBar_SetRange(Progressbar129$, 0, (0, fs$ / 2));
s$ = sqlOpen(AppPath$ + "Wn.sql");
if (s$ == 0)
ShowMessage(SQLError$);
End;
rc$ = SqlGetTable(s$, "SELECT Name FROM Tessie a", &data$, &row$, &column$);
if (rc$ <> SQLITE_OK)
ShowMessage(SQLERROR$);
end;
ForEach(data$)
If(data$ != "")
ComboBox_Add(Input1$, data$);
End;
SQLClose(s$);
End;
DIM(Aan$, 1001);
Aan$ = 1;
While(Aan$ <1001)
ComboBox_Add(Input2$, Aan$);
Aan$++;
End;
The explanation for the code above:
The global variables contain the names of the 4 controls that are on the form. Why name them input1, 2, 3 and 4?
When you want to create a user friendly application for a mobile device it’s nice to have a control(button) to move to the next or previous field. The code for this will be explained later.
First I want to know the size of the database and use it for a progressbar. On opening the program it’s nice to use a progressbar in case it is a large database.
When you use AppPath$ + the database name and extension (in this example: Wn.sql) it doesn’t matter what the path on the users device is. So always use this in your code instead of a full path name.
If you don’t use the progressbar just remove these two lines, otherwise change the “Progressbar129$” variable into the name it has on your form.
Next we call the database with the command “sqlOpen”, the same way as we requested for the file size.
The next “if” statement shows us (and the user) an error message in case the database doesn’t exist; a typing error is easily made.
Then we call the table, in this example it’s named “Tessie” and the “a” after the table name is needed, don’t ask me why. In this example I only need the column “Name”; if you want to use more columns from the database just add the column name(s) from you database, separated by a comma.
Again we use a message to check if data is found in the table.
In this example I selected all records from the column “Name” and the user has to select one of them. With the ForEach statement the first ComboBox is filled with all the records from the database. The If(data$ != "") statement skips empty fields.
The ComboBox variable name has to be the same as it’s named on your form (on the form without the $).
It’s very important to close the connection with the database with SQLClose(s$).
As you may have noticed you don’t have to change anything in the SQL database. It’s just manipulated with this easy code in PPL.
For the second ComboBox I needed a range of numbers, so I used this code:
DIM(Aan$, 1001);
Aan$ = 1;
While(Aan$ <1001)
ComboBox_Add(Input2$, Aan$);
Aan$++;
End;
On the form the name of this ComboBox is Input2, the starting number is 1: Aan$ = 1);
and this ComboBox is also filled by the command ComboBox_Add.
The third control is a Checkbox and I needed it to standard be checked, so I added the following code in the Creation section: Button_SetCheck(Input3$, true);
We go back to the form to add and set some functionality and properties.
Rename the first ComboBox to Input1, and check the following settings in the upper Properties window:
- CBS_AUTOHSCROLL
- CBS_DROPDOWN
- CBS_HASSTRINGS
- CBS_SORT
The last three properties in the list are checked standard.
In the second ComboBox (renamed as well) check the same properties, except for CBS_Sort. That has a wrong effect for numbers.
Check the following properties for the (renamed) CheckBox:
BS_AUTOCHECKBOX
BS_CHECKBOX
BS_NOTIFY
You can always try what the effect of any property is and read about it on: http://msdn.microsoft.com/en-us/library/bb775793(VS.85).aspx and other pages of msdn
As always, save your work and run it. It might very well be that it doesn’t run because I almost forgot to mention that you need some dll’s.
Copy the sql…..dll files from Program Files\PLL\RUNTIME to the directory were your project is stored.
IMPORTANT: the sqlite3_ppc.dll file is in the PPL directory on your device. Copy it to the project’s directory on your device.
One other property that I changed was the height of the dropdown list (in the Creation section):
Control_Resize(Input1$, 280, 320);
Control_Resize(Input2$, 96, 320);
I added one event to the first ComboBox and actually I could use this for the second one as well:
Select the Combobox on the form and then select the “Events” tab on the right side of the screen.
Double click the “OnSetFocus” line and type the following code in the new screen (after the “HandleEventParms” line:
SendMessage(Input1$, CB_ShowDropDown, 1, 0);
For a user friendly form we add some code to the button:
Select the button on the form (rename it first when you want to use another name) and double click “OnLeftButtonDown” in the Event window.
Type this code in the new window:
index$++;
If(index$>3)
Index$ = 1;
End;
SetFocus(getvar("input"%index$%"$"));
The declaration of the variables was already done in the beginning, in the creation part.
The explanation:
When the user clicks the button on the screen the index is raised by 1. So the SetFocus command will move the focus to the next field. That is why it’s practical to name the fields as a series, from 1 and up. So, if you use more or less fields on your form change the line: If(index$>3)
Don’t forget to copy the .sql database file to the project directory on your device!
This was part one of SQLite, the second part will be about writing the selected data to a new table in the same SQL database.
Special thanks to Nicknack, who helped me with the SQL Manager and the Index issue.
Success and have fun,
Helen