Elegant way of finding if column exists in database table with MQL5's function

[Martin Bittencourt]

Hi,

I need a function that will check if my tables have certain columns and, if not, add them. The second part is fine, but how can I do the check? The Database functions include one about the existence of a table, but not a column of it. I did some search on the internet and of all the possible solutions, the only one I managed to make it work was to do a SELECT clause on the column I want to check and, if it returns an error, I understand it isn't there. But this solution is far from elegant, giving a clear error message to the user like a problem happened (what, after all, is correct since I tried to SELECT from a table that doesn't exists):

And it even gives a generic error code... So is there a more "elegant" solution? I tried options such as PRAGMA table_info(tTrailing.value)" but it didn't work. Another option I found was to create a new table with all the columns and a temporary name, move all the data from the existing table, delete it and rename the new table with the correct name, but that seems a gigantic workaround :| It seems it would only be justified if I first notice some column is missing, but then again I would need a way to check that.

So any better ideas would be appreciated!

[Martin Bittencourt]

Martin Bittencourt:

Hi,

I need a function that will check if my tables have certain columns and, if not, add them. The second part is fine, but how can I do the check? The Database functions include one about the existence of a table, but not a column of it. I did some search on the internet and of all the possible solutions, the only one I managed to make it work was to do a SELECT clause on the column I want to check and, if it returns an error, I understand it isn't there. But this solution is far from elegant, giving a clear error message to the user like a problem happened (what, after all, is correct since I tried to SELECT from a table that doesn't exists):

And it even gives a generic error code... So is there a more "elegant" solution? I tried options such as PRAGMA table_info(tTrailing.value)" but it didn't work. Another option I found was to create a new table with all the columns and a temporary name, move all the data from the existing table, delete it and rename the new table with the correct name, but that seems a gigantic workaround :| It seems it would only be justified if I first notice some column is missing, but then again I would need a way to check that.

So any better ideas would be appreciated!

Heh, it seems I was too fast to say that non-elegant method worked ^^ Here is my code using that way:

int request = DatabasePrepare(handleDB
         ,"SELECT tTrailing.value FROM " + dbData.tableOperations + " LIMIT 1");
         
   if (request == INVALID_HANDLE)
   {
      MW_MTC_PRINT("Error while checking columns in table \"" + dbData.tableOperations
            + "\" in " + dbName + ". Adding missing columns (error code: " + IntegerToString(GetLastError()) + ")");
      
      if (!DatabaseExecute(handleDB,"ALTER TABLE '" + dbData.tableOperations + "' ADD " 
            "`tTrailing.value` DOUBLE"))
      {
         MW_MTC_PRINT("Error while adding column \"tTrailing.value\" to table \"" + dbData.tableOperations
            + "\" in " + dbName + " (error code: " + IntegerToString(GetLastError()) + ")");
            
         return false;
      }
      
      if (!DatabaseExecute(handleDB,"ALTER TABLE '" + dbData.tableOperations + "' ADD " 
            "`tTrailing.currPriceAtStart`"))
      {
         MW_MTC_PRINT("Error while adding column \"tTrailing.currPriceAtStart\" to table \"" + dbData.tableOperations
            + "\" in " + dbName + " (error code: " + IntegerToString(GetLastError()) + ")");
            
         return false;
      }
      
      if (!DatabaseExecute(handleDB,"ALTER TABLE '" + dbData.tableOperations + "' ADD " 
            "`tTrailing.execution` INTEGER"))
      {
         MW_MTC_PRINT("Error while adding column \"tTrailing.execution\" to table \"" + dbData.tableOperations
            + "\" in " + dbName + " (error code: " + IntegerToString(GetLastError()) + ")");
         
         return false;
      }
      
      MW_MTC_PRINT("Missing columns successfully added to table \"" + dbData.tableOperations + \"");
            
      return true;
   }
   else
   {
      DatabaseFinalize(request);
      
      return true;
   }

And the result:

So when I do the SELECT query, it fails to find the column that actually is there (confirmed using MetaEditor).

[Stanislav Korotky]

What about this:

PRAGMA table_xinfo('table_name')

?

[Martin Bittencourt]

Stanislav Korotky #:

What about this:

?

Hi!

I get the same result as when I do the query without the "x". That being said, I decided to do some extra search on what exactly I was getting of info with this whole pragma thing (the difference between working sleepy and awaken) and I realized what I was doing wrong: I was doing the same mistake as the guy in this link, trying to get the column names when actually the names are the rows in the query's result, so instead of using DatabaseColumnName, I'm supposed to use DatabaseColumnText.

Anyway, your answer actually kinda helped me find the mistake - another person suggesting some "pragma answer" indicated that such way was the correct one and I was the one who was doing something wrong :) So, thanks!

[Alain Verleyen]

Martin Bittencourt:

Hi,

I need a function that will check if my tables have certain columns and, if not, add them. The second part is fine, but how can I do the check? The Database functions include one about the existence of a table, but not a column of it. I did some search on the internet and of all the possible solutions, the only one I managed to make it work was to do a SELECT clause on the column I want to check and, if it returns an error, I understand it isn't there. But this solution is far from elegant, giving a clear error message to the user like a problem happened (what, after all, is correct since I tried to SELECT from a table that doesn't exists):

And it even gives a generic error code... So is there a more "elegant" solution? I tried options such as PRAGMA table_info(tTrailing.value)" but it didn't work. Another option I found was to create a new table with all the columns and a temporary name, move all the data from the existing table, delete it and rename the new table with the correct name, but that seems a gigantic workaround :| It seems it would only be justified if I first notice some column is missing, but then again I would need a way to check that.

So any better ideas would be appreciated!

I never worked with MQL5 Database API but looking at the documentation there is a bunch of DatabaseColumnXXX functions, so you can perfectly use them to check if a column exists. Use the 'dbc'Count() one, then loop through all columns using any criteria you need to find the existence or not of your column. I would not say it's more elegant though.

In my opinion, the elegant way to do it is through SQL, like you already found.