Logging actual SQL commands on server side


(Alan Olson) #1

Hi all -

I’m very new to Data Abstract, but have been using the ROSDK for many years. I’ve been experimenting and learning how DA works, and how it differs from ROSDK programming, and I’ve been quite impressed.

One question that I have now - is there any way on the server side to be able to actually log to a debug file the generated SQL commands before they are executed? I’d like to see how my DALinq client side code is converted to actual SQL.

Thanks,

Alan


(andreyt) #2

Hello Alan.

Yes, it is possible to get and log SQL statement before executing it. Please take a look at the article “Business Processors” (see it at http://wiki.remobjects.com/wiki/Business_Processors).Business Processors are used to control any manipulation with database. To get SQL statement using Business Processor you need to implement AfterSqlGeneration event (http://wiki.remobjects.com/wiki/BusinessProcessor_Class):

AfterSqlGeneration event “occurs after the business processor auto generates the SQL text used to process a specific type of change (insert, delete or update). A common use of this event could be to track the SQL being executed and log it in a text file for debugging purposes”.

Also please take a look at RemObjects DataAbstract sample “Server” (see it at “C:\Users\Public\Documents\RemObjects Samples\Data Abstract for .NET\Server”). It shows using AfterSqlGeneration event in action.

Thanks.


(Alan Olson) #3

I know this is an old question, but…is this also possible if you are using Relativity Server?


(antonk) #4

No. The only way to do access this data would be a Scripting event. Unfortunately Scripting API doesn’t expose these 2 events that might allow one to inspect the underlying database-level SQL.

I’ll log an issue to take a look on this.


(RemObjects) #5

Thanks, logged as bugs://81443


(Alan Olson) #6

Have you looked into this at all? I have a simple DALINQ query failing on relativity server for PSQL and I’d love to see the generated SQL


(antonk) #7

Not yet. You could use the PostgreSQL logging for the same purpose


(Alan Olson) #8

This is Pervasive.SQL, not Postgres


(antonk) #9

I see.

Well, let’s use some black magic then.

Take a look at this simple test project:

RelativityServerLogChecker.zip (19.3 KB)

What you need to do is to change its Main method to

	static void Main(string[] args)
	{
		var message = new BinMessage();
		var channel = new IpHttpClientChannel { TargetUrl = "http://localhost:7099/bin" };
		var loginService = CoLoginService.Create(message, channel);

		message.ClientID = new Guid("6B0946FB-9780-42D2-98A5-0192E7A7C4C6"); // TODO Chenage to actual Session ID

		loginService.Login("simple", "simple", "DASamples", "PCTrade");

		Console.WriteLine("Session ID: " + message.ClientID);

		var dataService = CoDataService.Create(message, channel);

		Console.WriteLine("Press ENTER to enable logging");
		Console.ReadLine();
		// Enable logginng
		dataService.SetCollectRequestInfoMode(true);


		while (true)
		{
			Console.WriteLine("Press ENTER to retrieve trace info");

			Console.ReadLine();

			// Try to retrieve logs from the server
			var requestInfo = dataService.GetLastRequestInfo();

			Console.WriteLine("Request Info");
			Console.WriteLine("Database SQL:");
			foreach (var entry in requestInfo.DatabaseSqlLog)
			{
				Console.WriteLine(entry.Category + ": " + entry.Message);
			}
		}
	}

You’ll need to log in in your real client app, take its session ID and put it to the line message.ClientID = ...

Then run the app. It will enable tracing mode on the provided session ID and then will display you the trace info.

Run the offending operation and check its logs

Note: This is an experimental feature so it might throw exceptions.


(Alan Olson) #10

well I found a 3rd party utility to log the actual SQL going to the Pervasive server. Here is what I get with my simple DALINQ query that should NOT have thrown an error:

SELECT "t0"."AddressLine1", "t0"."AddressLine2", "t0"."AdStarCode", "t0"."APClerk", "t0"."AutomaticNet", "t0"."Blank4a", "t0"."Blank7", "t0"."BoxCharge", "t0"."BSSPubNumber", "t0"."City", "t0"."ClassCodeJustify", "t0"."ClassCodeLength", "t0"."ContactName", "t0"."ContactPhoneNumber", "t0"."ContactsExtension", "t0"."ContractDefault", "t0"."ContractLength", "t0"."ContractRequired", "t0"."DaysAhead", "t0"."DaysPublished", "t0"."Deltas", "t0"."Discount", "t0"."DispPubNetCharge", "t0"."FaxCharge", "t0"."FaxNumber", "t0"."GrossOrNet", "t0"."HandJable", "t0"."InLinePubNetCharge", "t0"."InternetFeePub", "t0"."InternetFreePub", "t0"."IsAdstar", "t0"."IsVAT", "t0"."LastWriteDate", "t0"."LogoCharge", "t0"."ONLineHJ", "t0"."Phone", "t0"."PointSizes", "t0"."PubColWidth", "t0"."PubFontIndex", "t0"."PubName", "t0"."PubNumber", "t0"."PubsPayee", "t0"."PubType", "t0"."ReturnsAllLines", "t0"."Schlott", "t0"."SendToScienta", "t0"."SendViaPXAPI", "t0"."State", "t0"."SundayRate", "t0"."TotalYTDSpending", "t0"."ValidateLogo", "t0"."ValidPub", "t0"."WeekdayRate", "t0"."WhoPays", "t0"."ZipCode", "t0"."ZoneLength", "t0"."ZoneRequired", "t0"."Zones" FROM "pubfile" "t0" WHERE ("t0"."PubNumber" = :p0)(0019)

Its complaining on the where clause. Anything jump out at you?


(Alan Olson) #11

this is essentially

select * from pubfile where PubNumber=‘0019’

I’m using DA v9.5.0.1395 and Elements v10.0.0.2347

My code is this:

  var pq := (from prec in fDataModule.DataAdapter.GetTable<pubfile> 
              where prec.PubNumber='0019'
              select prec).ToList;

(Alan Olson) #12

Well, I upgraded to the latest release of both Elements and DA.NET, and now i’m seeing something different, but still a problem.

When I run my simple console app, I see this:

Unhandled Exception: RemObjects.SDK.Types.ServerException: An exception occurred on the server: Value cannot be null.
Parameter name: type
at RemObjects.SDK.Message.ProcessException()
at RemObjects.SDK.BinMessage.InternalReadFromStream(Stream stream)
at RemObjects.SDK.Message.ReadFromStream(Stream stream)
at RemObjects.SDK.IpHttpClientChannel.IntDispatch(Stream request, IMessage response)
at RemObjects.SDK.ClientChannel.Dispatch(IMessage message)
at RemObjects.SDK.DynamicRequest.InternalMakeRequest()
at RemObjects.SDK.DynamicRequest.MakeRequest()
at RemObjects.DataAbstract.Linq.LinqRemoteDataAdapter.FetchData(TableRequestInfo[] tableRequest, String[] tableNames, Action3 fillMethod) at RemObjects.DataAbstract.Linq.RemoteTable1.Execute(Expression expression, DataParameter[] parameters)
at RemObjects.DataAbstract.Linq.RemoteTable1.Execute(Expression expression) at RemObjects.DataAbstract.Linq.RemoteTableQuery1.GetEnumerator()
at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at rtest.Program.Main(String[] args) in D:\dev\miller\rtest\Program.pas:line 31

Line 31 is my DALINQ call I pasted above.


(antonk) #13

Please drop a mail to support@ so I’ll provide you a working version


(RemObjects) #14

Thanks, logged as bugs://81504


(RemObjects) #15

Thanks, logged as bugs://81505


(Alan Olson) #16

Anton -

Something is not right in terms of using PSQL.NET in the latest version of DA for .NET. I’m not sure if its a change in the daConfig file, or something else.

Did you notice the one SQL log I posted last week? That was when I was still using version 9.5.0.1395. I was able to log that sql with a third-party tool.

I’m assuming the issue has to do with the parameter in that example. The value I sent is in parentheses (0019)

Does the syntax in that SQL look correct to you?? Why wouldn’t you just replace the :p0 with the actual value i’m sending via DALINQ?

When I do the same with the latest official release, I get the ‘exception occurred on the server’ and i’m unable to log ANY SQL, even with the revised Relativity server you sent me. Something just isn’t right.


(antonk) #17

Because using string concatenation for composing queries is a SQL injection vulnerability.

Could you try to use this .daConfig file instead of the default one? DataAbstract.zip (7.6 KB)

Just unpack it and oput the .daConfig file next to the Relativity.exe and then restart it