Announcement

**José Roca** · 3 Nov 2008, 04:59 PM

Generate a new include file with the new browser checking the "Generate dispatch interfaces only" option.

**Michael Mattias** · 4 Nov 2008, 08:23 AM

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

**Erich Schulman** · 4 Nov 2008, 02:43 PM

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?

**José Roca** · 4 Nov 2008, 03:07 PM

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

**Michael Mattias** · 4 Nov 2008, 04:21 PM

>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."

**Erich Schulman** · 4 Nov 2008, 11:14 PM

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.

**Michael Mattias** · 5 Nov 2008, 01:31 PM

> 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

**Cliff Nichols** · 5 Nov 2008, 07:09 PM

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

**Michael Mattias** · 6 Nov 2008, 08:41 AM

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

**Erich Schulman** · 6 Nov 2008, 11:15 AM

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

**Michael Mattias** · 6 Nov 2008, 11:33 AM

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

**Erich Schulman** · 6 Nov 2008, 11:58 AM

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.

**Michael Mattias** · 6 Nov 2008, 04:02 PM

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.

**José Roca** · 6 Nov 2008, 06:02 PM

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.

**Michael Mattias** · 6 Nov 2008, 07:25 PM

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

**José Roca** · 7 Nov 2008, 04:17 AM

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

**Michael Mattias** · 7 Nov 2008, 06:58 AM

Code:

 IsNew = NOT (SameAsBefore)

**Michael Mattias** · 8 Nov 2008, 11:07 AM

We have updates today.....

PowerBASIC

http://www.powerbasic.com/support/pbforums/showpost.php?p=301638&postcount=3

MCM

Announcement

ADO example not PBWin9 compatible

ADO example not PBWin9 compatible

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment