?
Solved

Create table IF NOT EXIST

Posted on 2008-01-25
17
Medium Priority
?
6,936 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
0
Comment
Question by:thready
  • 7
  • 5
  • 2
  • +2
17 Comments
 
LVL 4

Expert Comment

by:scorpiia
ID: 20741627
if Object_id(tablename) is not NULL
0
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 20741672
it should go like this...

if select count(*) from pg_class where relname = 'mytable' is NULL THEN CREATE table table
0
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 20741687
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 1

Author Comment

by:thready
ID: 20741738
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
 
LVL 1

Author Comment

by:thready
ID: 20741990
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
 
LVL 1

Author Comment

by:thready
ID: 20742074
why don't they implement a CREATE TABLE IF NOT EXIST in postgres like in MySQL?  It would make things easier for me.....   :-)  
0
 
LVL 10

Expert Comment

by:adrpo
ID: 20746105

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
 
LVL 1

Author Comment

by:thready
ID: 20746125
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
 
LVL 3

Expert Comment

by:wide_awake
ID: 20751435
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
 
LVL 1

Author Comment

by:thready
ID: 20755267
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
 
LVL 10

Expert Comment

by:adrpo
ID: 20755512

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
 
LVL 1

Author Comment

by:thready
ID: 20755548
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
 
LVL 3

Expert Comment

by:wide_awake
ID: 20756013
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
 
LVL 10

Accepted Solution

by:
adrpo earned 2000 total points
ID: 20756167

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
 
LVL 10

Expert Comment

by:adrpo
ID: 20756182

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

Cheers,
za-k/
0
 
LVL 1

Author Closing Comment

by:thready
ID: 31424912
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
 
LVL 10

Expert Comment

by:adrpo
ID: 20759352

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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
In this video I will demonstrate how to set up Nine, which I now consider the best alternative email app to Touchdown.
Suggested Courses

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question