C# and postgreSQL8.0 first connection

//demo c# code snippet

//Onega 2005/02/02

//download and install postgreSQL 8.0

//download and install Npgsql0.7beta4-bin.zip unzip to ..\PostgreSQL\8.0\Npgsql\ms1.1

using System;

//C:\Program Files\Microsoft Visual Studio .NET 2003\SDK\v1.1\Bin\gacutil.exe

//download driver for .NET 1.1

//register Mono.Security.Protocol.Tls.dll and Npgsql.dll with gacutil.exe

//http://gborg.postgresql.org/project/npgsql/download/download.php?branch=devel

//add the following 2 dlls to global assembly cache.

//C:\Program Files\PostgreSQL\8.0\Npgsql\ms1.1\Mono.Security.Protocol.Tls.dll

//C:\Program Files\PostgreSQL\8.0\Npgsql\ms1.1\Npgsql.dll

namespace postgres_net

{

    /// <summary>

    /// Summary description for Class1.

    /// </summary>

    using System;

    using System.Data;

    using Npgsql;



    public class Test

    {

        public static void Main(string[] args)

        {

            string connectionString ="Server=127.0.0.1;Port=5432;User Id=postgres;Password=postgres;Database=template1;";

            //IDbConnection dbcon;

            NpgsqlConnection dbcon = new NpgsqlConnection(connectionString);

            dbcon.Open();

            IDbCommand dbcmd = dbcon.CreateCommand();

            string sql ="select * from pg_database";

            dbcmd.CommandText = sql;

            IDataReader reader = dbcmd.ExecuteReader();

            while(reader.Read())

            {

                if(reader.FieldCount>0)

                {

                    string firstfield = (string)reader[0];                    

                    Console.WriteLine(reader.GetName(0) + ":" +firstfield);

                }

            }

            // clean up

            reader.Close();

            reader = null;

            dbcmd.Dispose();

            dbcmd = null;

            dbcon.Close();

            dbcon = null;

            int sleep_time = 5000;

            Console.WriteLine("Sleep " + sleep_time/1000 +" seconds");

            System.Threading.Thread.Sleep(sleep_time);

        }

    }

}

vc access postgresql8.0 via ODBC bool compatible

//vc access postgresql8.0 bool compatible

#include "windows.h"

#include "sql.h"

#include "sqlext.h"

#include "stdio.h"

#include "boost/shared_ptr.hpp"

#include <map>

#include <deque>

#include <iostream>

#include <sstream>

//Onega 2005/02/15

//build on windows 2003 with VC2003, PostgreSQL 8.0

//bool column may be SQL_CHAR and '1'(ASCII 49) = true, '0'= false

namespace ODBCAgent

{

    SQLRETURN SQLFetch(

        SQLHSTMT     StatementHandle)

    {

        SQLRETURN rc = ::SQLFetch(StatementHandle);

        std::stringstream ss;

        ss<<"SQLFetch return "<<rc;



        return rc;

    }

}

//using namespace ODBCAgent;

//using ODBCAgent::SQLFetch;

std::string SqlTypeToString(SQLSMALLINT type)

{

    switch(type)

    {

    case SQL_CHAR:

        return "SQL_CHAR";

    case SQL_VARCHAR:

        return "SQL_VARCHAR";

    case SQL_LONGVARCHAR:

        return "SQL_LONGVARCHAR";

    case SQL_WCHAR:

        return "SQL_WCHAR";

    case SQL_WVARCHAR:

        return "SQL_WVARCHAR";

    case SQL_WLONGVARCHAR:

        return "SQL_WLONGVARCHAR";

    case SQL_DECIMAL:

        return "SQL_DECIMAL";

    case SQL_NUMERIC:

        return "SQL_NUMERIC";

    case SQL_SMALLINT:

        return "SQL_SMALLINT";

    case SQL_INTEGER:

        return "SQL_INTEGER";

    case SQL_REAL:

        return "SQL_REAL";

    case SQL_FLOAT:

        return "SQL_FLOAT";

    case SQL_DOUBLE:

        return "SQL_DOUBLE";

    case SQL_BIT:

        return "SQL_BIT";

    case SQL_TINYINT:

        return "SQL_TINYINT";

    case SQL_BIGINT:

        return "SQL_BIGINT";

    case SQL_BINARY:

        return "SQL_BINARY";

    case SQL_VARBINARY:

        return "SQL_VARBINARY";

    case SQL_LONGVARBINARY:

        return "SQL_LONGVARBINARY";

    case SQL_TYPE_DATE:

        return "SQL_TYPE_DATE";

    case SQL_TYPE_TIME:

        return "SQL_TYPE_TIME";

    case SQL_TYPE_TIMESTAMP:

        return "SQL_TYPE_TIMESTAMP";

    //case SQL_TYPE_UTCDATETIME:

    //    return "SQL_TYPE_UTCDATETIME";

    //case SQL_TYPE_UTCTIME:

    //    return "SQL_TYPE_UTCTIME";

    case SQL_GUID:

        return "SQL_GUID";

    }

    std::stringstream ss;

    ss<<"unknown type:"<<(long)type;

    return ss.str();

}

int main()

{

    SQLRETURN rc = SQL_SUCCESS;

    SQLHENV hEnv = NULL;

    rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);

    rc = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION,

(SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);

    SQLHDBC hDbc;

    rc = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);

    char szInConnStr[1024];

    strcpy(szInConnStr,"Driver={PostgreSQL};Server=localhost;Port=5432;Da

tabase=testdb;Uid=postgres;Pwd=postgres;BoolsAsChar=1;");

    char szOutConnStr[1024];

    SQLSMALLINT dwOutConnStrLen = sizeof(szOutConnStr);

    rc = SQLDriverConnect(hDbc, NULL, 

        reinterpret_cast<SQLCHAR*>(szInConnStr), strlen(szInConnStr), 

        reinterpret_cast<SQLCHAR*>(szOutConnStr), dwOutConnStrLen,

&dwOutConnStrLen,

        SQL_DRIVER_NOPROMPT);

    SQLHSTMT hStmt;

    rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);

    char szStmt[1024];

    strcpy(szStmt, "SELECT * FROM testdb.pg_catalog.pg_database;");

    //strcpy(szStmt, "SELECT * FROM test_table;");

    SQLPrepare(hStmt,reinterpret_cast<SQLCHAR*>(szStmt), SQL_NTS);

    rc = SQLExecute(hStmt);

    //rc = SQLExecDirect(hStmt, reinterpret_cast<SQLCHAR*>(szStmt),SQL_NTS);

    SQLSMALLINT nCols = 0;

    const int MAX_DATA_LEN = 1024;

    char szHeader[256];

    SQLSMALLINT swColLen;

    SQLSMALLINT swColType = 0;

    SQLUINTEGER udwColDef =0;

    SQLSMALLINT swColScale;

    SQLSMALLINT swColNull;

    SQLCHAR szRowBuffer[1024];

    SQLINTEGER bindcollenret;

    rc = SQLNumResultCols(hStmt, &nCols); // Get Num of Result Columns,

    typedef boost::shared_ptr<char> SmartBuffer;

    //std::map<int,SmartBuffer > record_buffer_list;

    std::deque<std::string> column_name_list;

    //std::map<std::string,SQLSMALLINT> column_name_list;

    std::deque<short> column_type_list;

    std::deque<SmartBuffer> record_buffer_list;

    std::deque<int> column_size_list;

    for (int nColCount=0; nColCount<nCols; nColCount++) 

    {

        szHeader[0] = 0;

        rc = SQLDescribeCol(hStmt, nColCount,

reinterpret_cast<SQLCHAR*>(szHeader),

            MAX_DATA_LEN, &swColLen, &swColType, &udwColDef,

            &swColScale, &swColNull

            ); // Get the header/title of each column

        column_name_list.push_back(reinterpret_cast<const char*>(szHeader));

        //column_name_list[reinterpret_cast<const char*>(szHeader)] = swColType;

        column_type_list.push_back(swColType);

        std::cout<<szHeader<<" type: "<<SqlTypeToString(swColType)

            <<"  column size:"<<(long)udwColDef <<std::endl;

        //if(udwColDef>0)

        {

            //int col_size = (udwColDef==1?udwColDef+1:udwColDef);

            column_size_list.push_back(udwColDef);

            SmartBuffer col_buf(new char[udwColDef+2]);

            rc = SQLBindCol(hStmt, nColCount, swColType,

                col_buf.get(), udwColDef+2, &bindcollenret

                ); 

            //record_buffer_list[reinterpret_cast<constchar*>(szHeader)]=(col_buf);

            record_buffer_list.push_back(col_buf);

        }

    }

    for(int i=0;i<record_buffer_list.size();i++)

    {

        if(column_size_list[i]>0)

        memset(record_buffer_list[i].get(),0,column_size_list[i]+2);

    }

    rc = SQLFetch(hStmt); // Fetch row data from the statement cursor.

    while(SQL_SUCCESS==rc||SQL_SUCCESS_WITH_INFO==rc)

    {

        for(int i=0;i<column_name_list.size();i++)

        {

            SmartBuffer sb = record_buffer_list[i];

            int column_size = column_size_list[i];

            std::string column_name = column_name_list[i];

            int column_type = column_type_list[i];

            if(column_name.length()==0)

                continue;

            std::cout<<column_name.c_str()<<"=";

            if(column_type==SQL_C_CHAR || SQL_VARCHAR == column_type)

            {

                std::cout<<record_buffer_list[i];

            }

            if(SQL_INTEGER == column_type_list[i])

            {

                int nlen;

                memcpy(&nlen,sb.get(),column_size+2);

                std::cout<<nlen;

            }

            std::cout<<"    ";

        }

        std::cout<<std::endl;

        for(int i=0;i<record_buffer_list.size();i++)

        {

            if(column_size_list[i]>0)

            memset(record_buffer_list[i].get(),0,column_size_list[i]+2);

        } 

        rc = SQLFetch(hStmt);

    }

    SQLCloseCursor(hStmt); 

    SQLFreeHandle(SQL_HANDLE_STMT, hStmt); 

    SQLDisconnect(hDbc); 

    SQLFreeHandle(SQL_HANDLE_DBC, hDbc); 

    SQLFreeHandle(SQL_HANDLE_ENV, hEnv); 

    system("pause");

}

table/field name in PostgreSQL

I found an interesting fact with PostgreSQL
name of some table/field must be quoted, otherwise psql complains relation image not exist.
Create or replace VIEW IMAGE_(EXPOSURE_TIME,XRAY_CURRENT,
)
as select Exposure_time,"Xray_tube_current",
from "IMAGE";