DBO with MS SQL backend - my first test crashes
Added by Boris Grinac 4 months ago
Hello, I am trying to write my first program with DBO. Very simple program, just connects to database and tries simple select query. But it crashes with unhandled exception.
Here is my results:
SQL connection works, connection is moved to session and this looks good. I have ODBC tracing log and there is no errors in the log.
When I try to send query like this:
int r = session.query("select ID from Source").where("Name = ?").bind(name);
This query is not sent to database, according to SQL.log, and program ends with exception in file Query_Impl.h line 160, here is the line:
auto statement = this->session_->getOrPrepareStatement(sql);
The sql string parameter looks good, correctly formed and I have tested this query directly in SSMS, this query works.
And so the question is why I have this exception in this simple program. Actually the program follows the "hand on" DBO manual.
My environment is Visual Studio 2022 and I use pre-compiled installation of WT, "Wt 4.10.2 msvs2022 x64"
Help me if you can.
Boris Grinac
Replies (5)
RE: DBO with MS SQL backend - my first test crashes - Added by Matthias Van Ceulebroeck 4 months ago
Hello Boris,
I suspect that you are missing a couple parameters to connect to your MS SQL server. The example specifies only the name of the database for SQLite3 (and we use this since it's a very easy thing to set up).
For MS SQL (and any other backend), you will need to pass more information to the connection constructor, see the documentation.
If this is not the issue, could you provide me with the source of the program you are trying to run?
Best regards,
Matthias
RE: DBO with MS SQL backend - my first test crashes - Added by Boris Grinac 4 months ago
Hello Matthias,
yes, I know SQL Server requires connection string and this connection string is a rocket science by itself. But I managed to prepare the file based DSN, just one line of parameters separated by semi colons. And as I mentioned, the connection works. I use OBBC tracing to verity. Anyhow, included is my program, please download from google drive.
https://drive.google.com/file/d/1aLHuFhU_fDquIyQNqo-MZWiSuzyCjk3r/view?usp=sharing
RE: DBO with MS SQL backend - my first test crashes - Added by Matthias Van Ceulebroeck 4 months ago
Hello Boris,
my apologies. I interpreted your first point such that I believed you were saying that you tested a manual (via terminal/cmd) connection, not the connection the application sets up.
I see in your code there is a comment that states connect itself already throws an exception. Doesn't the error message give you an indication as to what is going wrong?
If the connect() hasn't succeeded, it's normal that any subsequent query would fail as well.
However, since you say that the error only occurs on find, I think it may simply be a missing transaction.
Can you make sure that before you perform the actual query, you open a transaction in the same scope?
i.e.
dbo::Transaction transaction(session);
dbo::ptr<ion_source> s = session.query<int>("select ID from Source").where("Name = ?").bind(name)
Best,
Matthias
RE: DBO with MS SQL backend - my first test crashes - Added by Boris Grinac 4 months ago
Dear Matthias,
thanks so much! Now I am so happy! Actually, my colleague was fighting with Dbo last year, never made it work and ended up with nanodbc.
I have read the manual again, section 4. Querying objects. There is no mention there of transaction. Transactions are mentioned before, but since our queries are all select queries and are read only, I thought we do not need to start transactions for select queries. Probably transactions are needed by design of the library.
Maybe it will be a good idea to add the line of code that you suggested to me to the examples in section 4. Querying objects.
Many thanks!
Boris
RE: DBO with MS SQL backend - my first test crashes - Added by Matthias Van Ceulebroeck 4 months ago
Hi Boris,
section 3 mentions that:
All database operations happen within a transaction
But yes, the code should be explicit and correct. I have a documentation rework in the works, and will add it there.
Best regards,
Matthias