Insert data into a Azure Postgresql Tabelle

Answers

• Options

  txeriff Member Posts: 492

  2021-11-08 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

  0