is it possible to create query programmatically?

[t4nu]

Hi,
I put the search function in the sharedFunction so it can be used by several forms. Because of that, I thought it is necessary for the function to be able create the query on the fly, so it won't depend on the resources in the form.
Is it possible to do that?
Thanks in advance for the help.

[Segi]

It sounds like you want to filter the results of a query based on search parameters like a filter. If that's what you are trying to do, here is how I have done something like this.

In your database resource, create a field called WHERECLAUSE and uncheck persistent and check Dynamic SQL. Make sure required is not checked. Add the column to the SELECT where the where clause would go so it would read like

SELECT * FROM MYTABLE &&WHERECLAUSE.

Add the WHERECLAUSE to the table resource in your form. From there you can dynamically build the where clause using valid SQL. tables.mytable.WHERECLAUSE.value=" WHERE Name=&&NAME" or something like that depending on what you want to filter on

[t4nu]

Hi,
Thanks for the respond.
That's exactly what I have done all this time.
But because I put the search in the shared function, I found out that the resource is not available to be used, so I thought that it would be better if I can create a query directly.

It sounds like you want to filter the results of a query based on search parameters like a filter. If that's what you are trying to do, here is how I have done something like this.

In your database resource, create a field called WHERECLAUSE and uncheck persistent and check Dynamic SQL. Make sure required is not checked. Add the column to the SELECT where the where clause would go so it would read like

SELECT * FROM MYTABLE &&WHERECLAUSE.

Add the WHERECLAUSE to the table resource in your form. From there you can dynamically build the where clause using valid SQL. tables.mytable.WHERECLAUSE.value=" WHERE Name=&&NAME" or something like that depending on what you want to filter on

[Steve James]

Hi,

Is it that the function isn't available in the designer (ie intellisense) or runtime?

If it is intellisense then you can just change the 'Using form', if it is runtime then you need to make sure you link projects appropriately (ie properties of a project); then check the resources view to make sure the resource is available to the form (kind of a business view per form - or that's how I think of it)

http://dev-docs.verj.io/ufs/doc/Resources_View.htm

It is possible to run sql queries without the need for a database resource see http://forum.ebasetech.com/forum/viewto ... estatement

[t4nu]

Hi,
Thanks for the respond.

If I still have to use the form then what the point I make it as sharedFunction?

I have (at least) 2 applications, that need to search the same table, and because of that I thought it would be better if the function is placed in the shared, so it can be used by both application, that's the start of the idea to make a direct query come up.

But seems (from the responds) the direct query is something 'not possible'.

Hi,

Is it that the function isn't available in the designer (ie intellisense) or runtime?

If it is intellisense then you can just change the 'Using form', if it is runtime then you need to make sure you link projects appropriately (ie properties of a project); then check the resources view to make sure the resource is available to the form (kind of a business view per form - or that's how I think of it)

http://dev-docs.verj.io/ufs/doc/Resources_View.htm

It is possible to run sql queries without the need for a database resource see http://forum.ebasetech.com/forum/viewto ... estatement

[Steve James]

Maybe if you post your function it will become clearer what you mean by "direct query".

[Steve]

I am not sure if this is what you want to do, but...

You could create a JavaScript function to do the query:

e.g

Code: Select all

function doQuery(var name, var customersTable)
{
    var con = system.getDatabaseConnection("MYDB");
    var stmt;
    var rs;
    try
    {
         var query = "select * from customer where name = " + name;
         stmt = con.createStatement();
         var rs = stmt.execute(query);
         while(rs.next)
        {
             //populate table?
             customersTable.name.value = rs.getString("name");
             
        }
    }
    catch(e)
   {
   }
   finally
   { 
      if(rs) rs.close();
      if(stmt) stmt.close();
      if(con) con.close();
      
      
   }
}

Then add the script with your function in it to the Shared Function scripts in the Form --> Form properties --> Events section.

You reuse this function with other forms/scripts.

Steve

[t4nu]

YES, that's exactly what I mean with direct query.
Thanks a lot Steve, I will try to implement it.

I am not sure if this is what you want to do, but...

You could create a JavaScript function to do the query:

e.g

Code: Select all

function doQuery(var name, var customersTable)
{
    var con = system.getDatabaseConnection("MYDB");
    var stmt;
    var rs;
    try
    {
         var query = "select * from customer where name = " + name;
         stmt = con.createStatement();
         var rs = stmt.execute(query);
         while(rs.next)
        {
             //populate table?
             customersTable.name.value = rs.getString("name");
             
        }
    }
    catch(e)
   {
   }
   finally
   { 
      if(rs) rs.close();
      if(stmt) stmt.close();
      if(con) con.close();
      
      
   }
}

Then add the script with your function in it to the Shared Function scripts in the Form --> Form properties --> Events section.

You reuse this function with other forms/scripts.

Steve

[Steve]

Hi,

One thing to notice in the example I wrote is the finally method.

Code: Select all

....
finally 
   { 
      if(rs) rs.close(); 
      if(stmt) stmt.close(); 
      if(con) con.close(); 
   }

It is very important to close the result set, statement and the connection. Otherwise you could end up with database connection leaks.

Steve

[t4nu]

OK. Got it.
Thanks.

Hi,

One thing to notice in the example I wrote is the finally method.

Code: Select all

....
finally 
   { 
      if(rs) rs.close(); 
      if(stmt) stmt.close(); 
      if(con) con.close(); 
   }

It is very important to close the result set, statement and the connection. Otherwise you could end up with database connection leaks.

Steve