React Native sqlite issues selecting where column is NULL-CodePudding

I have React Native project where I am using react-native-sqlite-store (npm i react-native-sqlite-storage). The issue is that I get no results when I try the follow query show below. I need to be able to select all rows where the columns have exact match with the values. I need to also be able to select where the value could be NULL. With the correct code I am not able to get results however if I remove the midiGroup field from the query I get results. Not sure how to fix this.

Basically I need to be able to check even if a column has NULL. In React Native the message?.midiGroup value is null

tx.executeSql(
  "SELECT * FROM MidiMap WHERE midiType= ? AND midiNote= ? AND midiChannel= ? AND midiVelocity= ? AND midiGroup= ?;",
  [
    message?.midiType, // <---- noteOn
    message?.midiNote, // <---- 60
    message?.midiChannel, // <---- 0
    message?.midiVelocity, // <---- 64
    message?.midiGroup, // <---- for this query midiGroup is null
  ],
  (sqlTx, res) => {
    console.log(activeProfile, message?.midiType);
    let len = res.rows.length;

    // Check for items here
    if (len > 0) {
      for (let i = 0; i < len; i  ) {
        let item = res.rows.item(i);
        // Trigger here
        console.log(item);
      }
    }
  },
  (error) => {
    console.log(`Error checking results for midi triggering: ${error.message}`);
  }
);

CodePudding user response：

Use the operator IS to make comparisons against null.

Depending on your requirement, your query could be written as:

SELECT * 
FROM MidiMap 
WHERE midiType = ? 
  AND midiNote = ? 
  AND midiChannel = ? 
  AND midiVelocity = ? 
  AND midiGroup IS ?;

or:

SELECT * 
FROM MidiMap 
WHERE midiType = ? 
  AND midiNote = ? 
  AND midiChannel = ? 
  AND midiVelocity = ? 
  AND (midiGroup = ? OR midiGroup IS NULL);

CodePudding user response：

If you want to check if a column is null, simply do

SELECT * FROM MidiMap WHERE midiType= ? AND midiNote= ? AND midiChannel= ? AND midiVelocity= ? AND midiGroup= NULL;