Announcement

Collapse
No announcement yet.

ADO example not PBWin9 compatible

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

  • Michael Mattias
    replied
    We have updates today.....

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

    MCM

    Leave a comment:


  • Michael Mattias
    replied
    Code:
     IsNew = NOT (SameAsBefore)

    Leave a comment:


  • José Roca
    replied
    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

    Leave a comment:


  • Michael Mattias
    replied
    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

    Leave a comment:


  • José Roca
    replied
    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, 07:23 PM.

    Leave a comment:


  • Michael Mattias
    replied
    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.

    Leave a comment:


  • Erich Schulman
    replied
    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.

    Leave a comment:


  • Michael Mattias
    replied
    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

    Leave a comment:


  • Erich Schulman
    replied
    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

    Leave a comment:


  • Michael Mattias
    replied
    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, 09:48 AM.

    Leave a comment:


  • Cliff Nichols
    replied
    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

    Leave a comment:


  • Michael Mattias
    replied
    > 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

    Leave a comment:


  • Erich Schulman
    replied
    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.

    Leave a comment:


  • Michael Mattias
    replied
    >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."

    Leave a comment:


  • José Roca
    replied
    If VARIANTVT returns VT_BOOL then use FORMAT$(CINT(VARIANT#(vValue))).

    Leave a comment:


  • Erich Schulman
    replied
    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?

    Leave a comment:


  • Michael Mattias
    replied
    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

    Leave a comment:


  • José Roca
    replied
    Generate a new include file with the new browser checking the "Generate dispatch interfaces only" option.

    Leave a comment:


  • Erich Schulman
    started a topic ADO example not PBWin9 compatible

    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.)
Working...
X