There are different ways within hk_classes to add data to a table:
Using hk_classes commands
Using SQL (INSERT, UPDATE etc.)
The advantage to use the hk_classes commands is, that you don't have to bother about correct SQL, gives you the possibility of a much finer grained data manipulation and it will also work over the frontiers of different database backends, even to those who don't understand SQL. I recommend to use this way.
Example 5.15. Copy data from an existing datasource to an existing table in the same database
The following example copies all names of the authors into another,existing table "newauthors". It assumes that both datasources (tables) are not defined as form datasources and will be part of the same database (on the same SQL server). Of course it works the same way with form datasources (which you can access with the hk_thisform.get_datasource() function family), except the fact, that you don't need to enable them (lines 5 and 7).
Lines 1 to 7 are simple definition rows. Lines 8 and 9 define the source and the target columns. Of course you can use as many columns as needed, or write fixed text or whatever you want.
Copying happens in lines 10 to 15. The targettable must be in insertmode (line 11) otherwise it will update the current row.
To copy only some rows, simply edit the while loop.
db=hk_thisform.database() (1)
sourcetable=db.new_table("authors") (2)
targettable=db.new_table("newauthors") (3)
i=0 (4)
sourcetable.enable() (5)
sourcetable.goto_first() (6)
targettable.enable() (7)
sourcecolumn1=sourcetable.column_by_name("name") (8)
targetcolumn1=targettable.column_by_name("name") (9)
while i< sourcetable.max_rows(): (10)
targettable.setmode_insertrow() (11)
targetcolumn1.set_asstring(sourcecolumn1.asstring()) (12)
targettable.store_changed_data() (13)
sourcetable.goto_next() (14)
i=i+1 (15)
targettable.disable()(16)
sourcetable.disable()(17)
Example 5.16. Copy data from an existing datasource to an existing table in different databases (also different server backends)
To do the same, but store the data on a different server you simply have to replace line 3 from the previous example. Let's say your source data is on a Postgres server and the target data should be stored in a local format, e.g. SQLite3. It works the same with Mysql etc, simply replace the connection name in line 3. The connect() command in line 4 will open the password dialog when necessary.
[Lines 1 to 2 as above] (1)
dr=db.connection().drivermanager() (2)
con=dr.new_connection("sqlite3") (3)
con.connect() (4)
sqlitedb=con.new_database("mysqlitedatabase") (5)
targettable=sqlitedb.new_table("newauthors") (6)
[Lines 4 to 17 as above] (7)
Example 5.17. Copy data from an existing datasource to a new table
The last example copies the result a of query into a new table. In line 3 you can define the SQL statement. Be aware that you have to use the ANSI SQL delimiters, means " as identifier delimiter and ' as text delimiter. Particulary Mysql uses different delimiters. hk_classes will fix them in resultqueries (but not in actionqueries). Don't worry about the function name "copy_table" in line 5. It will also allow to copy data from resultqueries.
targetdb=hk_thisform.database() (1) sourcequery=targetdb.new_resultquery() (2) sql="SELECT * from \"authors\"" (3) sourcequery.set_sql(sql) (4) targetdb.copy_table(sourcequery,True,False) (5)