SQL Function & ADO.NET

navuser1

Dear all,

Is it possible to call sql function to get the return value from it using CAL in Nav 2013 R2 Environment ?

Please let me know.

bbrown

I think with NAV 2013 you'd want to use the .NET_Interop. You can do this in classic NAV version using ADO. Since .NET_Interop provides similar function, I would think you could accomplish what you need.

navuser1

Thanks for reply.
Database is on NAV 2013 R2 & User Interface is RTC currently.

----
Actually we're trying to integrate the NAV database with a 3rd Party Software (SQL Database). The 3rd Party Software is basically used for POS operation. It's also maintaining Card Master information related to NAV Customer Master. The Cards are being read from Card Reader System. The Card Reader machines are integrated with NAV. NAV can read the Card.

Which we want that we'll read the card and then send a request to 3rd Party DB to get the Customer ID (In NAV Customer ID is Customer Code) from it.

We've written a function in 3rd Party SQL DB & the function has a return value. we want to execute this thing in NAV.

How to call this SQL function in NAV Environment to get return value ?

We're using 'Microsoft ActiveX Data Objects 2.8 Library for db communication.

Please help.

navuser1

I'm able to execute the sql query from the NAV 2013 R2 RTC environment and get the required dataset but still missing something to do to retrieve return value from a sql function.

Please help me otherwise I have to ](*,) for few more times.

bbrown

Post what you have done and I'll try to help you out. Just letting you know that I'm rather busy with customers so my reply may be delayed.

navuser1

Problem is solved.
Following cal code is written.

ConnectionString := 'Data Source='+ServerName+';'
+ 'Initial Catalog='+NavDb+';'
+ 'Trusted_Connection=True;';

SQLConnection := SQLConnection.SqlConnection(ConnectionString);


QueryinText :='SELECT [dbo].[GetCust](@input)';
SQLCommand := SQLCommand.SqlCommand(QueryinText,SQLConnection);
SQLCommand.Parameters.AddWithValue('@input',Nav_variable);
SQLCommand.CommandType :=SQLCommand.CommandType.Text;

SQLConnection.Open();
Text2 :=SQLCommand.ExecuteScalar;  // Text2 is receiving return value.
SQLConnection.Close;

If you have other ways please share.

I'm looking for optimize solution.