OBJECT Codeunit 50091 SQL Query
{
  OBJECT-PROPERTIES
  {
    Date=11/23/12;
    Time=11:04:14 AM;
    Modified=Yes;
    Version List=DMS,SQL;
  }
  PROPERTIES
  {
    OnRun=VAR
            ltrecInvBuf@1000000000 : TEMPORARY Record 307;
            lrrRec@1000000001 : RecordRef;
          BEGIN
          END;

  }
  CODE
  {
    VAR
      bConnected@1000000000 : Boolean;
      adoConn@1000000002 : DotNet "'ADODB, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'.ADODB.Connection";
      nTemp@1000000001 : Integer;
      bInitialized@1000000003 : Boolean;
      tcErrNotInit@1000000004 : TextConst 'ENU=Connection not initialized.\Call "initialize" first.;ENC=Connection not initialized.\Call "initialize" first.';
      tcErrNotTemp@1000000005 : TextConst 'ENU=Record is not temporary;ENC=Record is not temporary';

    PROCEDURE getRecordFromSQL@1000000002(ptxtSQL@1000000000 : Text;prrRecRef@1000000002 : RecordRef;pbMatchFieldByPos@1000000011 : Boolean);
    VAR
      lrsRecordset@1000000001 : DotNet "'ADODB, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'.ADODB.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;
      txtCompany@1000000017 : Text;
      lnRecCount@1000000018 : Integer;
      fFields@1000000019 : DotNet "'ADODB, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'.ADODB.Fields";
      fField@1000000020 : DotNet "'ADODB, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'.ADODB.Field";
    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>

      IF NOT prrRecRef.ISTEMPORARY THEN
        ERROR(tcErrNotTemp);

      testInitialized;

      txtCompany := CONVERTSTR(COMPANYNAME, '."\/''%][', '________');

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

      lrsRecordset := adoConn.Execute(ptxtSQL, lnRecCount, 0);

      // map fields to underlying record object
      fFields := lrsRecordset.Fields;
      lnAdoFldCount := fFields.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, '."\/''', '_____'));
            fField := fFields.Item(lnAdoFldIndex-1);
            ltxtAdoFldName := UPPERCASE(fField.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
        prrRecRef.INIT;
        FOR lnAdoFldIndex := 1 TO lnAdoFldCount DO BEGIN
          IF lanFieldRefIDs[lnAdoFldIndex]<>0 THEN BEGIN

            lfrField := prrRecRef.FIELDINDEX(lanFieldRefIDs[lnAdoFldIndex]);
            fField := fFields.Item(lnAdoFldIndex-1);
            ltxtValue := FORMAT(fField.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
    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>
      testInitialized;
      adoConn.Execute(ptxtSQL, nTemp, 0);
    END;

    PROCEDURE initialize@1000000004(ptxtDBServer@1000000002 : Text;ptxtDBName@1000000003 : Text);
    VAR
      ltcConnStr@1000000000 : TextConst 'ENC="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=%1;Data Source=%2"';
      loCreator@1000000001 : DotNet "'ADODB, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'.ADODB.ConnectionClass";
    BEGIN
      // do connection to DB here
      IF NOT bInitialized THEN BEGIN
       adoConn :=  loCreator.ConnectionClass();
       adoConn.Open((STRSUBSTNO(ltcConnStr, ptxtDBName, ptxtDBServer)), '', '', 0);
       bInitialized := TRUE;
      END;
    END;

    LOCAL PROCEDURE testInitialized@1000000006();
    BEGIN
      //<DMS author="M.Hamblin" date="7/19/2010" issue="--" >
      // Runs SQL command with no result set
      //</DMS>
      IF NOT bInitialized THEN
        ERROR(tcErrNotInit);
    END;

    BEGIN
    {
      ************************
      Copyright Notice

      This objects content is copyright (2010) of Dynamic Manufacturing Solutions Inc.  All rights reserved.
      Reproduction. modification, or distribution of part or all of the contents in any form is prohibited
      unless this copyright notice is included.

      Code is free for personal and commercial use, modification, or redistribution.

      www.dynms.com
      ************************


      <DMS>
       <REVISION author="M.Hamblin" date="7/19/2010" version="DMS" issue="--">
         Does generic query and fills in temporary record with results.
         Some notes:
          - 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.
  }
}

OBJECT Page 50091 Test SQL
{
  OBJECT-PROPERTIES
  {
    Date=11/23/12;
    Time=11:04:31 AM;
    Modified=Yes;
    Version List=DMS,SQL;
  }
  PROPERTIES
  {
    SaveValues=Yes;
    SourceTable=Table307;
    PageType=List;
    SourceTableTemporary=Yes;
    OnOpenPage=BEGIN
                 setDBFields;
                 txtSQL :='SELECT quantity=sum(quantity), [Item No_], [location code] '+
                          'from [$company$item ledger entry] group by [Item No_], [location code]';
               END;

    ActionList=ACTIONS
    {
      { 1000000010;  ;ActionContainer;
                      ActionContainerType=ActionItems }
      { 1000000011;1 ;Action    ;
                      Name=Execute Query;
                      Promoted=Yes;
                      PromotedCategory=Process;
                      OnAction=BEGIN
                                 runQuery;
                               END;
                                }
    }
  }
  CONTROLS
  {
    { 1000000000;0;Container;
                ContainerType=ContentArea }

    { 1000000012;1;Group  ;
                CaptionML=[ENU=Database;
                           ENC=Database];
                GroupType=Group }

    { 1000000016;2;Group  ;
                GroupType=GridLayout;
                Layout=Columns }

    { 1000000017;3;Group  ;
                GroupType=Group }

    { 1000000013;4;Field  ;
                Name=Database Server;
                CaptionML=[ENU=Database Server;
                           ENC=Database Server];
                SourceExpr=txtDBServer }

    { 1000000015;4;Field  ;
                Name=SQL;
                CaptionML=[ENU=SQL;
                           ENC=SQL];
                SourceExpr=txtSQL;
                MultiLine=Yes }

    { 1000000018;3;Group  ;
                GroupType=Group;
                Layout=Columns }

    { 1000000014;4;Field  ;
                Name=Database Name;
                CaptionML=[ENU=Database Name;
                           ENC=Database Name];
                SourceExpr=txtDBName }

    { 1000000001;1;Group  ;
                Name=Group;
                CaptionML=[ENU=Data;
                           ENC=Data];
                GroupType=Repeater }

    { 1000000002;2;Field  ;
                SourceExpr="Item No." }

    { 1000000003;2;Field  ;
                SourceExpr="Location Code" }

    { 1000000004;2;Field  ;
                SourceExpr=Quantity }

    { 1000000005;2;Field  ;
                SourceExpr="Dimension Entry No." }

    { 1000000006;2;Field  ;
                SourceExpr="Variant Code" }

    { 1000000007;2;Field  ;
                SourceExpr="Bin Code" }

    { 1000000008;2;Field  ;
                SourceExpr="Serial No." }

    { 1000000009;2;Field  ;
                SourceExpr="Lot No." }

  }
  CODE
  {
    VAR
      cuSQLQry@1000000000 : Codeunit 50091;
      txtSQL@1000000001 : Text;
      txtDBServer@1000000002 : Text;
      txtDBName@1000000003 : Text;

    PROCEDURE setDBFields@1000000000();
    VAR
      lrecActiveSession@1000000000 : Record 2000000110;
    BEGIN
      //<DMS author="M.Hamblin" date="11/19/2012" issue="--" >
      // Set option fields at startup
      //</DMS>

      IF txtDBServer<>'' THEN
        EXIT;

      lrecActiveSession.GET(SERVICEINSTANCEID, SESSIONID);

      txtDBServer := lrecActiveSession."Server Computer Name";
      txtDBName := lrecActiveSession."Database Name";
    END;

    PROCEDURE runQuery@1000000003();
    VAR
      lrrRecref@1000000000 : RecordRef;
    BEGIN
      //<DMS author="M.Hamblin" date="11/19/2012" issue="--" >
      // Executes query
      //</DMS>

      RESET;
      DELETEALL;

      CLEAR(Rec);  // need this for strange 2013 page behaviour
      cuSQLQry.initialize(txtDBServer, txtDBName);
      lrrRecref.GETTABLE(Rec);
      cuSQLQry.getRecordFromSQL(txtSQL, lrrRecref, FALSE);


      MESSAGE('Returned %1 records', COUNT);
    END;

    BEGIN
    {
      ************************
      Copyright Notice

      This objects content is copyright (2010) of Dynamic Manufacturing Solutions Inc.  All rights reserved.
      Reproduction. modification, or distribution of part or all of the contents in any form is prohibited
      unless this copyright notice is included.

      Code is free for personal and commercial use, modification, or redistribution.

      www.dynms.com
      ************************


      <DMS>
       <REVISION author="M.Hamblin" date="11/19/2012" version="DMS" issue="--">
         test page for evaluating the SQL Query objects
       </REVISION>
      </DMS>
    }
    END.
  }
}

