Insert data into a Azure Postgresql Tabelle

stony

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

Thanks
stony

txeriff

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