Solved

How to convert fBQL or BQL to SQL statement while debugging in Visual Studio

  • 8 September 2022
  • 8 replies
  • 448 views
K
Rank

Hello,

While working with fBQL or BQL I have often wanted to see what exactly the equivalent SQL statement be. Is there an inbuilt function something like like .ToSQLString() that we can use on a fBQL statement or View that can show us the equivalent SQL.

 

So for example, if I have the following View

SelectFrom<CSAnswers>
.InnerJoin<InventoryItem>
	.On<InventoryItem.noteID.IsEqual<CSAnswers.refNoteID>>
.Where<CSAnswers.attributeID.IsEqual<Use<slots>.AsString>
	.And<InventoryItem.inventoryID.IsEqual<@P.AsInt>>>
.View.Select(cache.Graph, invtID)

 

How can I see the exact SQL that’s generated (including the parameter values in the Where clause)?

One option is I can run SQL profiler, but is there anything inbuilt Acumatica SDK/Framework that can help me view the SQL statement while debugging in VS?

icon

Best answer by MoulaliShaik79 8 September 2022, 09:01

View original

8 replies

M
Rank
Userlevel 4
Badge +1

Hi @kashif78,

Please check the below link:

https://help-2021r2.acumatica.com/(W(15))/Help?ScreenId=ShowWiki&pageid=5bc68600-61ab-4a49-958c-da5d732b2ac2

 

I hope this may help you.

 

Thanks,

Moulali Shaik.

Moulali Shaik.
K
Rank

Thank you @MoulaliShaik79, that documentation helped me come up with the required code.

So here’s the updated code that I have used and can see the strSQL while debugging

 

var strSQL = 
	SelectFrom<CSAnswers>
	.InnerJoin<InventoryItem>
		.On<InventoryItem.noteID.IsEqual<CSAnswers.refNoteID>>
	.Where<CSAnswers.attributeID.IsEqual<Use<slots>.AsString>
		.And<InventoryItem.inventoryID.IsEqual<@P.AsInt>>>
	.View.GetCommand().GetQuery(Base).SQLQuery(Base.SqlDialect.GetConnection());

 

K
Rank

While I was debugging using the above technique/code, I noticed that the query doesn’t includes the filter for CompanyID in Where, Join and Group By clauses. Any ideas how I can make Acumatica give me the SQL query after it adds the CompanyID to the SQL Query Tree?

M
Rank
Userlevel 4
Badge +1

Hi @kashif78,

The Company ID column is used for Multi-Tenet support purposes. We can not find it in our DAC Fields.

Please check the below link to get some information about the Company ID column.

https://help-2021r2.acumatica.com/(W(16))/Help?ScreenId=ShowWiki&pageid=d0945e20-1949-40b1-bd0f-92c7c432aa24

 

Note: Till now I didn’t use the Company ID column in FBQL or BQL queries.

 

Thanks,

Moulali Shaik.

Moulali Shaik.
K
Rank

Yes, I’m aware that we don’t add a CmpanyID field in our DAC. But Acumatica does add the filters for CmpanyID when running a query against the SQL DB. I wanted to see what all places in the query it adds the CompanyID filter

 

PS: My dev environment is a multi-tenant one having around 5 companies. 

Leonardo Justiniano
Rank
Userlevel 6
Badge +4

Hi All

Recent versions of Acumatica add an option under tools to trace SQL statements:

 

 

When you stop the logging a zip file is generated for downloading

 

You can then see all the add-ons Acumatica includes in your simple (F)BQL query.

Also you can use MS SQL Profiler if you want to see it coming as it happens (Enjoy the cache as not all the time those queries are triggered)

Leo
R
Rank
Badge

Another option that you have is you can wire up a SQL profiler as to see the end result of what gets sent to SQL server.

K
Rank

Hi All

Recent versions of Acumatica add an option under tools to trace SQL statements:

 

 

When you stop the logging a zip file is generated for downloading

 

You can then see all the add-ons Acumatica includes in your simple (F)BQL query.

Also you can use MS SQL Profiler if you want to see it coming as it happens (Enjoy the cache as not all the time those queries are triggered)

 

Thanks for the tip @Leonardo Justiniano 

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved