Dynamically create SQL queries

Queries and Resources
1

I am a novice to I really dont know how to do this correctly I have a database with columns with country names and in the GUI I have a multiselect so when I select the countries, and then send the entry to the database it would update the corresponding countries. I can generate the SQL but it is asking me to "Disable converting queries to prepared statements retool" which i don't think is a good idea if I understand what its asking me. So how could I run this dynamic query see below

function displayMultiselectListboxValues() {
    const values = multiselectListbox1.value;
    console.log(values);
    return values;
}

// Get the selected countries
const selectedCountries = displayMultiselectListboxValues();

// List of all possible country columns
const allCountries = ["Austria", "Belgium", "Bulgaria", "Croatia", "Cyprus", "Czech Republic", "Denmark", "Estonia", "Finland", "France", "Germany", "Greece", "Hungary", "Ireland", "Italy", "Latvia", "Lithuania", "Luxembourg", "Malta", "Netherlands", "Poland", "Portugal", "Romania", "Slovakia", "Slovenia", "Spain", "Sweden"];

// Generate SQL for country columns
const countryColumns = allCountries.map(country => `"${country}"`).join(', ');
const countryValues = allCountries.map(country => selectedCountries.includes(country) ? 'TRUE' : 'FALSE').join(', ');

console.log(countryColumns); // Example output: "Austria, Belgium, Bulgaria, ..."
console.log(countryValues);  // Example output: "TRUE, FALSE, TRUE, ..."

const productCode = textInput1.value;
const productName = textInput2.value;
const clientName = text2.value;

// Construct the full SQL query
const sqlQuery = `
INSERT INTO "CLFI-Products" (product_code, product_name, clientname, ${countryColumns})
VALUES ('${productCode}', '${productName}', '${clientName}', ${countryValues});
`;

console.log(sqlQuery);

// Return the SQL query string
return sqlQuery;
2

Instead of having a column for every country, have just one column “country” and create multiple records, one for each country. You will likely need to make some updates regarding how you have been accessing this data, but it is a more maintainable structure for a DB table.

1 Like
3

Hahaha this is so weird I literally came to that conclusion and made the modification without even reading this. Thanks anyway it makes way more sense