81–88 of 88 posts
Earlier quoted context omitted.
every one says "just use parameterized queries" but they don't handle arrays which makes the idea rather useless.
Use one that does? Or build it yourself? ARRAY[?, ?, ?, ?]
Earlier quoted context omitted.
every one says "just use parameterized queries" but they don't handle arrays which makes the idea rather useless.
Many database engines can handle arrays, or table-valued variables which are basically the same thing. Most ORMs will also abstract away arrays for you, so you as the developer never need to deal with escaping of data in arrays.
ORMs don't count. They're just editing the SQL.
Earlier quoted context omitted.
Use one that does? Or build it yourself? ARRAY[?, ?, ?, ?]
I do, but I feel like it defeats the purpose. In order to insert those ?s you have to parse the query, which is exactly what we're trying to avoid.
Why can’t you just generate the following SQL?
INSERT INTO foo VALUES (?, ARRAY[?,?,?], ?)
This is basically what everything else does when handing arrays, or other composite types like coordinates.
If the language or libraries are not suitable for doing this then the language or libraries are the problem, not the approach.
Earlier quoted context omitted.
I do, but I feel like it defeats the purpose. In order to insert those ?s you have to parse the query, which is exactly what we're trying to avoid.
Sorry, I’m not clear on why you need to parse the query? Imagine you want to INSERT (int, array[], int) into a table. Why can’t you just generate the following SQL? INSERT INTO foo VALUES (?, ARRAY[?,?,?], ?) Where param 1-3 are indexes 0-2 in the array? This is basically what everything else does when handing arrays, or other composite types like coordinates. If the language or libraries are not suitable for doing t…
Earlier quoted context omitted.
Sorry, I’m not clear on why you need to parse the query? Imagine you want to INSERT (int, array[], int) into a table. Why can’t you just generate the following SQL? INSERT INTO foo VALUES (?, ARRAY[?,?,?], ?) Where param 1-3 are indexes 0-2 in the array? This is basically what everything else does when handing arrays, or other composite types like coordinates. If the language or libraries are not suitable for doing t…
If you're using an ORM/SQL builder, sure. If you prefer writing raw SQL because ORMs are often quite limiting, then you have to write something like `INSERT INTO foo VALUES ?` and then you pass it `[1, [2,3,4], 5]` as params, which is mostly fine but you still have to parse out that "?" from the query. Why parse? Because what if you wrote 'VALUES "?"' now it's a string and shouldn't be replaced. It would be much nice…
Of course you could just do CAST(? AS …) or use something like “string_to_array(?)”. But that’s just working around not being able to compose queries.
Earlier quoted context omitted.
If you're using an ORM/SQL builder, sure. If you prefer writing raw SQL because ORMs are often quite limiting, then you have to write something like `INSERT INTO foo VALUES ?` and then you pass it `[1, [2,3,4], 5]` as params, which is mostly fine but you still have to parse out that "?" from the query. Why parse? Because what if you wrote 'VALUES "?"' now it's a string and shouldn't be replaced. It would be much nice…
I think your argument is “if I try hard enough to make what I want to do really hard, it will be really hard”. I agree. I’m not sure I buy your argument about ORMs being limiting, given your current… limitations. Of course you could just do CAST(? AS …) or use something like “string_to_array(?)”. But that’s just working around not being able to compose queries.
CAST() what? I apologize, the example was a bad. I don't want an array on the SQL side, I want to supply an array. Something more like
SELECT * FROM foo WHERE x=? AND y IN (?)
If I pass [1,[2,3]]
It should expand to
SELECT * FROM foo WHERE x=1 AND y IN (2,3)
Which means it actually needs to be written as
SELECT * FROM foo WHERE x=? AND y IN (?, ?)
And I'd have to pass [1,2,3].
At least in all the DB connectors I've seen. I mostly use MariaDB.
Earlier quoted context omitted.
I think your argument is “if I try hard enough to make what I want to do really hard, it will be really hard”. I agree. I’m not sure I buy your argument about ORMs being limiting, given your current… limitations. Of course you could just do CAST(? AS …) or use something like “string_to_array(?)”. But that’s just working around not being able to compose queries.
Some of my queries get quite complicated, but I'm good at writing SQL. Why would I want to mess around with an ORM trying to recreate the SQL I actually want? CAST() what? I apologize, the example was a bad. I don't want an array on the SQL side, I want to supply an array. Something more like SELECT * FROM foo WHERE x=? AND y IN (?) If I pass [1,[2,3]] It should expand to SELECT * FROM foo WHERE x=1 AND y IN (2,3) Wh…
Parameters supply only values, but the cases you’ve shown require expressions. Of course no database (or db connector) would work like that, it’s slightly nonsensical. In your specific example the cardinality of the IN clause is important to the plan.
But all in all, congratulations, you’ve come to reach the limits of using raw SQL with dynamic inputs. Your choices are now: build an ORM, use an ORM, or hack around this issue with code that will horrify the next person to work on it. shrug.
Earlier quoted context omitted.
Some of my queries get quite complicated, but I'm good at writing SQL. Why would I want to mess around with an ORM trying to recreate the SQL I actually want? CAST() what? I apologize, the example was a bad. I don't want an array on the SQL side, I want to supply an array. Something more like SELECT * FROM foo WHERE x=? AND y IN (?) If I pass [1,[2,3]] It should expand to SELECT * FROM foo WHERE x=1 AND y IN (2,3) Wh…
Oh, right, that’s the same problem with the same solution though. Parameters supply only values, but the cases you’ve shown require expressions. Of course no database (or db connector) would work like that, it’s slightly nonsensical. In your specific example the cardinality of the IN clause is important to the plan. But all in all, congratulations, you’ve come to reach the limits of using raw SQL with dynamic inputs.…