(LongRange Studio) Enhancement Recommendation

[edwardtn]

I am developing an application with many offline tables. When using LongRange Studio to maintain tables it is cumbersome when you want columns to be reordered...the order is important to me because it simplifies the insert with the method I am using to populate local data.

Could the Table Columns grid allow the user to either drag columns up or down, or provide the user with move up/move down options in the context menu? (There is already a Delete Row option.)

Please let me know if I am simply unaware of something, currently to add a column in the middle of the list I am renaming all columns - which is tricky because you cannot duplicate a column name.

Thanks,

Edward

[tsupartono]

Hi Edward,

1) LongRange Studio currently doesn't allow reordering of columns to simply the metadata update process.
When you change a column name, or reorder columns, these changes have to be reflected in the actual database on the client. LR can't simply recreate the database as the db might already contain data, so LR will have to alter the table structure. Reordering table columns is not a trivial task and requires a table to be dropped and re-created.

2) It's fine to rename columns as long as the table is new (has not been deployed to any device).
However, if you rename a column that already contains data, that data will remain in that renamed column, and what you think is a new column will end up with data from another column.

You mentioned that the order of the columns is important to you - can you provide more details so I can understand the situation & work out a solution for you?

[edwardtn]

Thanks Tony,

Now that I understand the issues better with column reordering...there doesn't seem to be an easy solution. I am simply creating a new application using a few devices for testing which I have cleared of local data when needing to change the database structure.

The ordering is important because I am downloading and inserting local data from a webroutine (within *JSON response). As long as my webroutine output matches the column ordering of the local table, building the SQL insert statement from the JSON object is seamless. I am actually inserting a fair amount of data on the device and this method has worked much better than the method used in the offline demos.

I really just wanted this feature because I am making frequent changes during the early prototyping of the local database....going forward I would expect to not be making changes to column ordering. I understand that the Studio IDE would not be aware if a device had inserted data into a table or not, and it would be complicated and not worth the trouble to drop and add a table (like VL does with $$ tables).

Regards,

Edward

[tsupartono]

Hi Edward,
When constructing your JSON response, what do you use as the keys? If you use the column names, you can use those in your SQL INSERT statement so it would not depend on the intrinsic order of the columns.
E.g.:

{
"EMPNO": "10001"
"FIRSTNAME:": "John"
}

Then in your INSERT statement, you could do:
INSERT INTO MYTABLE(FIRSTNAME, EMPNO) VALUES("John", "10001")

[edwardtn]

The LANSA webroutine outputs the JSON in a format where the list header (with column descriptions) are in a separate object (lists.list.header) than the list entries (lists.list.entries)...

I process the data in this way, getting 50 entries at a time (which gives very good performance):

1. Create beginning of insert - (INSERT INTO TABLE(Column1, Column2...) Values(

2. Iterate through the entries using jQuery each(), building value string - (Entry1Value1, Entry1Value2), (Entry2Value1, Entry2Value2)

3. Finish insert once 50 entries or entries.length is reached

I am creating the beginning of the insert (TABLE(Column1, Column2...)) dynamically using sql: "select sql from sqlite_master where type = 'table' and tbl_name = '" + tableName + "'" and parsing the column names with regex: var columnNames = stringValue.replace(/^[^\(]+\(([^\)]+)\)/g, '$1').replace(/ [^,]+/g, '').split(',');

Again, this works great and is reusable, I just must ensure that the list entries in my Lansa webroutine exactly match my table columns...and I could change either....but often I choose to change the table because the app is in early development and because I sometimes choose to just have the columns in a more logical sequence.

I attached a screenshot...this method performs very well for downloading and inserting a good deal of records.

[tsupartono]

Thanks for sharing the details - now I can understand your problem (unfortunately just as you said, there is currently no easy solution).

It's a great way though to do the record download that way.