OBJECT Form 50091 SQL Query Test
{
  OBJECT-PROPERTIES
  {
    Date=11/23/12;
    Time=10:58:31 AM;
    Modified=Yes;
    Version List=DMS-TEST;
  }
  PROPERTIES
  {
    Width=15750;
    Height=8690;
    SaveValues=Yes;
    TableBoxID=1000000000;
    SourceTable=Table307;
    SourceTableTemporary=Yes;
    OnInit=BEGIN
             txtSQL :=
             'SELECT quantity=cast(sum(quantity) as float), [Item No_], [location code] '+
             'from [$company$item ledger entry] group by [Item No_], [location code]';
           END;

  }
  CONTROLS
  {
    { 1000000000;TableBox;110 ;2310 ;15400;6160 ;HorzGlue=Both;
                                                 VertGlue=Both }
    { 1000000001;TextBox;0    ;0    ;1700 ;0    ;ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr="Item No." }
    { 1000000002;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000001;
                                                 InColumnHeading=Yes }
    { 1000000003;TextBox;0    ;0    ;1700 ;0    ;ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr="Location Code" }
    { 1000000004;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000003;
                                                 InColumnHeading=Yes }
    { 1000000005;TextBox;0    ;0    ;2200 ;0    ;ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr=Quantity }
    { 1000000006;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000005;
                                                 InColumnHeading=Yes }
    { 1000000007;TextBox;0    ;0    ;1700 ;0    ;ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr="Dimension Entry No." }
    { 1000000008;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000007;
                                                 InColumnHeading=Yes }
    { 1000000009;TextBox;0    ;0    ;1700 ;0    ;ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr="Variant Code" }
    { 1000000010;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000009;
                                                 InColumnHeading=Yes }
    { 1000000011;TextBox;0    ;0    ;1700 ;0    ;ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr="Bin Code" }
    { 1000000012;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000011;
                                                 InColumnHeading=Yes }
    { 1000000013;TextBox;0    ;0    ;1700 ;0    ;ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr="Serial No." }
    { 1000000014;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000013;
                                                 InColumnHeading=Yes }
    { 1000000015;TextBox;0    ;0    ;1700 ;0    ;ParentControl=1000000000;
                                                 InColumn=Yes;
                                                 SourceExpr="Lot No." }
    { 1000000016;Label  ;0    ;0    ;0    ;0    ;ParentControl=1000000015;
                                                 InColumnHeading=Yes }
    { 1000000019;CommandButton;12760;110;2660;550;
                                                 HorzGlue=Right;
                                                 CaptionML=ENU=&Run;
                                                 OnPush=BEGIN
                                                          runSQL;
                                                        END;
                                                         }
    { 1000000017;TextBox;1430 ;110  ;11220;1760 ;HorzGlue=Both;
                                                 VertAlign=Top;
                                                 MultiLine=Yes;
                                                 CaptionML=ENU=SQL;
                                                 SourceExpr=txtSQL }
    { 1000000018;Label  ;110  ;110  ;1210 ;440  ;ParentControl=1000000017 }
    { 1000000020;CommandButton;12760;770;2640;550;
                                                 HorzGlue=Right;
                                                 CaptionML=ENU=&Clear;
                                                 OnPush=BEGIN
                                                          RESET;
                                                          DELETEALL;
                                                        END;
                                                         }
    { 1000000021;CheckBox;12760;1430;2640 ;440  ;HorzGlue=Right;
                                                 CaptionML=ENU=Field by position;
                                                 ToolTipML=ENU=Match fields by position instead of by name;
                                                 SourceExpr=bByPos }
  }
  CODE
  {
    VAR
      txtSQL@1000000000 : Text[1024];
      bByPos@1000000001 : Boolean;

    PROCEDURE runSQL@1000000001();
    VAR
      lcuSQLQry@1000000000 : Codeunit 50091;
      lrrRecRef@1000000001 : RecordRef;
    BEGIN
      RESET;
      DELETEALL;

      lrrRecRef.GETTABLE(Rec);
      lcuSQLQry.getRecordFromSQL(txtSQL, lrrRecRef, bByPos);
      MESSAGE('Returned %1 records', COUNT);
      CurrForm.UPDATE(FALSE);
    END;

    BEGIN
    END.
  }
}

OBJECT Codeunit 50091 SQL Query
{
  OBJECT-PROPERTIES
  {
    Date=10/13/11;
    Time=12:45:59 PM;
    Modified=Yes;
    Version List=DMS;
  }
  PROPERTIES
  {
    OnRun=VAR
            ltrecInvBuf@1000000000 : TEMPORARY Record 307;
            lrrRec@1000000001 : RecordRef;
          BEGIN
          END;

  }
  CODE
  {
    VAR
      bConnected@1000000000 : Boolean;
      adoConn@1000000001 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000514-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Connection";

    PROCEDURE getRecordFromSQL@1000000002(ptxtSQL@1000000000 : Text[1024];prrRecRef@1000000002 : RecordRef;pbMatchFieldByPos@1000000011 : Boolean);
    VAR
      lrsRecordset@1000000001 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000535-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Recordset";
      lnAdoFldIndex@1000000004 : Integer;
      lnAdoFldCount@1000000005 : Integer;
      lnNavFldIndex@1000000006 : Integer;
      lnNavFldCount@1000000007 : Integer;
      lfrField@1000000008 : FieldRef;
      ltxtNavFldName@1000000009 : Text[30];
      ltxtAdoFldName@1000000013 : Text[30];
      lanFieldRefIDs@1000000003 : ARRAY [40] OF Integer;
      ltxtDebug@1000000010 : Text[30];
      lnCompanyPos@1000000012 : Integer;
      ltxtValue@1000000014 : Text[200];
      ldValue@1000000015 : Decimal;
      ldtValue@1000000016 : Date;
    BEGIN
      //<DMS author="M.Hamblin" date="7/19/2010" issue="--" >
      // Runs SQL and populates a record with results.  Maps returned field name to field name or by position.
      // WARNING: You must make sure the recordref passed in is temporary.
      //</DMS>

      initializeConnection;

      lnCompanyPos := STRPOS(ptxtSQL, '$company$');
      REPEAT
        IF lnCompanyPos<>0 THEN BEGIN
          ptxtSQL := DELSTR(ptxtSQL, lnCompanyPos, 8);
          ptxtSQL := INSSTR(ptxtSQL, COMPANYNAME, lnCompanyPos);
        END;//if
        lnCompanyPos := STRPOS(ptxtSQL, '$company$');
      UNTIL lnCompanyPos = 0;

      lrsRecordset := adoConn.Execute(ptxtSQL);

      // map fields to underlying record object
      lnAdoFldCount := lrsRecordset.Fields.Count;
      lnNavFldCount := prrRecRef.FIELDCOUNT;
      FOR lnAdoFldIndex := 1 TO lnAdoFldCount DO BEGIN
        IF pbMatchFieldByPos THEN
          lanFieldRefIDs[lnAdoFldIndex] := lnAdoFldIndex
        ELSE BEGIN
          lnNavFldIndex := 1;
          REPEAT
            lfrField := prrRecRef.FIELDINDEX(lnNavFldIndex);
            ltxtNavFldName := UPPERCASE(CONVERTSTR(lfrField.NAME, '."\/''', '_____'));
            ltxtAdoFldName := UPPERCASE(lrsRecordset.Fields.Item(lnAdoFldIndex-1).Name);
            IF (ltxtAdoFldName = ltxtNavFldName) OR
               (ltxtAdoFldName = UPPERCASE(lfrField.NAME))
            THEN BEGIN
              lanFieldRefIDs[lnAdoFldIndex] := lnNavFldIndex;
            END;//if

            lnNavFldIndex += 1;
          UNTIL (lanFieldRefIDs[lnAdoFldIndex]<>0) OR (lnNavFldIndex > lnNavFldCount);
        END;//if not match field by pos
      END;//for fld index

      WHILE lrsRecordset.EOF = FALSE DO BEGIN
        FOR lnAdoFldIndex := 1 TO lnAdoFldCount DO BEGIN
          IF lanFieldRefIDs[lnAdoFldIndex]<>0 THEN BEGIN

            lfrField := prrRecRef.FIELDINDEX(lanFieldRefIDs[lnAdoFldIndex]);
            ltxtValue := FORMAT(lrsRecordset.Fields.Item(lnAdoFldIndex-1).Value);

            CASE FORMAT(lfrField.TYPE) OF
              'Code', 'Text':
                lfrField.VALUE(ltxtValue);
              'Date':
              BEGIN
                EVALUATE(ldtValue, ltxtValue);
                lfrField.VALUE(ldValue);
              END;
              ELSE BEGIN
                EVALUATE(ldValue, ltxtValue);
                lfrField.VALUE(ldValue);
              END;//else
            END;//case
          END;//if
        END;//for

        prrRecRef.INSERT;
        lrsRecordset.MoveNext;
      END;//while

      CLEAR(lrsRecordset);
    END;

    PROCEDURE getADORecordSet@1000000003();
    BEGIN
    END;

    PROCEDURE executSQL@1000000001(ptxtSQL@1000000000 : Text[1024]);
    BEGIN
      //<DMS author="M.Hamblin" date="7/19/2010" issue="--" >
      // Runs SQL command with no result set
      //</DMS>

      initializeConnection;
      adoConn.Execute(ptxtSQL);
    END;

    LOCAL PROCEDURE initializeConnection@1000000004();
    VAR
      ltcConnStr@1000000000 : TextConst 'ENC="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=%1;Data Source=%2"';
    BEGIN
      // do connection to DB here
      IF ISCLEAR(adoConn) THEN BEGIN
       CREATE(adoConn);
       adoConn.Open(STRSUBSTNO(ltcConnStr, getURLPart('database'), getURLPart('servername')));
      END;
    END;

    LOCAL PROCEDURE getURLPart@1000000000(sURLPart@1000000000 : Text[30]) : Text[100];
    VAR
      sTemp@1000000001 : Text[255];
      nPos@1000000002 : Integer;
    BEGIN
      //<DMS author="MH" date="4/16/2007" >
      // Strips part of the URL from the current ContextURL

      sTemp := CONTEXTURL;

      nPos := STRPOS(sTemp, sURLPart);
      sTemp := COPYSTR(sTemp, nPos+STRLEN(sURLPart)+1, 100);
      sTemp := COPYSTR(sTemp, 1, STRPOS(sTemp, '&')-1);

      EXIT(sTemp);

      //</DMS>
    END;

    EVENT adoConn@1000000001::InfoMessage@0(pError@1000000002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000500-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Error";adStatus@1000000001 : Integer;pConnection@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000550-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Connection");
    BEGIN
    END;

    EVENT adoConn@1000000001::BeginTransComplete@1(TransactionLevel@1000000003 : Integer;pError@1000000002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000500-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Error";adStatus@1000000001 : Integer;pConnection@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000550-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Connection");
    BEGIN
    END;

    EVENT adoConn@1000000001::CommitTransComplete@3(pError@1000000002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000500-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Error";adStatus@1000000001 : Integer;pConnection@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000550-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Connection");
    BEGIN
    END;

    EVENT adoConn@1000000001::RollbackTransComplete@2(pError@1000000002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000500-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Error";adStatus@1000000001 : Integer;pConnection@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000550-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Connection");
    BEGIN
    END;

    EVENT adoConn@1000000001::WillExecute@4(VAR Source@1000000007 : Text[1024];CursorType@1000000006 : Integer;LockType@1000000005 : Integer;VAR Options@1000000004 : Integer;adStatus@1000000003 : Integer;pCommand@1000000002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{B08400BD-F9D1-4D02-B856-71D5DBA123E9}:'Microsoft ActiveX Data Objects 2.8 Library'._Command";pRecordset@1000000001 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Recordset";pConnection@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000550-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Connection");
    BEGIN
    END;

    EVENT adoConn@1000000001::ExecuteComplete@5(RecordsAffected@1000000005 : Integer;pError@1000000004 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000500-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Error";adStatus@1000000003 : Integer;pCommand@1000000002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{B08400BD-F9D1-4D02-B856-71D5DBA123E9}:'Microsoft ActiveX Data Objects 2.8 Library'._Command";pRecordset@1000000001 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000556-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Recordset";pConnection@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000550-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Connection");
    BEGIN
    END;

    EVENT adoConn@1000000001::WillConnect@6(VAR ConnectionString@1000000005 : Text[1024];VAR UserID@1000000004 : Text[1024];VAR Password@1000000003 : Text[1024];VAR Options@1000000002 : Integer;adStatus@1000000001 : Integer;pConnection@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000550-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Connection");
    BEGIN
    END;

    EVENT adoConn@1000000001::ConnectComplete@7(pError@1000000002 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000500-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'.Error";adStatus@1000000001 : Integer;pConnection@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000550-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Connection");
    BEGIN
    END;

    EVENT adoConn@1000000001::Disconnect@8(adStatus@1000000001 : Integer;pConnection@1000000000 : Automation "{2A75196C-D9EB-4129-B803-931327F72D5C} 2.8:{00000550-0000-0010-8000-00AA006D2EA4}:'Microsoft ActiveX Data Objects 2.8 Library'._Connection");
    BEGIN
    END;

    BEGIN
    {
      ************************
      Copyright Notice
      This objects content is copyright of Dynamic Manufacturing Solutions Inc 2011.  All rights reserved.
      Any redistribution or reproduction of part or all of the contents in any form is prohibited.
      ************************



      <DMS>
       <REVISION author="M.Hamblin" date="7/19/2010" version="DMS" issue="--">
         Does generic query and fills in temporary record with results.
         Some notes:
          - you must cast decimal values as "Floats" in SQL - e.g., select convert(Float, sum(Amount)) from ...
          - NULL values are a no-no.  Must use ISNULL to eliminate
          - you must make sure the recordref passed in is temporary
          - note that collation in temporary tables likely will not match NAV/SQL collation
          - SQL field names containing '_' instead of '.' (etc.) are fine
          - put '$company$' before table names for tables that are company-specific
          - you can match fields by name or by position they're returned (name is safer)
          - user must be given explicit access (via roles or permissions) to SQL Server objects via their SQL login
       </REVISION>
      </DMS>
    }
    END.
  }
}

