Here are my parameters for this proof of concept task:
- No exports from QlikView to populate source tables
- No links to any external resources outside of QlikView.
- Solely 100% QlikView objects, resources and scripting
- Provide INSERT, UPDATE, DELETE functionality to a single primary Oracle or SQL server database source table.
- Provide full table displays for row selection of the UPDATE/DELETE row being picked (and not merely use an input box for the population of a primary key for single row selection).
- Must work on a single user evaluation version with no QlikView server facilities available.
INSERT - No problem... POC PASSED! I succeeded doing an insert by populating input boxes with temporary variables. I even brought in values from a 2nd
Oracle source template table and populated my input boxes from them to speed data entry. I used RELOAD and IF/THEN statements to select a DEV/QA/PROD database connection. I seem to have command of the "SQL Insert into table..." part going on just fine.
UPDATE/DELETE functionally seems to be more of a challenge for me.
I am to load the table from my Oracle source into a table box on the screen so the user can isolate which row will have the action performed. Done! Easy so far...
My goal is to select a single row from that table box and copy all columns of that row to an input box (to make changes to columns I want to update). Then I hoped to do a RELOAD call. This RELOAD would then do an UPDATE to my original Oracle source table from the Input Box variables.
The tricky part is getting one row of column data from my resident table in memory data into those variables assigned to each column to persist because the 2nd RELOAD which calls upon those temp variables to select a single row of data on the in memory table needed to assign that row to the temp column variable disappears.
In other words, I loaded up TABLE_A: SELECT * FROM ORACLE_SOURCE
TABLE_A had 100 rows of data in memory
Now with one row selected and displayed, I want all column data to live in the vCol1, vCol2, vCol3 variables I have created for an input box.
The following is my failed attempt and the reason why:
The problem is populating all the temp column variables with the press of a button to RELOAD and to reselect my data from the original Oracle Source table to populate
TABLE_B: Select onerow FROM OracleSource based on unique key (from TABLE_A).
The current problem is TABLE_A does not persist in memory on the load of TABLE_B. Table_B is merely the reselection of data from the same Oracle table as Table A used to display all data in a table box for the user and to allow selection of a single row from a list in that table box.
It would be much easier to merely ask for the key in an input box then load a single row into temp variables (as I did with the insert from a template) used in my INSERT statements... but this POC is to use the table box to select the row to UPDATE or DELETE.
Getting a single displayed row of data from a table box into an input box is the name of the game here and my attempts have failed. (Note: the user may not have "selected" the column if the primary key but might still see a single row).
My questions are these:
- Is there a command to make my initial loaded TABLE_A to persist when a connection and subsequent RELOAD instruction is given? A script command "PERSIST TABLE_A ON RELOAD" would be a helpful command to have for this scenario.
- Or, is there a way to populate all temporary column variables of a single selected row from the memory resident TABLE_A being displayed in the table box object on the screen at the time that single row is selected by the user without doing that assignment in a script while doing a RELOAD process?
In order to edit any temporary field for an UPDATE statement to be issued later, all temporary columns for that single row selection of TABLE_A in the table box must be populated by that single row selection.
- Can the Peek command be used to assign the temp variable somewhere outside of the RELOAD script and is there a cursor variable available to identify that single row being selected in memory resident TABLE_A of its table box?
I need to say, populate all my temp variables from that selected row.
Once done, I will attempt to do an UPDATE to the source table.
The DELETE ROW functionality would then be obvious to achieve because it's selection would be the same as the UPDATE row.
Thanks in advance,
Les
