vfafou Well-Known Member Licensed User Longtime User Mar 21, 2016 #1 Hello! I'm using parameterised queries since I've started writing with B4X. These days I'm writing an expansion to one of my projects and I have been fallen to the following case: Code: Copy SQLQuery = "SELECT * FROM sometable " SQLQuery = SQLQuery & "WHERE field1 = ? " SQLQuery = SQLQuery & "AND field2 = ? " if vSomeVariable = "X" then SQLQuery1 = " AND field3 in (SELECT f3 FROM sometable2 WHERE f1 = ? AND f2 = ?)" end if SQLQuery = SQLQuery & SQLQuery1 ... How to manipulate the ??s of the subquery since it is not always necessary? I have to mention that I'm talking for a far more complex query structure than my example! Would you think that it will be better if I write it as a stored function to the database? Thank you in advance! Last edited: Mar 22, 2016
Hello! I'm using parameterised queries since I've started writing with B4X. These days I'm writing an expansion to one of my projects and I have been fallen to the following case: Code: Copy SQLQuery = "SELECT * FROM sometable " SQLQuery = SQLQuery & "WHERE field1 = ? " SQLQuery = SQLQuery & "AND field2 = ? " if vSomeVariable = "X" then SQLQuery1 = " AND field3 in (SELECT f3 FROM sometable2 WHERE f1 = ? AND f2 = ?)" end if SQLQuery = SQLQuery & SQLQuery1 ... How to manipulate the ??s of the subquery since it is not always necessary? I have to mention that I'm talking for a far more complex query structure than my example! Would you think that it will be better if I write it as a stored function to the database? Thank you in advance!
Erel B4X founder Staff member Licensed User Longtime User Mar 22, 2016 #2 vfafou said: Hello Erel! Click to expand... This is a community forum. Please don't limit your questions to a single member. Upvote 0
vfafou said: Hello Erel! Click to expand... This is a community forum. Please don't limit your questions to a single member.
KMatle Expert Licensed User Longtime User Mar 22, 2016 #3 What kind of db do you use? MySql? Normally you design a db and tables to fit your needs not "playing" with x kinds of different queries (which is a design mistake). Additionally: the performance can't be good, often with a slight change in the table structure you can reach it very easy. Could you post the table structure and what you are trying to do wth it? Upvote 0
What kind of db do you use? MySql? Normally you design a db and tables to fit your needs not "playing" with x kinds of different queries (which is a design mistake). Additionally: the performance can't be good, often with a slight change in the table structure you can reach it very easy. Could you post the table structure and what you are trying to do wth it?
B billzhan Active Member Licensed User Longtime User Mar 22, 2016 #4 Store the queries in a map (key-value) . Code: Copy ' RDC frame work config.properties sql.insert_animal=INSERT INTO animals ( name ,doub) VALUES (? , ? ) sql.select_animal=SELECT name, doub FROM animals WHERE name = ? Upvote 0
Store the queries in a map (key-value) . Code: Copy ' RDC frame work config.properties sql.insert_animal=INSERT INTO animals ( name ,doub) VALUES (? , ? ) sql.select_animal=SELECT name, doub FROM animals WHERE name = ?
