Error setting DBP_AUTOCOMMIT in SqlServer

[gralata]

Hello,

in SqlServer, I'm trying to populate two child tables in this way:
I execute 'tblMain' population and, for each row fetched in tblMain, a tblChild population is executed.

This code

Code: Select all

Child Table: tblMain
   Contents
      Column: colN_ID_NOMI
      Column: colC_RAGSOC1
   Functions
      Function: Populate
         Local variables
            String: sSelect
            Number: nResultSet
            Number: nInd
         Actions
            Set sSelect = "SELECT n_id_nomi, c_ragsoc1 INTO :colN_ID_NOMI, :colC_RAGSOC1 FROM is_t_nomi"
            If CursorConnect( hCursor )
               Call SalTblPopulate( hWndForm, hCursor, sSelect, TBL_FillAll )
   Window Variables
      Sql Handle: hCursor
   Message Actions
      On SAM_FetchRowDone
         Call tblChild.Populate(  )
!
Child Table: tblChild
   Contents
      Column: colN_COD_CLIFOR
   Functions
      Function: Populate
         Local variables
            String: sSelect
            Number: nResultSet
            Number: nInd
         Actions
            Set sSelect = "SELECT n_cod_clifor INTO :colN_COD_CLIFOR FROM is_t_clifor WHERE n_id_nomi = tblMain.colN_ID_NOMI"
            If CursorConnect( hCursor )
               Call SalTblPopulate( hWndForm, hCursor, sSelect  , TBL_FillAll )
   Window Variables
      Sql Handle: hCursor
...
Function: CursorConnect
   Description:
   Returns
      Boolean:
   Parameters
      Receive Sql Handle: hSqlIn
   Static Variables
   Local variables
   Actions
      If hSqlIn
         Return TRUE
      !
      Call SqlCreateStatement (SqlOleDbSession, hSqlIn)
      !
      Call SqlSetResultSet( hSqlIn, TRUE )
      Call SqlSetParameter( hSqlIn, DBP_AUTOCOMMIT, FALSE, '' )
      !
      Return TRUE
      !

in TD3 works fine, but in TD6.1 causes an error.

To eliminate the error I have to comment DBP_AUTOCOMMIT setting...

Code: Select all

Function: CursorConnect
   Description:
   Returns
      Boolean:
   Parameters
      Receive Sql Handle: hSqlIn
   Static Variables
   Local variables
   Actions
      If hSqlIn
         Return TRUE
      !
      Call SqlCreateStatement (SqlOleDbSession, hSqlIn)
      !
      Call SqlSetResultSet( hSqlIn, TRUE )
      ! Call SqlSetParameter( hSqlIn, DBP_AUTOCOMMIT, FALSE, '' )
      !
      Return TRUE
      !

[Jeff Luther]

Well, that's a big change in TD versions. What about MS SQL Server versions? What's the old vs. new version?

Also, you wrote:

in TD6.1 causes an error.

I think it's important to find out what the error is. You write that you can stop the error by commenting out the AUTOCOMMIT call, but is the error *when* SqlSetParameter( hSqlIn, DBP_AUTOCOMMIT, FALSE, '' ) is called? What is the specific error -- that is, the details??

And if the error is *not* on the SqlSetParameter() call, when does it occur? We need to know more. A small test case to show this could be helpful too.

[gralata]

What about MS SQL Server versions? What's the old vs. new version?

I used MS SQL Server 2008R2 in both versions

You write that you can stop the error by commenting out the AUTOCOMMIT call, but is the error *when* SqlSetParameter( hSqlIn, DBP_AUTOCOMMIT, FALSE, '' ) is called? What is the specific error -- that is, the details??

The error is after SqlSetParameter.
I obtain error on populate the child table

TestTable_02.jpg

A small test case to show this could be helpful too.

test case:

testtable.zip

database:

dbtest.zip

Thank you.

[Jeff Luther]

Error msg. translated into English could be helpful

Also, sometimes it helps me if I run the same SELECT (without INTO and with hard-coded ID_NOMI value, of course) in SQLTalk. Does it run? Give same error? error after a certain number of rows fetched? Problem with SalTblPop is this is a DO_IT!() function that gives you no details -- unless you trap SAM_FetchRow msg, for example -- as to where in the query the sql error is happening.

I haven't run your test case yet (and will, but couple other thoughts based on your code:
* set query to return 1 row -- what happens/
* hard code an ID_NOMI in the select statement and not have a concat. value in the WHERE clause.

[Jeff Luther]

P.S. what's the change of getting CREATE TABLE statements and some sample data for your is_t_nomi and is_t_clifor DB tables in your test? I don't have that type of access to our MS SS server. Thanks.

Though... just looking at your code. This once worked? "in TD3 works fine, but in TD6.1 causes an error."

I ask because you have this code in the test case:

Code: Select all

[b]tblMain[/b]
     Call SalTblPopulate( ..., TBL_FillALL )       ! NO where clause so *ALL* rows from is_t_nomi are fetched

     On SAM_FetchRowDone       ! This is sent for *each* Main row fetched... so for each 'Main' row you want to pop. ALL 'Child' rows?
        Call tblChild.Populate(  )

[b]tblChild[/b]
     Call SalTblPopulate( hWndForm, hCursor, sSelect  , TBL_FillAll )       ! this does return ONE row 

As I read this code, TD's Mainis not going to stop fetching while Childgets its turn so it is very likely Main will get ahead of Child -- also for each Child populate, it overwrites the previous select from previous row in Main.

ARE YOU SURE this once worked in TD v3.1? If Main had 1 row this should work but there is no WHERE clause in Main's query...