Hello everyone

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.

Could anyone please guide me?

Thanks

give up now

this is not going to be possible

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

Hi,

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?

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?

Thanks again

in that case, you have nothing to do

those queries will all run unaltered in mysql, postgresql, and microsoft sql server (which is the database that uses TSQL)

well, except for the 2nd one, which won't run at all in any of them

oh, and the 3rd one, that one won't work in any of them either

well, except for the 2nd one, which won't run at all in any of them

oh, and the 3rd one, that one won't work in any of them either

why? Have I written something wrong?

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.

Sorry for being silly but is there anyway I can see a working example for it because I dont know much about databases.

yes, you have

if you could show the real queries, instead of that made up stuff, perhaps they don't have errors

but the ones you showed do have errors

These are almost all of the queries

query << "delete from " << MN << "import";

query << "load data local infile '" << impFile << "' into table " << MN << "import fields terminated by ','";

query << "select count(distinct SLIP), count(distinct SLP_ID) from " << MN << "import";

query << "select TRAD from " << MN << "import where SLIP =" << clslip;

query << "delete from " << MN << "import where DATE != '" << dated << "'";

query << "delete from " << MN << "import where TRD in ('E', 'F')";

query << "delete from " << MN << "import where STATUS = 'UR' or STATUS = 'UA' or STATUS = 'NC'";

query << "update " << MN << "import set SEQ_NO='000' where SEQ_NO=0";

query << "delete from " << MN << "import where STATUS='MS' AND TRD NOT IN ('B', 'K')";

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.

those are almost all okay

LOAD DATA INFILE, of course, is not a query but a command, and this one will need to be converted, as only mysql supports it

the use of "!=" as a comparison operator is non-standard, you'll want to use "<>" instead, which is standard

also, set SEQ_NO='000' where SEQ_NO=0 might not work in other databases as it relies on implicit conversion

Alright thanks a ton both of you

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.

Thanks