Insert data into a Azure Postgresql Tabelle

stonystony Member Posts: 120
Hello, I would like to fill several tables in an Azure Postgresql database from Navision (Onpromise). How could I do that.

Thanks
stony

Answers

  • txerifftxeriff Member Posts: 437
    edited 2021-11-08
    Hi,

    you can do TSQL from NAV using automations/dotnets.

    I have an old piece of code wrote ages ago for automations, you ccan try adapt it to dotnet variables or try google TSQL for NAV maybe?

    Please note:
    • Name DataType Subtype Length
    lADOConnection Automation 'Microsoft ActiveX Data Objects 6.1 Library'.Connection
    vString Text
    lADOCommand Automation 'Microsoft ActiveX Data Objects 6.1 Library'.Command
    lADORecordSet Automation 'Microsoft ActiveX Data Objects 6.1 Library'.Recordset
    vConnectionVariant Variant

    • Fieldseparator is a constant that I have defined: <->
    •SingleQuote is a constant with value <'>



    insert sentence:
    
    
    IF ISCLEAR(lADOConnection) THEN 
      CREATE(lADOConnection,TRUE,TRUE);
    lADOConnection.ConnectionString:=
        'Provider=SQLOLEDB.1;Password='+txtPassword+';Persist Security Info=True;User ID='+
        txtUserId+';Initial' +
        ' Catalog='+txtBaseDatos+';Data Source='+txtServer+';Use Procedure for Prepare=1;Auto' +
        ' Translate=True;Packet Size=4096;Use Encryption for' +
        ' Data=False;Tag with column collation when possible=False';
     
    lADOConnection.Open;
     
     
    vString :='INSERT INTO [dbo].[yourTablename] ([Date],[Model],[Operation],[Rtable],[Data],[Version]) VALUES'
                              +'('+SingleQuote+FORMAT(T50000.Date)+SingleQuote+','+SingleQuote+T50000.Model+SingleQuote+','+SingleQuote+T50000.Operation+SingleQuote+','+SingleQuote+T50000.Rtable+SingleQuote+','
                              +SingleQuote+vData+SingleQuote+','+SingleQuote+T50000.Version+SingleQuote+')';
    
    
    

    select sentence:
    
    IF ISCLEAR(lADOConnection) THEN 
      CREATE(lADOConnection,TRUE,TRUE);
    IF ISCLEAR(lADOCommand) THEN
      CREATE(lADOCommand,TRUE,TRUE);
    IF ISCLEAR(lADORecordSet) THEN
      CREATE(lADORecordSet,TRUE,TRUE);
    lADOConnection.ConnectionString:=
        'Provider=SQLOLEDB.1;Password='+txtPassword+';Persist Security Info=True;User ID='+
        txtUserId+';Initial' +
        ' Catalog='+txtBaseDatos+';Data Source='+txtServer+';Use Procedure for Prepare=1;Auto' +
        ' Translate=True;Packet Size=4096;Use Encryption for' +
        ' Data=False;Tag with column collation when possible=False';
    lADOConnection.Open;
    lADOCommand.CommandText:='SELECT [ID],'+fieldseparator+',[Date],'+fieldseparator+',[Model],'+fieldseparator+
                             ',[Operation],'+fieldseparator+',[Rtable],'+fieldseparator+',[Data],'+fieldseparator+',[Version]'+
                             ' FROM [DatabaseName].[dbo].[yourTablename]'+
                             ' WHERE [Rtable]='+singlequote+'RECEPTION'+singlequote+' AND ID=69'; //ID for testing purposes
    lADOCommand.CommandType(1);
    vconnectionvariant:=ladoconnection; //we must move to a variant type variable to work
    ladocommand.activeconnection:=vconnectionvariant;
    
    lADORecordSet:=lADOCommand.Execute;
    MESSAGE(FORMAT(lADORecordSet.GetString));
    
    lADOConnection.Close;
    CLEAR(lADOConnection);
    CLEAR(lADOCommand);
    CLEAR(lADORecordSet);
    
    

    read sql:
    
      lADORecordSet.MoveFirst;
      REPEAT
         vID:=lADORecordSet.Fields.Item('ID').Value;
         //vdate
         vDate:=FORMAT(lADORecordSet.Fields.Item('Date').Value);
         //Model
         vModel:=lADORecordSet.Fields.Item('Model').Value;
         //Operation
         vOperation:=lADORecordSet.Fields.Item('Operation').Value;
         //rtable
         vRtable:=lADORecordSet.Fields.Item('Rtable').Value;
         //data
         vDataLOC:=lADORecordSet.Fields.Item('Data').Value;
         //version
         vVersion:=lADORecordSet.Fields.Item('Version').Value;
      
    
          
       lADORecordSet.MoveNext;
      UNTIL lADORecordSet.EOF;
    
    
    
    useuful links:
    http://msdn.microsoft.com/en-us/library/windows/desktop/ms675065(v=vs.85).aspx

    http://msdn.microsoft.com/en-us/library/windows/desktop/ms676975(v=vs.85).aspx
Sign In or Register to comment.