Announcement

Collapse
No announcement yet.

ADO example not PBWin9 compatible

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • ADO example not PBWin9 compatible

    I downloaded the ADO sample at
    http://www.powerbasic.com/support/do...DO_Example.zip

    When trying to compile, I get an error in msado15.inc at line 705
    Code:
    MEMBER GET SIZE <&H00000006>() AS ADODBADO_LONGPTR
    Outside of Python, I am no good with objects so what will I need to do to make this usable in v9? (I am already aware that INTERFACE DISPATCH is now deprecated.)
    Erich Schulman (KT4VOL/KTN4CA)
    Go Big Orange

  • #2
    Generate a new include file with the new browser checking the "Generate dispatch interfaces only" option.
    Forum: http://www.jose.it-berater.org/smfforum/index.php

    Comment


    • #3
      There is a new ADO sample you might be interested in...
      Generic 'ADO' Connection and Query Tester (CC 5+/Win 9+) 11-02-08

      Lots of comments included, just as in all my demos.

      Compiles and runs 'as is' either with PB/CC 5 or PB/WIN 9.

      MCM
      Michael Mattias
      Tal Systems Inc. (retired)
      Racine WI USA
      [email protected]
      http://www.talsystems.com

      Comment


      • #4
        Originally posted by Michael Mattias View Post
        There is a new ADO sample you might be interested in...
        Generic 'ADO' Connection and Query Tester (CC 5+/Win 9+) 11-02-08

        Lots of comments included, just as in all my demos.

        Compiles and runs 'as is' either with PB/CC 5 or PB/WIN 9.

        MCM
        Tentatively, I am going to adopt this approach instead. I have encountered one problem, though. Bit (Boolean) columns always return UNKNOWN. This is with SQL Server 2005 Express. I don't want to change column definitions, so where in the code do we adjust for that kind of column?
        Erich Schulman (KT4VOL/KTN4CA)
        Go Big Orange

        Comment


        • #5
          If VARIANTVT returns VT_BOOL then use FORMAT$(CINT(VARIANT#(vValue))).
          Forum: http://www.jose.it-berater.org/smfforum/index.php

          Comment


          • #6
            >If VARIANTVT returns VT_BOOL then use FORMAT$(CINT(VARIANT#(vValue))).

            Aha!

            As you can see in my code, I am trying to cover all the possibilities explicitly rather than implicitly. This is one I did not encounter before, so I will add it..

            FWIW, I got the code for VT_DATE and VT_DECIMAL from two unrelated posts here. Amazing what you can find with "search."
            Michael Mattias
            Tal Systems Inc. (retired)
            Racine WI USA
            [email protected]
            http://www.talsystems.com

            Comment


            • #7
              I added VT_BOOL to my copy of the code.

              I have already adapted the code to display tables in Egrid32demo. Next I will need to adapt it for MLG and then decide which to use for the remainder of the project.
              Erich Schulman (KT4VOL/KTN4CA)
              Go Big Orange

              Comment


              • #8
                > added VT_BOOL to my copy of the code

                I do not have any databases which support a "Boolean" as far as I can tell (I have oracle, jet and Excel - which I have working using the connection string below), but I can't create a "logical" column anywhere.

                What I think I want to do is something like
                Code:
                                CASE %VT_BOOL
                                      sValue = IIF$(ISTRUE CINT(VARIANT#(vvalue)), "1", "0")
                However, I can't test that... so I am reluctant to modify the code.

                If your SQL Server DB has boolean columns, can you please set the values to true and false and confirm this will return "1" or "0" depending on that value?

                ALSO
                I just tried using the same program against MS-Excel Spreadsheet using the connection string I found at www.connectionstrings.com

                This worked great: (command file comments included for reference)
                Code:
                XXExcel  Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
                
                XX For Excel HDR=Yes/No, first row of worksheet is column headers 
                XX   "IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) 
                XX data columns as text. Note that this option might affect excel sheet write access negative.  
                XX ====  EXCEL SQL syntax  =====
                XX "SELECT * FROM [sheet1$]". I.e. excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.  
                XX --  END MS-EXCEL INFO 
                ....
                XX  NEXT WORKED FIRST TIME OUT OF THE BOX ON AN EXCEL SPREAD SHEET 11/5/08! 
                CONNECT=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Clients\Cloyes Gear\_System 
                Processing\ASN Production\Portal\BPITABLES.XLS;Extended Properties="Excel 
                8.0;HDR=Yes;IMEX=1";
                Query=Select * from [dbo_ship_carton$]
                XX LETS TRY SeLECTIVE COLUMNS using the header column names TOO
                XX THIS WORKS TOO!!!
                XXQuery=Select carton_no, carton_type from [dbo_ship_carton$] order by 2,1 
                QUERY=select gross_wgt,gross_wgt_uom, net_wgt, net_wgt_uom from [dbo_ship_carton$]
                I know "ADO" is probably "the long way" to go when there is a direct COM interface into MS-Excel, but for wimps like me who use Excel as a cheap and dirty database, this is terrific.

                MCM
                Michael Mattias
                Tal Systems Inc. (retired)
                Racine WI USA
                [email protected]
                http://www.talsystems.com

                Comment


                • #9
                  I know "ADO" is probably "the long way" to go when there is a direct COM interface into MS-Excel, but for wimps like me who use Excel as a cheap and dirty database, this is terrific.
                  Need a cup of spinach?

                  Honestly though, since I am not a database type of guy, I can see the point in understanding the "Under the Hood" rather than the COM way of "Just Use It" and not worry about how it works till a problem creeps up
                  Engineer's Motto: If it aint broke take it apart and fix it

                  "If at 1st you don't succeed... call it version 1.0"

                  "Half of Programming is coding"....."The other 90% is DEBUGGING"

                  "Document my code????" .... "WHYYY??? do you think they call it CODE? "

                  Comment


                  • #10
                    Updates 11/6/08

                    Demo program: Cleaned up comments. Added Public Domain Statement

                    ADOEXECTUTESELECT: Fixed bug in retrieval of row zero column names (fencepost error resulted in column names skipping first column and being 'off-by-one' for the rest)
                    . Added explicit handing of type VT_BOOL in fetch loop to returns "0" or "1" (NOT TESTED); cleaned up comments; added public domain statement.
                    -Enhanced VT_DATE return to return time IF non-zero. Now returns ccyy-mm-dd hh:mm:ss (24hour) if time found.

                    COMING SOON:
                    Will allow multiple-line SELECT statements. I was working with this last night and trying to type in long statements was a PITA. Would much rather use "Pb-like line continuation" as in

                    Code:
                    QUERY=  Select  X, _ 
                                           Y, _ 
                                          Z, _
                                 from   t1, _
                                          t2, _ 
                                         t3 
                                 UNION 
                                 Select d, e,f _
                                 from   w, x, y _ 
                                 where  _ 
                                       condition _
                                  and condition _
                                  and condition _ 
                                  Order by  1, 2, 3
                    Put that all on one line and you'll see what I mean.

                    Better still here is the actual query I was working with
                    Code:
                    SELECT 'N' ISKIT, SHIP_TO_CUST, SHIP_TO_CUST_NAME, USER_ORD_NO, ORD_LN_NO, PART_NO, USER_PART_NO, PART_DESC,LOT_SERIAL_NO, SHIP_UOM, ISNULL(SUM(SHIP_QTY),0) QTY, 
                      CUST_PURCH_ORD_NO, PART_REV, ORD_NO
                      FROM SHIP_CARTON SC INNER JOIN SHIP_CARTON_DTL SCD ON SC.CARTON_NO = SCD.CARTON_NO
                     WHERE SHIPMENTID = 501
                       AND SC.SEGMENT = 'CHICAGO'
                       AND ISNULL(KIT_PART_NO,'') = ''
                     GROUP BY USER_ORD_NO, ORD_LN_NO, USER_PART_NO, PART_DESC, PART_NO, LOT_SERIAL_NO, CUST_PURCH_ORD_NO, SHIP_UOM, PART_REV, SHIP_TO_CUST, SHIP_TO_CUST_NAME, ORD_NO
                     UNION
                     SELECT 'Y' ISKIT, SHIP_TO_CUST, SHIP_TO_CUST_NAME, USER_ORD_NO, ORD_LN_NO, NULL PART_NO, KIT_PART_NO USER_PART_NO, NULL PART_DESC, LOT_SERIAL_NO, SHIP_UOM, MIN(dbo.GetKitCartonShipQty(SCD.ORD_NO, SCD.ORD_LN_NO, NULL)) QTY,
                             CUST_PURCH_ORD_NO, PART_REV, ORD_NO
                       FROM SHIP_CARTON SC INNER JOIN SHIP_CARTON_DTL SCD ON SC.CARTON_NO = SCD.CARTON_NO
                     WHERE SC.SHIPMENTID = 501
                       AND ISNULL(SCD.KIT_PART_NO,'') <> ''
                       AND SC.SEGMENT = 'CHICAGO'
                     GROUP BY USER_ORD_NO, ORD_LN_NO, KIT_PART_NO, LOT_SERIAL_NO, SHIP_UOM, CUST_PURCH_ORD_NO, PART_REV, SHIP_TO_CUST, SHIP_TO_CUST_NAME, ORD_NO
                     ORDER BY USER_PART_NO, ORD_LN_NO
                    Imagine trying to edit that on one line using Notepad.



                    MCM
                    Last edited by Michael Mattias; 6 Nov 2008, 08:48 AM.
                    Michael Mattias
                    Tal Systems Inc. (retired)
                    Racine WI USA
                    [email protected]
                    http://www.talsystems.com

                    Comment


                    • #11
                      Originally posted by Michael Mattias View Post
                      > added VT_BOOL to my copy of the code

                      I do not have any databases which support a "Boolean" as far as I can tell (I have oracle, jet and Excel - which I have working using the connection string below), but I can't create a "logical" column anywhere.

                      What I think I want to do is something like
                      Code:
                                      CASE %VT_BOOL
                                            sValue = IIF$(ISTRUE CINT(VARIANT#(vvalue)), "1", "0")
                      However, I can't test that... so I am reluctant to modify the code.

                      If your SQL Server DB has boolean columns, can you please set the values to true and false and confirm this will return "1" or "0" depending on that value?
                      MCM
                      This application is using SQL Server 2005 Express which is free to download and use. (Personally, I prefer PostgreSQL but that's not my call.)

                      Here you will see the code I was using up til just now (commented out) and your code.
                      Code:
                                      CASE %VT_BOOL
                                          'sValue = FORMAT$(CINT(VARIANT#(vValue)))
                                          sValue = IIF$(ISTRUE CINT(VARIANT#(vvalue)), "1", "0")
                      My application displays results this way:
                      Code:
                                  'In city limits?
                                  SendMessage hEgridC, %EG_INTERNALSELECTTL, 5, iRow
                                  ThisCell = sRow(4,iRow)
                                  IF ThisCell = "0" THEN ThisCell = "No" ELSE ThisCell = "Yes"
                                  SendMessage hEgridC, %EG_SETCELLTEXT, STRPTR(ThisCell), LEN(ThisCell)
                                  'Mobile home?
                                  SendMessage hEgridC, %EG_INTERNALSELECTTL, 6, iRow
                                  ThisCell = sRow(6,iRow)
                                  IF ThisCell = "0" THEN ThisCell = "No" ELSE ThisCell = "Yes"
                                  SendMessage hEgridC, %EG_SETCELLTEXT, STRPTR(ThisCell), LEN(ThisCell)
                                  'Year built
                                  SendMessage hEgridC, %EG_INTERNALSELECTTL, 7, iRow
                                  ThisCell = sRow(5,iRow) : SendMessage hEgridC, %EG_SETCELLTEXT, STRPTR(ThisCell), LEN(ThisCell)
                      The MLG build is comparable. I am still trying to decide which to go with.

                      The attachments illustrate the data and table structure.
                      Attached Files
                      Erich Schulman (KT4VOL/KTN4CA)
                      Go Big Orange

                      Comment


                      • #12
                        Sounds like it works OK.

                        I wanted to convert to "0" and "1" in case the DBMS returned "0" and "<anything other than zero>" .. I wanted consistent values for true and false.

                        But it's not like it's hard to modify if you wanted, say "Yes" and "No", or "TRUE" or "FALSE", or even "RED" or "GREEN", is it?

                        MCM
                        Michael Mattias
                        Tal Systems Inc. (retired)
                        Racine WI USA
                        [email protected]
                        http://www.talsystems.com

                        Comment


                        • #13
                          Originally posted by Michael Mattias View Post
                          But it's not like it's hard to modify if you wanted, say "Yes" and "No", or "TRUE" or "FALSE", or even "RED" or "GREEN", is it?
                          MCM
                          Yes|No, hai|iie, True|False, AFC|NFC, Intel|AMD, etc. is all in the context of the application. I would rather let your code be generic and my application decide how to handle the result.

                          Not making changes directly in your code also mean I can update it without having to put it under Subversion and setting up vendor branches.
                          Erich Schulman (KT4VOL/KTN4CA)
                          Go Big Orange

                          Comment


                          • #14
                            Not making changes directly in your code also mean I can update it
                            I wrote that to be "#INCLUDE and #FORGET"

                            FWIW, used it with SQL Server yesterday (on a client system where I cannot ALTER TABLE to test BOOLEAN columns). Perfect.

                            This 'ADO' is turning out to be one hell of a tool.
                            Michael Mattias
                            Tal Systems Inc. (retired)
                            Racine WI USA
                            [email protected]
                            http://www.talsystems.com

                            Comment


                            • #15
                              An small example to illustrate some advantages of the improved OBJECT... syntax in the new compilers (you are still using the old syntax).

                              Code:
                              ' ########################################################################################
                              ' This example demonstrates the Execute method when run from a Connection object.
                              ' ########################################################################################
                              
                              ' SED_PBCC ' Use PBCC compiler
                              #COMPILE EXE
                              #DIM ALL
                              #INCLUDE ONCE "WIN32API.INC"
                              #INCLUDE ONCE "ADODB28DISP.INC"
                              
                              ' ========================================================================================
                              ' Main
                              ' ========================================================================================
                              FUNCTION PBMAIN
                              
                                 LOCAL oConnection AS Int__Connection
                                 LOCAL oRecordset AS Int__Recordset
                                 LOCAL vConStr AS VARIANT
                                 LOCAL vSqlStr AS VARIANT
                                 LOCAL vRes AS VARIANT
                                 LOCAL iEOF AS INTEGER
                                 LOCAL vField AS VARIANT
                                 LOCAL nState AS LONG
                              
                                 ' Create a Connection object
                                 oConnection = NEWCOM "ADODB.Connection"
                                 IF ISNOTHING(oConnection) THEN EXIT FUNCTION
                              
                                 TRY
                                    ' Connection String - Change it if needed
                                    vConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
                                    ' Open the connection
                                    OBJECT CALL oConnection.Open(vConStr)
                                    ' SQL query
                                    vSqlStr = "SELECT TOP 20 * FROM Authors ORDER BY Author"
                                    ' Create the recordset
                                    OBJECT CALL oConnection.Execute(vSqlStr) TO oRecordset
                                    DO
                                       ' While not at the end of the recordset...
                                       OBJECT GET oRecordset.EOF TO iEOF
                                       IF iEOF THEN EXIT DO
                                       ' Get the content of the "Author" column
                                       vField = "Author"
                                       OBJECT GET oRecordset.Collect(vField) TO vRes
                                       PRINT VARIANT$(vRes)
                                       ' Fetch the next row
                                       OBJECT CALL oRecordset.MoveNext
                                    LOOP
                                 CATCH
                                    ' TODO: Display error information
                                    ' ...
                                    ? HEX$(OBJRESULT)
                                    ? IDISPINFO.DESC$
                                 FINALLY
                                    ' Close and release the recordset
                                    OBJECT GET oRecordset.State TO nState
                                    IF nState = %adStateOpen THEN
                                       OBJECT CALL oRecordset.Close
                                    END IF
                                    oRecordset = NOTHING
                                    ' Close the connection
                                    OBJECT GET oConnection.State TO nState
                                    IF nState = %adStateOpen THEN
                                       OBJECT CALL oConnection.Close
                                    END IF
                                 END TRY
                              
                                 ' Release the objects
                                 oConnection = NOTHING
                              
                                 WAITKEY$
                              
                              END FUNCTION
                              ' ========================================================================================
                              In your example, instead of

                              Code:
                              OBJECT CALL  oConn.Execute (vSql, vnRow) TO vW
                              LET oRS  =  vW
                              You can use

                              Code:
                              OBJECT CALL  oConn.Execute (vSql, vnRow) TO oRS
                              When the parameter is not declared as VARIANT in the interface definition, you can use standard variable types if you wish, e.g.

                              Instead of

                              Code:
                              OBJECT GET oRs.EOF  TO vEOF
                              IF ISTRUE VARIANT#(vEof) THEN
                              You can use

                              Code:
                              DIM iEOF AS INTEGER
                              OBJECT GET oRs.EOF  TO iEOF
                              IF iEOF THEN
                              Instead of

                              Code:
                              DIM vColName AS VARIANT
                              FOR icol  = 0 TO ncol-1   ' uses index
                                   LET vColIndex  = iCOL
                                   OBJECT GET       oFields.Item (vColIndex) TO vField
                                   LET   oField   = vField
                                   OBJECT GET       oField.Name  TO vColName
                                   sRow (iCol +1, iRow) = VARIANT$(vColName)
                              NEXT
                              You can use

                              Code:
                              DIM strColName AS STRING
                              FOR icol  = 0 TO ncol-1   ' uses index
                                   LET vColIndex  = iCOL
                                   OBJECT GET       oFields.Item(vColIndex).Name TO strColName
                                   sRow (iCol +1, iRow) = ACODE$(strColName)
                              NEXT
                              It is also advisable to close the Recordset before closing the Connection.
                              Last edited by José Roca; 6 Nov 2008, 06:23 PM.
                              Forum: http://www.jose.it-berater.org/smfforum/index.php

                              Comment


                              • #16
                                I'll check the RecordSet status and close it if open. Thanks for tip.

                                On the (subjectively) "great new syntax.."

                                Most of the examples of "ADO" I could find started and were built around the RecordSet object. I started with the Connection object because it "sounded kind of like" the "connection handle" I was used to working with with ODBC.

                                So.. I first tried ADO with Win 8x. 9x came out just after I got it working with 8x.

                                Then, I had the problem requiring the IDISPINFO data... so I ordered and installed 9x.

                                Then I discovered the existing COM-browser libarary file was incompatible with 9x.

                                Then I had to figure out the new COM browser to get the old interfaces (the interfces I had had working)

                                Then I got my "IDISPINFO" info.

                                Then I had to rewrite the function to make it parameter-driven AND generic.

                                New syntax can wait.

                                MCM
                                Michael Mattias
                                Tal Systems Inc. (retired)
                                Racine WI USA
                                [email protected]
                                http://www.talsystems.com

                                Comment


                                • #17
                                  On the (subjectively) "great new syntax.."
                                  To clarify: I'm, not talking about the new direct interface calls support, but about improvements in the syntax for the old OBJECT CALL/GET/SET/LET, that now allows the use of variables instead of always variants.

                                  You no longer need to use:

                                  Code:
                                  OBJECT CALL  oConn.Execute (vSql, vnRow) TO vW
                                  LET oRS  =  vW
                                  Now, you can use:

                                  Code:
                                  OBJECT CALL  oConn.Execute (vSql, vnRow) TO oRS
                                  Forum: http://www.jose.it-berater.org/smfforum/index.php

                                  Comment


                                  • #18
                                    Code:
                                     IsNew = NOT (SameAsBefore)
                                    Michael Mattias
                                    Tal Systems Inc. (retired)
                                    Racine WI USA
                                    [email protected]
                                    http://www.talsystems.com

                                    Comment


                                    • #19
                                      We have updates today.....

                                      http://www.powerbasic.com/support/pb...38&postcount=3

                                      MCM
                                      Michael Mattias
                                      Tal Systems Inc. (retired)
                                      Racine WI USA
                                      [email protected]
                                      http://www.talsystems.com

                                      Comment

                                      Working...
                                      X