I am a recent graduate and have recently joined my job. I have been assigned a task of introducing a layer between the c++ program and its connectivity with database which would translate a query to any given database which in this case is postgres. We are using mysql++ and I have to write the code such that it works with any database tool.
I am not sure what you are asking for here? Are you saying you need something to dynamically connect to any database and issue an SQL or are you saying that an SQL statement must be executable on any database.
The first possibility above, you could use ODBC as the client and write your application using this client. Another possibility would be to look at Oracle's database gateway solutions which from one database allows you to connect to multiple different databases to issue SQL statements using ODBC.
If it is the second you are looking for then this will be extremely complex because each database server will have its own support or SQL along with their own customizations. To give an example, in Oracle to concatenate two strings you would do string1 || string2, in MySQL this would be CONCAT(string1, string2). You would need to find every difference in every database and do the necessary. This would mean parsing each of the SQL's in advance, then perform the necessary conversions of the SQL statements and then send these to be executed. This parsing and converting prior to executing each statement will add a big performance overhead.
Also to consider is the table types in MySQL and the support of transactions. If for example, the table does not support transactions but you want the SQL statements to be transactional you will need to write the transactional component to make sure it works correctly.
As Rudy has said, this is a huge undertaking with a lot of complexities involved. I would suggest narrowing the scope of the work and start there rather than taking a swing at this generic solution.
Thank you so much it-iss.com for your detailed reply. That's so very nice of you. I have basically done just few course assignments during my studies and it is the first time I am working in mysql apart from studies.
The queries used in current C++ code are the basic ones. These are 1) select 2) delete 3) update. What if it was just postgres? Would you suggest any solution for just one type of database and these basic queries?
Many thanks once again for your time and detailed reply
I think you should look more closely at what you are trying to achieve. If you narrow the scope to only the queries that already exist and have these translated for other databases. I think devising a generic solution will be too big. Databases only do SELECTs, INSERTs, UPDATEs and DELETEs so that is not really narrowing the scope.
I suppose you should go back and ask yourself (and your manager) what are you trying to achieve and why? I think answering this question will give a better idea of what will be needed by your solution. Focus on keeping the solution simple, making a solution complex will overrun by years and be extremely difficult to support and for what gain?
transition of the system from MySQL to postgres, better would be to make the database connection abstract completely, so if a client decides to buy it and connect it to an oracle database, we don't care.
These are the exact wordings of my manager and I discussed it with my colleague (my manager wasn't interested in discussing it with me and he seldomly comes to office) and this is what my colleague explained to me that I have been asked to create a Data Access Layer between the C++ application and the database it is interacting with. The program currently uses simple queries like
1) select 'column' from 'some table' where 'something = something AND something = something'
2) delete 'column' from 'some table' where 'something' = something'
3) update 'something'
there are no joins etc anywhere involved in the current application and these are the only three queries used in the application and I have to convert it to postgres at the moment.
I hope I have explained it well. And one more thing, is there any thing called TSql?
I would have a look into using ODBC client connection, this way the database queries can be executed by simply changing the client software and the connection string to the database but the remainder of the code will remain as it is.
Looking at your queries all will work on any type of database except 1, the "load data ...". This is specific to MySQL and you will need to find a different solution for each database that you will have to support.
But like I said I have no experience in databases but only OO languages so could you please tell me how do I go about it, the solution? i-e create a Data Access Layer between the C++ application and the database it is interacting with. . Do I have to create a new class? any changes to the database connectivity part of the code? etc. Please guide.