Query returing multiple rows - Array

[medida]

Hello.

I'm succesfully running the following code on Android - but on iOS it returns the columns from the query in a different order - making a process break.

this.myDB.query(sql, sqlParams).then(res => { resolve(res.values); });

How can i force the process to order the returned data in a order definied in the SELECT query.

otherwise i'm going to have to manage the next insert manally rather than passing an array and inserting multiple rows at once :(

[jepiqueau]

@medida thanks for using the plug-in can you give me the query and the table schema so I can reproduce easily the issue. Which framework are you using? And which version of the plugin

[medida]

hi I'm using the latest version of the plugin - v3.2.4 I'm using ionic v5 / capacitor and working on both windows ( for android ) and a mac (for the ios)

sadly my project is large and i currently don't have time to create a samll one.... But On any table call i do using the standard select statement - with columns defined. e.g. this.appDatabaseService.database_query('select "" AS form_processitemid, "' + this.form_create.form_processid + '" AS form_processid, formitemid, form_parent_formitemid, form_label, form_datatype, form_data, form_ordernumber, list_id, list_type, form_required, "' + this.form_create.crud_datetime + '" as crud_datetime from client_forms_items where formid = ? and ifnull(_item_deleted,"false") <> "true"', [this.form_create.formid]).then((data: any) => {

This statement returns 5 rows of data. On android these are all in the same format.

{values: Array(10)} values: Array(10) 0: {form_processitemid: '02558C70-82D7-44BB-BF2D-1B809B9C90A2', form_processid: '47872BCF-C63A-43EA-A19E-78A054B68378', formitemid: 'D5870233-E259-4EEB-C679-4F0F6D29ACFD', form_parent_formitemid: '...', form_label: 'Manager completing this form', …} 1: {form_processitemid: 'DF71A4BB-AD4F-4C58-BFC4-E5A57E3F7F0D', form_processid: '47872BCF-C63A-43EA-A19E-78A054B68378', formitemid: 'BBCBF4CB-75D9-45A2-F96C-06FCF703E525', form_parent_formitemid: '...', form_label: 'Employee Name (enter manually)', …} 2: {form_processitemid: 'CACB9812-B434-4903-AFC2-09518858ED02', form_processid: '47872BCF-C63A-43EA-A19E-78A054B68378', formitemid: 'A3D26C39-1460-4CF4-9053-F46E4B67DF4B', form_parent_formitemid: '...', form_label: 'Date', …} 3: {form_processitemid: 'EF2E4D04-F822-4CE3-8F6F-F21EBA023FCA', form_processid: '47872BCF-C63A-43EA-A19E-78A054B68378', formitemid: '61DB4521-0EA2-4949-BFC3-CBA03BF0438E', form_parent_formitemid: '...', form_label: 'Site ', …} 4: {form_processitemid: '088C0FDE-74E6-4666-BFB3-EE65CCE9CB99', form_processid: '47872BCF-C63A-43EA-A19E-78A054B68378', formitemid: '81D480D2-0011-498C-F697-0E86407AAE76', form_parent_formitemid: '...', form_label: 'Manager ', …} 5: {form_processitemid: '827FFBDF-AE16-43A7-AEF4-79EEC6851361', form_processid: '47872BCF-C63A-43EA-A19E-78A054B68378', formitemid: '79A8F5D8-168C-4A72-C54D-8170BAA0A974', form_parent_formitemid: '...', form_label: 'Signature ', …} 6: {form_processitemid: '2F5D91D3-178E-4D98-B1AB-2BB00DD75182', form_processid: '47872BCF-C63A-43EA-A19E-78A054B68378', formitemid: '549676B8-6C4C-46BF-9405-31A1B6750BEF', form_parent_formitemid: '...', form_label: 'Employee Name (select)', …} 7: {form_processitemid: 'E71CB993-7D22-4E2A-BD38-F797D3C57BCB', form_processid: '47872BCF-C63A-43EA-A19E-78A054B68378', formitemid: '6BB52AEB-3083-4B1E-8CFC-F2E63D009396', form_parent_formitemid: '...', form_label: 'KGB Site', …} 8: {form_processitemid: 'EE169C16-016B-4C1D-9B70-F8A67A93DC34', form_processid: '47872BCF-C63A-43EA-A19E-78A054B68378', formitemid: '7736D9B1-4F92-4318-8FF9-34EF8171DF16', form_parent_formitemid: '...', form_label: 'Repeating Block', …} 9: {form_processitemid: 'C413C751-0329-472F-94D8-EFE2EF9D2EAB', form_processid: '47872BCF-C63A-43EA-A19E-78A054B68378', formitemid: 'DA9740B6-7B81-4F90-8AF7-74AD68378B38', form_parent_formitemid: '...', form_label: 'Static Text Block', …}

ON my mac - which is running the same code and returns the data as:

0 {form_data: "", form_value: null, form_label: "Employee Name (enter manually)", form_datatype: "string", _file_count: 0, …} 1 {form_value: null, form_required: "true", formitemid: "A3D26C39-1460-4CF4-9053-F46E4B67DF4B", form_processitemid: "0B044837-E862-4D4C-BE59-E25E9D656374", crud_datetime: null, …} 2 {form_value: null, _selected: null, crud_datetime: null, form_required: "true", _item_deleted: null, …} 3 {formitemid: "D5870233-E259-4EEB-C679-4F0F6D29ACFD", _item_deleted: null, form_required: "false", _selected: null, form_processid: "DE447A34-E7DB-42D5-BA82-637F5D16F8E8", …} 4 {_file_count: 0, _selected: null, form_data: "", formitemid: "81D480D2-0011-498C-F697-0E86407AAE76", form_datatype: "string", …}

Whilst writing this i have also noticed that my query is returning fewer records :( So this issue may be the cause of it.... I've yet to get through that.

[medida]

Ok - so i have gone though the process and there is definaly something different in the way my pject handles objects from apple to android.

I tried making an interface for the array - but on iOS that made no difference and the debugging output from safair still showed the object / array in a random order for each row.

I gave got round the whole problem by re-writing my table insert process which now manages each row as a uniue item rather than dealing with the entire array ( as a fixed format).

Thanks for the plugin.. I still get a toJSON error when using it iOS - but it does not impaxct on anything.

[jepiqueau]

@medida could be helpful for the developer that you share at least the table format and the lines of code where you insert your data the one failing and the one working so I can reproduce it anD also look at what is the issue with exportToJson. Thanks for taking the time sure it will be helpful

[medida]

hi

Yes - i'll sort it over the next few days - got a very rush job on as an OLD app is no longer working with iOS 15 becuase of the webview chnages... SO i have to roll out a new version which has been worked on for a while.

Thanks again.

[jepiqueau]

@medida ok good luck

[mobilemarines]

Hi. I also encountered this issue with the version 3.4.1. Is there already an estimation in which version a fix for this problem will be provided?

[jepiqueau]

@mobilemarines can you share à code example which reproduce the isse

[jepiqueau]

@mobilemarines i mean the issue is on Android, iOS or electron.

[mobilemarines]

@jepiqueau I only encountered the issue on iOS.

[medida]

@mobilemarines i just got round the issue. I was not concered with the data order from the select statement - other than i was not checking it for the insert statement later on.

From my select statements i call the columns correctly using there names. For the inserts i was using the JSON in the format provided and then just builing the SQL - now i loop through the table format in question and rebuild the entire SQL statement.. slightly longer but no more problems

[jepiqueau]

@mobilemarines @medida if you could share an simple example reproducing the issue including the way you create the table, fiil the data and query would be a good start to fix the issue

[medida]

An example is the code at the top of the his page...

and the way i fixed the insert issue was..

` tableData.forEach(row => {

  let _row = row;

  let _values = [];
  let rowValues = '';
  let rowArray = [];

  ///
  // columns starting with _ can be toggled to be either included or not
  // columns starting with _i_ will always be excluded
  //
  ///

  if (hasHeader === false) {
    // tidy up data ready for SQL statements
    for (let col in _row) {
      if ((col.indexOf('_') !== 0 || includeHiddenColumn === true) && col.indexOf('_i_') !== 0) { // ignore columns starting with _ unless overriden AND _i_
        _fields.push(col);

      }
    }
    //this.logging.log(_fields);

    sqlStatement = 'INSERT OR REPLACE INTO ' + tableName;
    sqlStatement += ' (' + _fields.map(x => x).join(",") + ' )';
    sqlStatement += ' Values (' + _fields.map(x => '?').join(",") + ' )';

    hasHeader = true;
  }

  _fields.map(x => {
    _values.push(this.sanitiseForSql(_row[x]));
  });

  if (count >= this.DEFAULT_BATCH_INSERT_SIZE) {
    // reset values - and start a new BLOCK
    sqlArray.push(sqlBlock);
    sqlBlock = { statement: '', values: [] };
    count = 0; // reset counter

  }

  if (count === 0) {
    sqlBlock.statement = sqlStatement;
  }

  rowArray = _values;

  sqlBlock.values.push(rowArray); // push row into the BLOCK values

  count++;

});`

is the section in the middle which is the main part

sqlStatement = 'INSERT OR REPLACE INTO ' + tableName; sqlStatement += ' (' + _fields.map(x => x).join(",") + ' )'; sqlStatement += ' Values (' + _fields.map(x => '?').join(",") + ' )';

this creates my INSERT statement using the table layout and then build the SQL statement using the fields from that table in the table column order - So making sure that my SQL statement is always written correctly no mater how i pass the data to it.

[jepiqueau]

@medida Were your table and data imported from a JSON Object if yes can you provide the JSON Object @mobilemarines Can you provide a replicated code showing the error

[jepiqueau]

@medida @mobilemarines i could not fix something that i cannot reproduce. @medida you said An example is the code at the top of the his page... but i do not see any code at the top of this page.

[medida]

@medida @mobilemarines i could not fix something that i cannot reproduce. @medida you said An example is the code at the top of the his page... but i do not see any code at the top of this page.

This is the exact code but its the same as the example at the top

this.appDatabaseService.database_query('Select client_forms.*, substr("00000000"|| ifnull((select max(form_ref_id) + 1 from Client_Forms_Process ),1), -8, 8) as form_ref_id_code , (select count(*) from client_forms_items where client_forms_items.formid = client_forms.formid) as form_items from client_forms where ifnull(client_forms._item_deleted, "false") <> "true" ' + where_clause + ' order by form_name limit 10 OFFSET ?', [this.searchPage_offset]).then((val: any) => {

My Select statement does not list the columns from the table but does a "select *" The issue was that it was different from ANDROID to IOS. I just worked my round the problem rather than worrying. The solution i came up with prevents any issues in the future with table changes as well

[mobilemarines]

@jepiqueau Yeah. I´m currently working on a small demo project. Will send you the link when it´s done.

[jepiqueau]

@mobilemarines OK thanks that will help me to fix it

[mobilemarines]

@jepiqueau Pushed a small demo project here: https://github.com/mobilemarines/simple-sqlite-demo. On the start page, I stringified the returned values into separate divs for a better overview. I hope that helps to understand/debug the issue.

[jepiqueau]

@mobilemarines thanks for the small demo project i will have a look and come back to you

[mobilemarines]

@jepiqueau Any news yet?

[jepiqueau]

@mobilemarines not yet i work all yesterday on without finding a solution . This is due to the use of the dictionary to get the result of the query. I have to use an array instead which has an impact in all the code. So it will require quite some time. Sorry for this it will come but i cannot yet fix à date for it.

[jepiqueau]

@medida @mobilemarines After so many trials, nothing work.

In fact reading the Capacitor documentation for plugin iOS Returning Data Back

The resolve() method of CAPPluginCall takes a dictionary and supports JSON-serializable data types.

and according to the Apple doc

A Dictionary is a generic type that manages an unordered collection of key-value pairs.

So Basically you have to deal with this in your app by recreating a json object from the return json object.

In Android and javascript you deal with json object directly

[mobilemarines]

@jepiqueau Alright, then we need to take care of that on our own. Thanks for your help.

[jepiqueau]

@medida @mobilemarines this has been fixed in 3.4.2-5

[medida]

thanks - I'll try it out when i next work on that project :)