Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Dec 2010
    Posts
    13

    Unanswered: mySql to Postgres

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gulhk View Post
    ...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.
    give up now

    this is not going to be possible

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Dec 2010
    Posts
    13
    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

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  6. #6
    Join Date
    Dec 2010
    Posts
    13
    HTML Code:
    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?

    Thanks again

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gulhk View Post
    these are the only three queries used in the application
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2010
    Posts
    13
    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?

  9. #9
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  10. #10
    Join Date
    Dec 2010
    Posts
    13
    Sorry for being silly but is there anyway I can see a working example for it because I dont know much about databases.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gulhk View Post
    why? Have I written something wrong?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Dec 2010
    Posts
    13
    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')";

  13. #13
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gulhk View Post
    These are almost all of the queries
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Dec 2010
    Posts
    13
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •