1. Brian O'Malley
  2. SnapDevelop
  3. Monday, 11 July 2022 16:55 PM UTC

We are migrating an existing PowerBuilder application to make use of SnapDevelop to handle the datawindow calls.  Our system has a reporting area, where we give the user a selection of criteria they can use to customize the report.  As the current system works, we have something like this where we are appending to the where clause:

is_sqlSyntax = dw_report.GetSQLSelect()
is_sqlSyntax += " and def_lname Like 'J%'"
ll_ret = dw_report.SetSQLSelect(is_sqlSyntax)
dw_report.SetTransObject(SQLCA)
ll_rows = dw_report.retrieve ( )

In the new system, we make the following call to issue the retrieve of the datawindow:

dw_report.dataobject = 'd_caseload_all_main'

ls_url = gs_host +"/api/"+dw_browse.dataobject+"/retrieve/"
ll_ret = gnv_RestClient.Retrieve(dw_browse, ls_URL)

Since the datawindow object is now in SnapDevelop, calling a SetSQLSelect() from the PowerBuilder code before calling the retrieve does nothing.  I would like to know how I can pass to the datawindow object in SnapDevelop the text that needs to be appended to the SQL and then make it happen in the SnapDevelop code.

When I tried to just do a SetSQLSelect in SnapDevelop (without worrying about how I would get the value passed in) it did not work.  I added the 3 lines of code between the '----' lines to the Service but I get no data returned to the PB app.

public class D_Caseload_MainService : ID_Caseload_MainService
{
   private readonly DataContext _dataContext;

   public D_Caseload_MainService(SqlDataContext dataContext)
   {
      _dataContext = dataContext;
   }

   public async Task<IDataStore<D_Caseload_Main>> RetrieveAsync(CancellationToken cancellationToken)
   {
      var dataStore = new DataStore<D_Caseload_Main>(_dataContext);

----------------------------------------------------------------------------------
      string sqlSelect = dataStore.GetSqlSelect();
      sqlSelect += " and def_lname Like 'J%'";
      dataStore.SetSqlSelect(sqlSelect);
----------------------------------------------------------------------------------
            
      await dataStore.RetrieveAsync(new object[] { }, cancellationToken);
 
      return dataStore;
   }
}

Could someone help me figure out how to make this work?  I need to know 1) how to send the SQL to be appended on the PowerBuilder side  and 2) where to put the SetSqlSelect call so that the SQL is updated before retrieving data.

Thanks!

Brian O'Malley

 

 

Olan Knight Accepted Answer Pending Moderation
  1. Tuesday, 12 July 2022 22:18 PM UTC
  2. SnapDevelop
  3. # 1

If you want to append something to the SQL being sent to the database, orto manipulate that SQL in any way, I would do so in the SQLPREVIEW event of the datawindow.

Comment
There are no comments made yet.
Daryl Foster Accepted Answer Pending Moderation
  1. Tuesday, 12 July 2022 01:13 AM UTC
  2. SnapDevelop
  3. # 2

Hi Brian,

To get the string to your retrieve function you'll need to add some arguments to your controller method. Here is a basic example.  I'm not sure if you need more than one argument sent to your retrieve function so in my example I've included 2 arguments, you can extend it if you need more arguments or remove the second one if you don't need it.  The arguments are passed as query parameters to the controller and then passed onto the retrieve function of the service which will then dynamically modify the sql before retrieving.  I don't know what your def_lname column is, I'm assuming last name, so I've added an additional argument called firstName to demonstrate the technique. You will just need to update it to the appropriate column names.  To call the api you would append the arguments, so these calls would all be valid:

 

gshost + "/api/d_caseload_main/retrieve"

gshost + "/api/d_caseload_main/retrieve?lastName=J%"

gshost + "/api/d_caseload_main/retrieve?lastName=J%&firstName=A%"

 

The examples below aren't real code from a project so they may not compile, but hopefully they give you a good enough idea of the technique.

In the Controller Class you would need to accept the arguments from the query parameters. An example would be:

// GET api/d_caseload_main/retrieve?lastName=J%&firstName=A%
[HttpGet()]
[ProducesResponseType(typeof(D_Caseload_Main), StatusCodes.Status200OK)]
[ProducesResponseType(StatusCodes.Status404NotFound)]
[ProducesResponseType(StatusCodes.Status500InternalServerError)]
public ActionResult<IDataStore> Retrieve([FromQuery] string lastName, [FromQuery] string firstName)
{
	try
	{
		var caseLoad = _caseLoadService.RetrieveDynamicAsync(lastName, firstName, CancellationToken.None);

		if (caseLoad.RowCount == 0)
		{
			return NotFound();
		}
		else 
		{
			return Ok(caseLoad);
		}
	}
	catch (Exception ex)
	{
		return StatusCode(StatusCodes.Status500InternalServerError, ex.Message);
	}
}

 

Then in your service class you would need to implement the RetrieveDynamicAsync method, for example:

public async Task<IDataStore<D_Caseload_Main>> RetrieveDynamicAsync(string lastName, string firstName, CancellationToken cancellationToken)
{
	var dataStore = new DataStore<D_Caseload_Main>(_dataContext);

	string whereSql = "";

	// Note, the input arguments should probably be sanitised, or sanity checked to prevent sql injection type issues

	if (!String.IsNullOrEmpty(lastName)) 
	{
		whereSql = whereSql + String.Format(" and def_lname like '{0}' ", lastName);
	}

	if (!String.IsNullOrEmpty(firstName)) 
	{ 
		whereSql = whereSql + String.Format(" and def_fname like '{0}' ", firstName);
	}

	if (!String.IsNullOrEmpty(whereSql)) 
	{ 
		string sql = dataStore.GetSqlSelect();

 		// this is just appending your dynamic where to the existing sql statement
		sql += whereSql

		// or alternatively you could have a placeholder in your sql that you 
		// replace instead of just appending
		//sql = sql.Replace("--{DYNAMIC-WHERE}--", whereSql);
 
		// success should be checked and an exception raised if false
		bool success = dataStore.SetSqlSelect(sql);
	}

	await dataStore.RetrieveAsync(new object[] { }, cancellationToken);

	return dataStore;
}

 

 

Comment
  1. Brian O'Malley
  2. Tuesday, 12 July 2022 19:39 PM UTC
That worked. Thanks for the help!
  1. Helpful
  1. Logan Liu @Appeon
  2. Wednesday, 13 July 2022 02:18 AM UTC
Hi Brian, just an extra reminder, if you're going to expose this API to the public instead of only using it internally, be aware of the risk of SQL injection.



To prevent parameter data passed into the API from being treated as a part of SQL instructions and executed directly, a safer way is to add more Retrieve arguments for this DataWindow (NET DataStore), thus it can use variable binding to reduce the risk.



Regards, Logan

  1. Helpful 1
There are no comments made yet.
Brian O'Malley Accepted Answer Pending Moderation
  1. Monday, 11 July 2022 19:12 PM UTC
  2. SnapDevelop
  3. # 3

Update...

I realized why the code that was inserted into the Service did not work.  I was using a datawindow object that had retrieval arguments already, and that doesn't work with SetSQLSelect in regular PowerBuilder either.  I tested on a different datawindow which did not have any retrieval arguments and it seems like it works when injecting a hard-coded string to the end of my SQL. 

Now all I need to figure out is how to get the string value sent to the retrieve function in the first place, so it can be appended.  

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.