Comments (20)
If i use raw statements, why should i use SqlKata?
I'm using SqlKata to generate my dynamic queries...
from querybuilder.
Thanks, sadly there is no plan to add support for it in the near future, it would be great if you can help, either by making a pull request, or by giving a hint on what are the major differences, so we can implement one.
from querybuilder.
I'm writing Oracle compiler now. I don't know exact changes between pgsql and oracle, so I'm implementing only features I need. I will make PR when it will be ready.
@jonslo If you would like to cooperate on this, please contact me.
from querybuilder.
@tlaguz that would be awesome, these sections may contains difference
https://sqlkata.com/docs/where-string
https://sqlkata.com/docs/where-date
https://sqlkata.com/docs/limit
If you need any help please let me know
from querybuilder.
I've created WIP branch in my fork. You can find compiler class here: https://github.com/tlaguz/querybuilder/blob/oracle/QueryBuilder/Compilers/Oracle11gCompiler.cs
As you can see I've managed to do limit and offset proof of concept.
What do you think about making integration tests against real database? Simple statements are easy to test in unit tests, but limit with offset for example is getting messy.
db.Query("CUSTOMERS").Limit(5).Offset(10).Get()
produces:
SELECT * FROM (SELECT "a".*, ROWNUM r__ FROM (SELECT * FROM "CUSTOMERS") "a" WHERE ROWNUM <= 15) WHERE r__ > 10
IMO It should be also tested with some ORDER BY
and GROUP BY
statements to check if everything is compiling correctly. Should this case be tested in unit tests as well?
I created a docker container with Oracle 11 express and example database structure inside for my own testing purposes. I can commit this it if you like. It would be hard to automate these tests, but it eases programming a lot.
from querybuilder.
mutating the same queries lead to many issues before, especially in this case, an example would be when compiling the same query twice, am not sure if it's still a problem right now,
check the SqlServerCompiler (Legacy approach) i think it's similar
https://github.com/sqlkata/querybuilder/blob/master/QueryBuilder/Compilers/SqlServerCompiler.cs#L17
from querybuilder.
Thank you for advice. I will change my code.
I will need unique (in query scope) alias generator to support nested limited queries. The only place I found which is shared between query and its subqueries is Query
class itself, because it has AbstractQuery.Parent
variable. The problem is the Parent
var is not copied by Copy
function. Is this an expected behavior?
If it's not, I would propose something like this:
public abstract class AbstractQuery
{
protected AbstractQuery Parent;
public uint AliasCounter { get; protected set; }
public string GetUniqueAlias => Parent != null ? Parent.GetUniqueAlias : $"SqlKata_{AliasCounter++}__";
}
What would be the best solution?
from querybuilder.
While in practice these kind of query nesting should be avoided, but in fact it's possible, I prefer if we keep this to another issue, and try to find a solution for it later, since this will affect the SqlServer compiler also.
we can leave it for now, so we assume that nested limited queries are not supported at the moment.
one approach would be what you have suggested, another maybe using random generator, etc ...
regarding the Parent
prop, it was used in previous versions, and currently there is no clear usage for it, so yes maybe it will get removed in future release unless we found a proper use for it.
beside note, will searching I found that Oracle also support the standard FETCH NEXT OFFSET, like in sql server, so following the same strategy as Sql Server compiler will be a good fit here.
What do you think ?
from querybuilder.
beside note, will searching I found that Oracle also support the standard FETCH NEXT OFFSET, like in sql server, so following the same strategy as Sql Server compiler will be a good fit here.
Unfortunately this is supported since Oracle 12c. This is why I decided to name compiler class Oracle11gCompiler
to highlight database's target version.
There should be also 12c compiler, but 11g is my priority, since most of our clients won't upgrade their databases soon.
from querybuilder.
Any progress on this? How functional is the oracle compiler?
from querybuilder.
Coming back to this, I've pulled the fork and rebased/merged it with the trunk, written out a few unit tests for oracle11g and all seems to be fine. If I get round to finishing the tests, can I make a pull request? Or is the alias issue preventing this?
from querybuilder.
@mathijs-dumon thanks for the progress, @tlaguz do you have any input on this ?
from querybuilder.
#126 issue is still not solved. Oracle have different binding format than every dbms currently supported by the sqlkata, every supported dbms handles @...
and Oracle requires :...
, so there is no easy way to adapt existing code.
I can't tell if this is the last issue.
from querybuilder.
Could you make a pull request on a different branch ?
from querybuilder.
@mathijs-dumon @tlaguz We can safely close this issue now right ?
from querybuilder.
I'd say so, if there are issues, I'll likely commit fixes for them anyway.
from querybuilder.
That's great!
from querybuilder.
What about Oracle Sequences?
from querybuilder.
maybe using the new db.Statement method? (see #94)
I think it's not yet in the docs
from querybuilder.
maybe using the new db.Statement method? (see #94)
I think it's not yet in the docs
Actually it's https://sqlkata.com/docs/execution/fetching-records#execute-raw-statements
from querybuilder.
Related Issues (20)
- Oracle connection pooling with SqlKata
- Can you please include https://github.com/sqlkata/querybuilder/commit/f3d7c924f4d11c33056d7b002f9d5066dc856117 & re-publish 2.4.0 ?
- RobiniaDocs API Explorer
- Compile query with parameters for ODBC Connection
- Assigning NULL value to column with AsUpdate throws NotSupportedException HOT 1
- db access where day error No value given for one or more required parameters.
- [SQLServer] insert bool value #437 HOT 1
- Possible documentation error
- Distinct AsCount error
- Vulnerability in System.Text.RegularExpressions 4.3.0
- FromRaw can't translate list type parameter properly HOT 1
- how to use include HOT 2
- Question: Combining multiple Statements HOT 1
- Error on WhereContains HOT 1
- Getting Raw SQL from the package, without passing it to the database engine HOT 5
- Error when trying to insert into Always Encrypted column
- SqlServerCompiler Limit generates different query on compile compared to previous versions HOT 3
- Postgres over-escaping ? HOT 1
- `With` missing if defined in sub query HOT 2
- Use RetryProvider in SqlKata.Execution
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from querybuilder.