Create table IF NOT EXIST

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
LVL 1
threadyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

scorpiiaCommented:
if Object_id(tablename) is not NULL
0
Shanmuga SundaramDirector of Software EngineeringCommented:
it should go like this...

if select count(*) from pg_class where relname = 'mytable' is NULL THEN CREATE table table
0
Shanmuga SundaramDirector of Software EngineeringCommented:
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

threadyAuthor 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?
0
threadyAuthor 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!
0
threadyAuthor Commented:
why don't they implement a CREATE TABLE IF NOT EXIST in postgres like in MySQL?  It would make things easier for me.....   :-)  
0
adrpoCommented:

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/
0
threadyAuthor 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.....  ;-)
0
wide_awakeCommented:
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.
0
threadyAuthor 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
0
adrpoCommented:

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

0
threadyAuthor 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

0
wide_awakeCommented:
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?
0
adrpoCommented:

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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
adrpoCommented:

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

Cheers,
za-k/
0
threadyAuthor 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!
0
adrpoCommented:

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/
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.