Overview
Features
Download
Documentation
Community
Add-Ons & Services

PostgreSQL ODBC insert into Poco::Data syntax?

Please post support and help requests here.

PostgreSQL ODBC insert into Poco::Data syntax?

Postby chumpski » 08 Jul 2009, 13:45

Hi,

New to Poco and to this forum.

I am trying out Poco ODBC library to access a PostgreSQL database. I can access the database with isql so it is likely configured properly. Also i can perform a select on the database and print out results with Poco::Data. My problem occurs when i try to insert data into the database. I am having a problem with the substitution syntax to use in my SQL statement i think.

Here is my code:
Code: Select all
#include "Poco/Data/SessionFactory.h"
#include "Poco/Data/Session.h"
#include "Poco/Data/ODBC/Connector.h"
#include "Poco/Data/ODBC/ODBCException.h"
#include <iostream>
#include <string>
#include <exception>
#include <vector>

using namespace Poco::Data;
using namespace std;

int main(int argc, char** argv)
{
   // Register the ODBC connector.
   Poco::Data::ODBC::Connector::registerConnector( );

   try
   {
      // Connect to ODBC database.
      Session session( string( "ODBC" ), string( "dsn=ODBCTESTDB;uid=psqluser;pwd=psqlpassword;" ) );
      cout << "Opened successfully!" << endl;
      cout << endl;

      // Example of a SELECT statement on TESTDB.
      {
         cout << "Performing a \"SELECT county FROM test\" SQL statement..." << endl;

         // Create vector to store results.
         vector< string > resultStringVec;
   
         // Execute select statement on table.
         session << "SELECT county FROM test", into( resultStringVec ), now;
   
         // If no results returned
         if( resultStringVec.empty( ) )
         {
            cout << "No results returned from the query!" << endl;
         }
         else
         {
            // Iterate through results
            int i = 1;
            for( vector< string >::iterator curr = resultStringVec.begin( ), end = resultStringVec.end( ); curr != end; ++curr, ++i )
            {
               cout << "Record " << i << ": " << *curr << endl;
            }
         }

         cout << endl;
      }

      // Example of INSERT INTO statement on TESTDB with single data types
      {
         string countyName( "tyrone" );
         string countyColour( "white" );

         cout << "Performing a \"INSERT INTO test VALUES (?, ?)\" SQL statement..." << endl;

         session << "insert into test values (?, ?)" << use( countyName ), use( countyColour ), now;

         // Create vector to store results.
         vector< string > resultVec;

         cout << "Testing INSERT INTO with a printout of the database contents SQL statement..." << endl;

         // Execute select statement on table.
         session << "SELECT county FROM test", into( resultVec ), now;
   
         // If no results returned
         if( resultVec.empty( ) )
         {
            cout << "No results returned from the query!" << endl;
         }
         else
         {
            // Iterate through results
            int i = 1;
            for( vector< string >::iterator curr = resultVec.begin( ), end = resultVec.end( ); curr != end; ++curr, ++i )
            {
               cout << "Record " << i << ": " << *curr << endl;
            }
         }

         cout << endl;
      }
   }
   catch ( Poco::Data::ODBC::StatementException& ex )
   {
      cout << "Statement Exception: " << ex.toString();
   }
   catch( Poco::Exception &e )
   {
      cout << "General Exception occurred: " << e.displayText( ) << endl;
   }

   // Unregister the ODBC connector.
   Poco::Data::ODBC::Connector::unregisterConnector( );

   return 0;
}



My application throws an exception when i try to insert data into the database.

If you look at the line:
Code: Select all
session << "insert into test values (?, ?)" << use( countyName ), use( countyColour ), now;


It throws an exception:
Statement Exception: ODBC Error: ODBC handle exception
===================
Connection:Not applicable
Server:ODBCTESTDB
===========================
ODBC Diagnostic record #1:
===========================
SQLSTATE = 07002
Native Error Code = 32
The # of binded parameters < the # of parameter markers


This is a problem the ODBC Postgres driver is giving me is it?

If i try the way recommended in the documnetation with :name reference characters:
Code: Select all
session << "insert into test values (:cn, :cc)" << use( countyName ), use( countyColour ), now;


I get the exception:
Statement Exception: ODBC Error: ODBC handle exception
===================
Connection:Not applicable
Server:ODBCTESTDB
===========================
ODBC Diagnostic record #1:
===========================
SQLSTATE = HY000
Native Error Code = 7
ERROR: syntax error at or near ":" at character 26;
Error while executing the query


This gives me a SQL syntax error so i guess my Postgres driver does not support the Poco::Data :name syntax?

My Postgres version is 8.3. (8.3.7-0lenny1)

My Postgres ODBC Driver is the odbc-postgresql ANSI driver. (1:08.03.0200-1)
chumpski
 
Posts: 4
Joined: 08 Jul 2009, 13:26

Re: PostgreSQL ODBC insert into Poco::Data syntax?

Postby guenter » 10 Jul 2009, 11:31

Your insert line looks wrong:
session << "insert into test values (?, ?)" << use( countyName ), use( countyColour ), now;

should be:
Code: Select all
session << "insert into test values (?, ?)", use( countyName ), use( countyColour ), now;

(comma before use)
guenter
 
Posts: 1119
Joined: 11 Jul 2006, 16:27
Location: Austria

Re: PostgreSQL ODBC insert into Poco::Data syntax?

Postby chumpski » 10 Jul 2009, 11:52

Your right, thank you very much. I just tested that now and it works.
chumpski
 
Posts: 4
Joined: 08 Jul 2009, 13:26


Return to Support

Who is online

Users browsing this forum: No registered users and 1 guest

cron