DATETIME, ADO, parameterized INSERTs and milliseconds, oh my

-

i want execute paramterized ado insert command sql server datetime column without losing milliseconds.  can accomplish without parameters, isn't want.  suggestions?

 

it ok datetime has resolution of 3.33 milliseconds.

 

see attached code:

 

code snippet

#include <string>

#include <iostream>

#import "c:\program files\common files\system\ado\msado15.dll" rename( "eof", "adoeof" )

int main( int argc, char* argv[] )

{

    ::coinitialize( null );

    try

    {

        adodb::_connectionptr connection;

        connection.createinstance(__uuidof( adodb::connection ) );

 

        std::string connectionstring;

 

        connectionstring.append( "provider=sqloledb;" );

        connectionstring.append( "data source=hpserv1;" ); // choose server/instance.

        connectionstring.append( "initial catalog=tempdb;" );

        connectionstring.append( "integrated security=sspi;" );

        connection->connectiontimeout = 10;

        connection->open(

            _bstr_t( connectionstring.c_str() ),

            _bstr_t( "" ),

            _bstr_t( "" ),

            adodb::adopenunspecified );

 

        std::string sqlstatement;

 

        sqlstatement = "drop table testtable1";

 

        try

        {

            connection->execute( _bstr_t( sqlstatement.c_str() ), null, adodb::adexecutenorecords );

        }

        catch( const _com_error& )

        {

            // ignore errors table doesn't exist.

        }

 

        sqlstatement = "create table testtable1 ( colint int not null primary key, coldate datetime )";

        connection->execute( _bstr_t( sqlstatement.c_str() ), null, adodb::adexecutenorecords );

 

        // ====================================================================

        // works (datetime resolution 3.33 milliseconds rounds .347)

 

        sqlstatement = "insert testtable1 ( colint, coldate ) values ( 1, '2007-12-28 20:05:16.345' )";

        connection->execute( _bstr_t( sqlstatement.c_str() ), null, adodb::adexecutenorecords );

 

        // ====================================================================

        // works (null inserted)

 

        sqlstatement = "insert testtable1 ( colint, coldate ) values ( 2, null )";

        connection->execute( _bstr_t( sqlstatement.c_str() ), null, adodb::adexecutenorecords );

 

        // ====================================================================

        // works (datetime resolution 3.33 milliseconds rounds .347)

 

        sqlstatement = "insert testtable1 ( colint, coldate ) values ( 3, convert( datetime, '2007-12-28 20:05:16.345' ) )";

        connection->execute( _bstr_t( sqlstatement.c_str() ), null, adodb::adexecutenorecords );

 

 

        // ====================================================================

 

        unsigned int colint = 3;

        sqlstatement = "insert testtable1 ( colint, coldate ) values ( ?, ? )";

 

        adodb::_commandptr command;

 

 

        // ====================================================================

        // fails (operand type clash: ntext incompatible datetime)

 

        colint++;

 

        command.createinstance( __uuidof( adodb::command ) );

        command->activeconnection = connection;

        command->commandtype = adodb::adcmdtext;

        command->commandtext = _bstr_t( sqlstatement.c_str() );

        command->parameters->append( command->createparameter(

            _bstr_t(),

            adodb::adinteger,

            adodb::adparaminput,

            4,

            _variant_t( colint ) ) );

        command->parameters->append( command->createparameter(

            _bstr_t(),

            adodb::adbstr,

            adodb::adparaminput,

            -1,

            _bstr_t( "2005-10-25 09:10:11.012" ) ) );

 

        try

        {

            command->execute( null, null, adodb::adcmdtext );

        }

        catch( const _com_error& e )

        {

            std::cout << "error @ colint=" << colint << " (adodb::adbstr , _bstr_t):\n"

                << " hresult = " << e.error() << ": " << e.description() << "\n"

                << " sql statement: " << command->commandtext << std::endl;

        }

 

 

        // ====================================================================

        // fails (operand type clash: text incompatible datetime)

 

        colint++;

 

        command.createinstance( __uuidof( adodb::command ) );

        command->activeconnection = connection;

        command->commandtype = adodb::adcmdtext;

        command->commandtext = _bstr_t( sqlstatement.c_str() );

        command->parameters->append( command->createparameter(

            _bstr_t(),

            adodb::adinteger,

            adodb::adparaminput,

            4,

            _variant_t( colint ) ) );

        command->parameters->append( command->createparameter(

            _bstr_t(),

            adodb::adchar,

            adodb::adparaminput,

            -1,

            _bstr_t( "2005-10-25 09:10:11.012" ) ) );

 

        try

        {

            command->execute( null, null, adodb::adcmdtext );

        }

        catch( const _com_error& e )

        {

            std::cout << "error @ colint=" << colint << " (adodb::adchar , _bstr_t):\n"

                << " hresult = " << e.error() << ": " << e.description() << "\n"

                << " sql statement: " << command->commandtext << std::endl;

        }

 

 

        // ====================================================================

        // fails (a datetime inserted table milliseconds zeroed)

 

        colint++;

 

        ::systemtime sysnow;

        ::getsystemtime( &sysnow );

 

        double mytime;

        ::systemtimetovarianttime( &sysnow, &mytime );

 

        // systemtimetovarianttime strips milliseconds, we'll add more

        // in we're testing insert of milliseconds.

 

        double desiredmilliseconds = 456;

        mytime += desiredmilliseconds / 24.0 / 3600.0 / 1000.0;

 

        command.createinstance( __uuidof( adodb::command ) );

        command->activeconnection = connection;

        command->commandtype = adodb::adcmdtext;

        command->commandtext = _bstr_t( sqlstatement.c_str() );

        command->parameters->append( command->createparameter(

            _bstr_t(),

            adodb::adinteger,

            adodb::adparaminput,

            4,

            _variant_t( colint ) ) );

        command->parameters->append( command->createparameter(

            _bstr_t(),

            adodb::addate,

            adodb::adparaminput,

            -1,

            _variant_t( mytime, vt_date ) ) );

 

        try

        {

            command->execute( null, null, adodb::adcmdtext );

        }

        catch( const _com_error& e )

        {

            std::cout << "error @ colint=" << colint << " (adodb::addate , _variant_t vt_date):\n"

                << " hresult = " << e.error() << ": " << e.description() << "\n"

                << " sql statement: " << command->commandtext << std::endl;

        }

    }

    catch ( _com_error& e )

    {

        std::cout << "unexpected error:\n "

            << e.description() << std::endl;

    }

 

    return 0;

}

 

  

i've built your sample code , works if choose different provider.

you're refering "sqloledb" mdac. recommend use sql native client: sqlncli.

if don't have can download here:

- x86: http://download.microsoft.com/download/2/7/c/27c60d49-6dbe-423e-9a9e-1c873f269484/sqlncli.msi

- x64: http://download.microsoft.com/download/2/7/c/27c60d49-6dbe-423e-9a9e-1c873f269484/sqlncli_x64.msi 

- ia64: http://download.microsoft.com/download/2/7/c/27c60d49-6dbe-423e-9a9e-1c873f269484/sqlncli_ia64.msi

 

simply change connection string use sqlncli provider , parametrized insert starts work.



SQL Server  >  SQL Server Data Access



Comments

Post a Comment

Popular posts from this blog

Motherboard replacement

-
running windows 10 technical preview, need replace motherboard can run the  sysprep  command restoring pc ? or there easier  or more preferred way ? thanks if replace motherboard within same family of chipsets (i.e. intel > intel or amd > amd), should okay restarting pc twice. carey frisch Windows 10 Insider Preview  >  Windows 10 Insider Preview General
Read more

Cannot create Full Text Search catalog after upgrading to V12 - Database is not fully started up or it is not in an ONLINE state

-
in order access new full text search capiblities of sql azure, upgraded our azure sql v12. (a day later), when try create new catalog: create fulltext catalog mycatalog default i following message: msg 9972, level 16, state 100, line 1 database not started or not in online state. try full-text ddl command again after database started , becomes online. hi scott, apologies inconvenience caused you. known issue on service side , fix going included in next update. specific case mitigated, should able use full-text search syntax now. thanks, mihaela Microsoft Azure  >  Azure SQL Database
Read more

Remote Desktop App - Error 0x207 or 0x607

-
hi all, i manage windows 2012 r2 network, including connection broker, session hosts , remote apps virtual servers.  i've used remote desktop app on own android phone, no issues @ - connects published gateway url , can login , access network. we looking move business onto nokia handsets ms operating system (currently on blackberry), not able connect via remote desktop app on nokia lumia handset. setup, per android config , brings remote apps , remote desktop screen, know can connect.  when select remote desktop, shows following process: initiating remote connection securing remote connection certificate can't verified - choose connect it digitally signed cert (digicert sha2), pc name different physical connection broker name (inherited domain name old gov project - can't publish publicly) initiating establishing securing connection error have been 0x207 getting 0x607 a bit baffled, work on android (and ipad @ home using app). assume ms use d...
Read more