We help IT Professionals succeed at work.

Create table IF NOT EXIST

thready
thready asked
on
Medium Priority
7,000 Views
Last Modified: 2011-10-03
Hi Experts,

Is there a way to check if a table exists in a given database before I try to create it in ODBC for Postgres?

Thanks,
Mike
Comment
Watch Question

Commented:
if Object_id(tablename) is not NULL
Shanmuga SundaramDirector of Software Engineering
CERTIFIED EXPERT

Commented:
it should go like this...

if select count(*) from pg_class where relname = 'mytable' is NULL THEN CREATE table table
Shanmuga SundaramDirector of Software Engineering
CERTIFIED EXPERT

Commented:

Author

Commented:
Thanlks for the quick response....
Hmm... Object_id not recognized as a function..  Do you know of anything that exists from the ODBC API?  (anything from odbc32.lib)?  Wouldn't this function need to be in the context of some given database?  Where are you calling Object_id from?

Author

Commented:
shasunder, that solution looks like the right one, works from the shell when I execute it by hand, but programmatically from ODBC, when I bind a variable to relname and call SQLFetch, I get SQL_NO_DATA back (when in fact, the table exists).  I'm using psqlodbc version 8.3.1.  Any ideas?

Thanks!

Author

Commented:
why don't they implement a CREATE TABLE IF NOT EXIST in postgres like in MySQL?  It would make things easier for me.....   :-)  

Commented:

Hi,

Just a stupid solution:
Run a select from odbc:
select count(*) from are_you_sure_is_there_table;
then catch the error, and add the table.
If there is no error, it means the table exists.

Cheers,
za-k/

Author

Commented:
I agree adrpo, not the best solution but you may end up winning the points since this would be the only thing that would work so far out of all the solutions.....  ;-)
SELECT count(*) from information_schema.tables WHERE table_name = 'my_table';

That will give you the count of matching tables.  You can also optionally confirm the table_schema if you have tables of the same name in different schemas.

The good thing about using information_schema is that it's part of the SQL standard.  Here's a link to the documentation on the information_schema:

http://www.postgresql.org/docs/8.2/static/information-schema.html

There are several other useful views in that schema.

Author

Commented:
Hi wide_awake,
This also works in the shell, but not when I call it from a program using ODBC.  Something is wrong somewhere - I'm wondering if it could be the driver?  I'm connecting to the same database as the same user...  
Thanks,
Mike

Commented:

Hi again...

Here is how you do it in ODBC ....
I just try it, so it works.

    Dim rsTables As ADODB.Recordset
    Set rsTables = cn.OpenSchema(adSchemaTables)
    
    Do Until rsTables.EOF
        If rsTables!TABLE_NAME = "YOUR_TABLE_NAME" Then
           Debug.Print "Table:" & rsTables!TABLE_NAME & " exists!" & vbCrLf
        End If
        rsTables.MoveNext
    Loop

Open in new window

Author

Commented:
Hi adrpo,

I'm using the psqlODBC driver for Visual C++ - the one where your calls would have to use SQLPrepare, SQLBincCol, SQLFetch, etc...  That's maybe where the problem is - if this psqlODBC driver....  Not sure what's going wrong...

Mike

BOOL CMyClass::TableExists(CString strTableName, CDatabase * pDatabase, HSTMT hStatement)
{
	CString strStatement;
	strStatement.Format("SELECT count(*) from information_schema.tables WHERE table_name = '%s'), strTableName);
	if(!ExecuteStatement(strStatement))
	{
		CString S;
		S.Format("Error:  Could not run query from DoesTableExist:\r\n\r\n%s", strStatement);
		::MessageBox(NULL, S, "Error", MB_ICONWARNING);
		return FALSE;
	}
 
	SQLINTEGER nCount, cbCount;
	SQLBindCol(hStatement, 1, SQL_C_ULONG, &nCount, 0, &cbCount);
	RETCODE rc = SQLFetch(hStatement);
	if (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO)
		return nCount > 0;
	else
	{
		CString S;
		S.Format("Error:  Could not fetch results in DoesTableExist: \r\n\r\n%s", strStatement);
		::MessageBox(NULL, S, "Error", MB_ICONWARNING);
		return FALSE;
	}
}
 
BOOL ExecuteStatement(CString strStatement, BOOL bReportErrors)
{
	unsigned char * QUERY = (unsigned char *) strStatement.GetBuffer(MAX_PATH);
	strStatement.ReleaseBuffer();
	RETCODE rc = SQLPrepare(m_hStatement, QUERY, SQL_NTS);
	if(!MYSQLSUCCESS(rc))
	{
		if(bReportErrors)
			DisplayErrorMessageStatementHandle(m_hStatement);
		return FALSE;
	}
	else
	{
		rc = SQLExecute(m_hStatement);
		if(!MYSQLSUCCESS(rc))
		{
			DisplayErrorMessageStatementHandle(m_hStatement);
			return FALSE;
		}
	}
	
	return TRUE;
}

Open in new window

Your string on line 4 (the SQL string) is not closed.  Maybe that's your problem.

If that's not it, what is the error you're getting?
Commented:

Hi,

I tried directly in ODBC in Visual C++ 2005.
Here is my version of exists table....

This is the most important part:
/* Retrieve all tables in the database */
SQLTables(stmt, NULL, 0, NULL, 0, NULL, 0, _T("TABLE"), SQL_NTS);

Excuse my code, but I was in a hurry, you adapt it...

Cheers,
za-k/



 
   SQLHENV env;
   SQLHDBC dbc;
   SQLHSTMT stmt;
   SQLSMALLINT columns; /* number of columns in result-set */
   SQLCHAR buf[ 5 ][ 64 ];
   int row = 0;
   SQLINTEGER indicator[ 5 ];
   int i;
 
   /* Allocate an environment handle */
   SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
 
   /* We want ODBC 3 support */
   SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
 
   /* Allocate a connection handle */
   SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
 
   /* Connect to the DSN mydsn */
   /* You will need to change dsn to one you have created */
   /* and tested */
   SQLRETURN x = SQLDriverConnectW(dbc, NULL, _T("DSN=PostgreSQL30;UID=postgres"), SQL_NTS,
                    NULL, 0, NULL, SQL_DRIVER_COMPLETE);
 
   if (x != SQL_SUCCESS)
   {
     exit(1);
   }
   /* Allocate a statement handle */
   SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
 
   /* Retrieve all tables in the database */
   SQLTables(stmt, NULL, 0, NULL, 0, NULL, 0, _T("TABLE"), SQL_NTS);
 
   /* How many columns are there */
   SQLNumResultCols(stmt, &columns);
 
   /* Loop through the rows in the result-set binding to */
   /* local variables */
   for (i = 0; i < columns; i++) {
      SQLBindCol( stmt, i + 1, SQL_C_CHAR,
            buf[ i ], sizeof( buf[ i ] ), &indicator[ i ] );
   }
 
   CString strTableName("host");
   /* Fetch the data */
   while (SQL_SUCCEEDED(SQLFetch(stmt))) {
      /* display the results that will now be in the bound area's */
     
       TRACE(_T("Found table: %hs %hs %hs %hs\n"), buf[0], buf[1], buf[2], buf[3], buf[4]);
       if (indicator[ 2 ] == SQL_NULL_DATA) {
          /* don't care */
       }
       else 
       {
          CString strTableInDb(buf[2]);
          if (strTableName.Compare(strTableInDb) == 0)
            TRACE0("Found our table\n");
       }
   }

Open in new window

Commented:

More info about SQLTables:
http://msdn2.microsoft.com/en-us/library/ms131341.aspx

Cheers,
za-k/

Author

Commented:
Thank you adrpo!  It was the SQLTables() call that I did not know about.  Thanks for the nice long answer - made it very easy for me - very appreciated!  Cheers!

Commented:

Thanks for the problem. I learned quite a lot about ODBC these days.
At first I thought there is no way to access the schema and I thought:
  This is outrageous! How come you cannot access that??!!
  You can do that with DAODB but not with ODBC??!
Then I searched some more and found the SQLTables function.

Cheers,
za-k/

Explore More ContentExplore courses, solutions, and other research materials related to this topic.