mix-n-match ADO source

[Erich Schulman]

I am using MCM's ADODB28DISP.INC, but now I need to support other things besides SELECTing, like INSERT INTO. I went back to the msado15.inc as seen at http://www.powerbasic.com/support/do...DO_Example.zip (albeit now regenerated for PB9 use).

I got an error for a duplicate equate when trying to combine the files.
$CLSID_ADODB_Connection = GUID$("{00000514-0000-0010-8000-00AA006D2EA4}")
$CLSID_ADODB_Event_ConnectionEvents = GUID$("{5EF71E5E-C2CB-4D92-A15D-6ABBFABBB987}")
The different GUIDs make it plain I can't just delete one of those lines to resolve the duplicate problem. Will I need to build a .dll to isolate the msado15.inc code?

[Michael Mattias]

You should be able to use the demo just by changing the SQL statement and eschewing the fetch code used with the SELECT.

There is a variable (property, I think) which should report to you "number of rows affected" for an INSERT or UPDATE... which is probably what you should "OBJECT GET" and return to the calling function after a succesful "Execute"

I think I have some (compatible) INSERT code here...let me look...

Yeah this ought to get you started

    ' Create the comnnection object against which we do just about everything.
    SET oConn  = NEW ADODBConnection IN $PROGID_ADoDbConnection28  ' "ADODB.Connection"

    IF ISOBJECT(oConn) THEN
           STDOUT "created ADODBConnectionObject OK, setting connection string
           sConn       = "Provider=OraOLEDB.Oracle;Data Source=XE;User ID=prod;Password=prod"
           ' -------------------------------------------------
           ' CONNECT TO DATABASE
           ' -------------------------------------------------
           CALL           ADOConnectToDb (oConn, sConn, %NULL) TO iRet

           IF ISFALSE iRET THEN  ' success, we are connected

               ' begin multiple statement transaction here
               ' Each insert is inserting one ten-line ord_comnt with the CRLF0x06 delimiters
               OBJECT CALL oconn.Begintrans   ' check errors here
               FOR Z = 1 TO 3
                    sOrdno     = FORMAT$(119+Z)
                    sLongValue = ""
                    FOR iLineNo = 1 TO 10
                       sLongValue = sLongValue & $CRLF & CHR$(&h06) _
                         & USING$ ("Order  &  LINE # Code Z TEXT", sOrdNo, iLineNo)
                    NEXT

                    sQuery  =   "Insert into ord_comnt " _
                         & "(ord_no, comnt_no, print_cd, comnt_txt) " _
                         &  " VALUES (" & sOrdNo & ", 1, 'Z', '" & sLongValue & "')"

                    STDOUT USING$( "Inserting for Order &",  sOrdNo)
                    ' ------------------------------------------------

                    ' ------------------------------------------------
                    CALL  ADOInsertOnConnection (oConn, sQuery, nRow, sErrorMessage) TO iRet
                    IF ISTRUE iRet THEN
                        ' should be in a state to get the error messages?
                        STDOUT "Error in Insert"
                        STDOUT SErrorMessage   ' this is not getting back here

                        bCommit = %FALSE
                        EXIT FOR

                    ELSE
                        STDOUT USING$("Insert suceeded for order & ", sOrdNo)
                    END IF
               NEXT ' next Z, which is the next order number and therefore the next Insert
....
FUNCTION ADOInsertOnConnection (oConn AS ADODBConnection, sSql AS STRING, nRow AS LONG, sMsg AS STRING) AS LONG

' I think all I have to do here is begin the transaction but I don't
' want to do that each time...
' all this becomes in the abscence of transaction control and connection is execute!


  LOCAL vSql    AS VARIANT
  LOCAL vW   AS VARIANT  ' working var
  LOCAL vItem     AS VARIANT
  LOCAL vNRow     AS VARIANT
  LOCAL iRet      AS LONG
  LOCAL funcval   AS LONG
  LOCAL vErrIndex AS VARIANT, vErrCount AS VARIANT
  LOCAL vErrDEsc  AS VARIANT
  LOCAL I         AS LONG
  LOCAL s         AS STRING
  LOCAL nError    AS LONG


  Funcval  = %TRUE   ' default to error, set to zero on success only
  nRow     = 0       ' reset, will set on success.

  vSQl        =  sSql
  OBJECT CALL oconn.Execute (vsql, vnRow)
  iRet     =  OBJRESULT
  STDOUT   USING$("Insert on Connection iret #", iRet)
  IF Iret    = %S_OK THEN
      Funcval =  0& ' success
      nRow     = VARIANT#(vNRow)
  ELSE
      OBJECT GET oConn.Errors.count TO vErrCount
      nError  = VARIANT#(vErrCount)
      STDOUT USING$ ("Found # errors", nError)
      sMsg  = ""
      FOR i = 1 TO nError
          vErrIndex =  I-1&
          OBJECT GET    oConn.Errors.Item(vErrIndex).Description TO vErrDesc
          s            =  VARIANT$(vErrDesc)
          STDOUT USING$ ("Error # description is '&'", I-1,S)
          ' this is correct why is it not getting back to caller?
          sMsg = sMsg & S
          IF I <> nError THEN
            sMSg = sMsg & $CRLF
          END IF

         NEXT I
  END IF

  FUNCTION = FuncVal


END FUNCTION

MCM