CODEWITHSUNDEEP
sqlite
karruma
karruma

Reputation: 768

Sqlite3 - passing parameters to prepared statements

I have prepared statement, which I would like to test from command line tool without "manually" passing the parameters.

Example sql:

 SELECT * FROM TABLE WHERE X = ? AND Y = ?

Is there a way of passing the parameters so the SQL can be executed with given values with something like: SQL bind(a,b,c) ?

Thanks,

Upvotes: 3

Views: 4116

Answers (1)

anddev84
anddev84

Reputation: 1483

EDIT: Not sure where you're running this, but here's an Android solution for those interested:

If I understand your question correctly, you want to pass values from the command line into a SQL statement to test out the results. It's a bit of work, but doable:

There is functionality within adb to send Intents to a specific Activity, which could include Extras. If you will always have a fixed number of arguments, you could potentially program an Activity to be started with an Intent containing Extras and pass it from the command line. Enter the command (cue music): adb shell am start. For more on this command, see the documentation or this tutorial

Here's some sample code:

public class MainActivity extends Activity {

    private final String EXTRA1 = "com.example.sample.EXTRA1";
    private final String EXTRA2 = "com.example.sample.EXTRA2";

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        ...

        String qparam1 = null;
        String qparam2 = null;
        Intent calledBy = getIntent();

        if (calledBy.hasExtra(EXTRA1)) qparam1 = calledBy.getStringExtra(EXTRA1);
        if (calledBy.hasExtra(EXTRA2)) qparam2 = calledBy.getStringExtra(EXTRA2);
        ...

At this point you now have a couple of options, since you have your parameters, you can either run a normal getContentResolver().query(), create the selection and pass in the parameters as selection args:

String selection = "WHERE x=? AND Y=?";
String[] args = new String[]{qparam1, qparam2};
getContentResolver().query(uri, projection, selection, args, null);

(beware doing it this way if you have int or long args. See this article.)

You can also choose to create a precompiled statement. Some precompiled statements will only give you a 1x1 result, so it might be limiting, depending on the output you're expecting.

SQLiteDatabase db; // assume already initialized
SQLiteStatement statement = db.compileStatement("SELECT * FROM table WHERE x=? AND y=?");

statement.bindAllArgsAsStrings(new String[]{qparam1, qparam2});
// now run some kind of execute command, as per SQLiteStatement documentation

SQLiteStatement documentation

Phew. Ok finally, using adb shell am start to send the data. As per the doc, you can do something like the following

> adb shell am start -n com.example.sample/.MainActivity -e com.example.sample.EXTRA1 first -e com.example.sample.EXTRA2 second

If all goes per plan, it should start your Activity, pass in the extras into your query, and run it. Then all that's left, depending on what your output is, is figuring out where to put the output, probably a file that you could then retrieve later.

Best of luck!

Upvotes: 1

Related Questions