Database Interface

This is a discussion on Database Interface within the C++ Programming forums, part of the General Programming Boards category; I was thinking if I were to implement this instead of using an already existing library or ORM, how would ...

  1. #1
    Dae
    Dae is offline
    Deprecated Dae's Avatar
    Join Date
    Oct 2004
    Location
    Canada
    Posts
    1,034

    Smile Database Interface

    I was thinking if I were to implement this instead of using an already existing library or ORM, how would it turn out in C++.

    Unfortunately we don't have the luxury of creating new class members dynamically on the fly in C++ (good if you care about speed). So it looks like one libraries overload the << operator, normal query with variables in between, while another has complex syntax and iterators but more flexibility.

    I want a simple, understandable interface. Yet it needs to be flexible enough for all SQL operations (and thus multiple scope levels, recursion, etc.)

    So I ask, what are some decent ways of interfacing with databases you've seen in C++?

    I wonder if this, as usage, would be a decent direction:

    Code:
    Linker linker;
    
    Row r = linker
    .Select()
    	.Fields()
    		.Field("*")
    		.And()
    		.Count("some_category").As("all")
    	.End()
    	.From()
    		.Table("some_table")
    		.And()
    		.Table("another_table")
    	.End()
    	.Where()
    		.Field("some_field").EqualTo(10)
    		.Or()
    		.Where()
    			.Field("another_field").EqualTo(20)
    			.And()
    			.Field("this_field").EqualTo("abc")
    			.And()
    			.Where()
    				.Field("some_field").EqualTo(10)
    				.Or()
    				.Field("another_field").EqualTo(20)
    			.End()
    		.End()
    		.Or()
    		.Where()
    			.Select()
    				.Fields()
    					.Field("*")
    				.End()
    				.Where()
    					.Field("this_field").BiggerThan(50)
    				.End()
    			.End()
    		.End()
    	.End()		
    .Load();
    Row would store the query object, the query object would have pointers to the variables so it could be updated/saved/deleted.

    Reason I ask is I'm not familiar with existing methods, as I've normally used the low-level libraries and pure sql strings. As well as not wanting to waste more time on something that may be stupid.

    Thanks in advance. I know it might be a waste of a topic, but it can't hurt to ask.
    Warning: Have doubt in anything I post.

    GCC 4.5, Boost 1.40, Code::Blocks 8.02, Ubuntu 9.10 010001000110000101100101

  2. #2
    C++ Witch laserlight's Avatar
    Join Date
    Oct 2003
    Location
    Singapore
    Posts
    21,606
    Wouldn't "pure SQL strings" be easier to read, and easy to say, load from file?
    C + C++ Compiler: MinGW port of GCC
    Version Control System: Bazaar

    Look up a C++ Reference and learn How To Ask Questions The Smart Way

  3. #3
    Dae
    Dae is offline
    Deprecated Dae's Avatar
    Join Date
    Oct 2004
    Location
    Canada
    Posts
    1,034
    Quote Originally Posted by laserlight View Post
    Wouldn't "pure SQL strings" be easier to read, and easy to say, load from file?
    Yes, but that's the purpose of the .EqualTo(10) part. That 10 could be variable. A variable which could change (passed by pointer, not by value). Of course you could just use %1, %2, %3, etc. in your SQL strings (perhaps loaded from file). The interface would be designed to do both, of course. I'm just torn as to what's the best method in the long run, and if this structure is worth implementing or needs tweaks. Part of the advantage of this is also not needing to know the exact SQL syntax, or the differences between MySQL, MsSQL, Oracle, PostgresSQL, etc. and being able to switch between them without changes. You also have programming to check the validity of the query, so you don't publish bad queries (syntax or otherwise) - wasting your time or adding bugs (which was the main reason listed for some database libraries). Then again hardcoding SQL seems like bad oversight. Although you do need to know if you're simply doing an update, select, delete, etc., rows you're receiving, and what data they contain, so all in all abstracting SQL commands isn't going to increase flexibility VERY much as the C++ will usually need updates as well. It just reduces clutter for the most part. In which case you can do a simple #include.

    So yeah.. maybe trash it.
    Last edited by Dae; 03-25-2009 at 11:16 PM.
    Warning: Have doubt in anything I post.

    GCC 4.5, Boost 1.40, Code::Blocks 8.02, Ubuntu 9.10 010001000110000101100101

  4. #4
    C++ Witch laserlight's Avatar
    Join Date
    Oct 2003
    Location
    Singapore
    Posts
    21,606
    Quote Originally Posted by Dae
    Yes, but that's the purpose of the .EqualTo(10) part. That 10 could be variable. A variable which could change (passed by pointer, not by value). Of course you could just use %1, %2, %3, etc. in your SQL strings (perhaps loaded from file). The interface would be designed to do both, of course. I'm just torn as to what's the best method in the long run, and if this structure is worth implementing or needs tweaks.
    Have you looked into interfaces that allow for prepared statements? In such cases, placeholders exist in the SQL statement string, and then values and/or variables are bound to these placeholder parameters at runtime.
    C + C++ Compiler: MinGW port of GCC
    Version Control System: Bazaar

    Look up a C++ Reference and learn How To Ask Questions The Smart Way

  5. #5
    Registered User
    Join Date
    Sep 2004
    Location
    California
    Posts
    3,246
    Putting SQL in code is a bad idea. Use Stored Procedures.

  6. #6
    C++ Witch laserlight's Avatar
    Join Date
    Oct 2003
    Location
    Singapore
    Posts
    21,606
    Quote Originally Posted by bithub
    Putting SQL in code is a bad idea.
    Not necessarily; it depends on the situation.
    C + C++ Compiler: MinGW port of GCC
    Version Control System: Bazaar

    Look up a C++ Reference and learn How To Ask Questions The Smart Way

  7. #7
    Dae
    Dae is offline
    Deprecated Dae's Avatar
    Join Date
    Oct 2004
    Location
    Canada
    Posts
    1,034
    This is probably where the replies will stop. It'll look like I'm being stubborn, but I'm really just looking for database interface ideas, rather than ditching the idea entirely, unless there's a better argument.

    Quote Originally Posted by bithub View Post
    Putting SQL in code is a bad idea. Use Stored Procedures.
    I know putting SQL in code is a layer issue, but where you call the procedure it's essentially the same issue, and stored procedures are evil. I personally prefer coding SQL in C++ for testing, debugging, small projects, etc. and if I plan on switching database types. The fact is if you change a stored procedure, trigger, external query, hardcoded query, or whatever, you could break your application. Dynamic queries are at least a bit safer, and just as flexible. Infact you could wrap your queries in a separate file each inside its own function with placeholder arguments. Or you could code them in Lua and call them from C++. Either way I think choosing your interface wisely is warranted.

    Quote Originally Posted by laserlight View Post
    Have you looked into interfaces that allow for prepared statements? In such cases, placeholders exist in the SQL statement string, and then values and/or variables are bound to these placeholder parameters at runtime.
    Sorry that's what I meant by %1, %2, %3, etc. Placeholders.
    Warning: Have doubt in anything I post.

    GCC 4.5, Boost 1.40, Code::Blocks 8.02, Ubuntu 9.10 010001000110000101100101

  8. #8
    Registered User
    Join Date
    Sep 2004
    Location
    California
    Posts
    3,246
    I know putting SQL in code is a layer issue, but where you call the procedure it's essentially the same issue, and stored procedures are evil.
    For every one person you find that says stored procedures are bad, I bet I can find 1000 that say the opposite

    At my company, we have a massive C++ application that relies heavily on a database. We decided to go the route of prepared statements, and we have been regretting that decision for awhile now. Before we started using prepared statements, the SQL was hardcoded in the application which had its own set of problems. There's nothing uglier than a massive function full of ifs, elses, and switch statements which are used to build a SQL string.

    YMMV, but in my experience, stored procedures are the best way to go if you are using a stand-alone database. If you are using something embedded like sqlite, then I usually just hard-code the SQL in the application.

  9. #9
    Dae
    Dae is offline
    Deprecated Dae's Avatar
    Join Date
    Oct 2004
    Location
    Canada
    Posts
    1,034
    Quote Originally Posted by bithub View Post
    For every one person you find that says stored procedures are bad, I bet I can find 1000 that say the opposite
    You're probably right. At least a 1:100 ratio or more. I don't really mean to say they are bad, but I do agree with some of the arguments and that it depends on the situation. I think a library that covers both is best. I just want to know what the best usage would look like considering the ORM syntax limitations of C++ compared to other languages.

    Quote Originally Posted by bithub View Post
    There's nothing uglier than a massive function full of ifs, elses, and switch statements which are used to build a SQL string.
    Does it really matter? As long as it does what it's suppose to. It's abstracted, just like all of the ugly file system code. Also note it wouldn't be one massive function with the usage above. It could be either a bunch of conditional statements, or a bunch of node objects linked together (much like a GUI).

    Again, not trying to choose one or the other. Just looking at it from a syntax/features point of view.
    Warning: Have doubt in anything I post.

    GCC 4.5, Boost 1.40, Code::Blocks 8.02, Ubuntu 9.10 010001000110000101100101

  10. #10
    Registered User
    Join Date
    Sep 2004
    Location
    California
    Posts
    3,246
    Does it really matter?
    I believe it does. From a maintenance point of view, ugly code is harder to maintain. It also is more difficult to write unit tests which cover every conditional case. If this is just a personal project, then it's just a matter of personal preference. If other people are going to be reading the code (or maintaining it), then you have to take readability into account.

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. How to get RSSI value, send to sensor, sensor receive package, repackage it?
    By techissue2008 in forum Networking/Device Communication
    Replies: 1
    Last Post: 03-04-2009, 09:13 AM
  2. Database assignment is Killing me!
    By Boltrig in forum C Programming
    Replies: 2
    Last Post: 11-29-2007, 02:56 AM
  3. Creating a database
    By Shamino in forum Game Programming
    Replies: 19
    Last Post: 06-10-2007, 01:09 PM
  4. connecting database to the interface
    By Unregistered in forum C Programming
    Replies: 3
    Last Post: 02-24-2002, 12:42 AM
  5. Replies: 1
    Last Post: 10-09-2001, 10:20 PM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21