Changing Select field in sql query inside of PHP method not working properly

Scriptcase 8 Bugs
#1

I have seen a bad bug in SC for quite sometime, however I was finally able to pinpoint exactly what is happening instead of just a ‘symptom’ of the entire app breaking or doing something unintended.

One example of where this happens:
I have a custom PHP method coded, in which we are running an SQL query. We recently had to change the name of the field in the table this query was accessing (the application was working perfectly for many months before this change).
I edited the query in the PHP method, however you can clearly see in the source code generated, that the OLD QUERY is still present somewhere it shouldn’t be, and whenever I try to run the application, it pops up an error code in the ScriptCase development environment.

Here is the updated PHP Method:

sc_include(core.library.php);

$sql1 = "SELECT MgmtIP FROM ap320 WHERE PMP320BSSID = '{PMP320ConnectedBSSID}'" 
. " ORDER BY MgmtIP";

$sql2 = "SELECT Nickname FROM ap320 WHERE PMP320BSSID = '{PMP320ConnectedBSSID}'" 
. " ORDER BY Nickname";

sc_lookup(rs1, $sql1);
sc_lookup(rs2, $sql2);

$GSTR2 = {rs1[0][0]};
$GSTR3 = {rs2[0][0]};
$GSTR1="http://$GSTR2";
{APNickname}=$GSTR3;
$link="<a href='$GSTR1' target='_blank'>{APNickname}</a>";

{APLink}=$link;
{APNickname}=$GSTR3;

Once I click on RUN, the following pop up window with an error appears in SC:

SQL ERROR's:

Field:pmp320connectedbssid SELECT PMPIP FROM ap320` WHERE PMP320BSSID = '{PMP320ConnectedBSSID}' ORDER BY PMPIP
()

(which was the OLD query) … the field PMPIP was renamed to MgmtIP in the MySQL database and in the PHP method.

This is a show stopper because I can’t deploy this updated application to our PE until this is fixed for fear that it would display this broken query to an end user (actually I have about half a dozen apps that are affected by this same bug). I can grep the generated source code and see that the old query is STILL in the index.php and one of the class.php files, despite the fact I have updated the php method, and there is no trace of the old field name in the application anymore.

Modifying queries in SC seems to be a big issue … i.e I can edit a query, and if I accidentally get the syntax wrong, it breaks/trashes/destroys the entire app to the point it won’t even load anymore, and correcting the syntax error doesn’t FIX it … and I either have to restore from backup or start over from scratch …

This should not be difficult at all the duplicate …

  • Custom PHP method that executes an mySql query.
  • Rename a field you are using in the select statement in the DB, then edit your php method to reflect that change
  • Watch the pop error appear ...
#2

Wow, no responses from anyone at Scriptcase or Netmake regarding this?? Seriously??

As an addendum to my report, I have found this seems to effect the Export function of the grid, as when I go to Export my grid to XLS, it gives me an error

Error
Error while accessing the database:
Unknown table 'ap320' in information_schema
select PMPIP from `ap320` where PMP320BSSID = '00:00:00:00:34:01' order by PMPIP

When you edit the SQL query in the application, the changes are clearly NOT being propagated to other areas of the application (such as the Export function).

With no way to EDIT this, this application is broken until you guys release a FIX for this.

#3

Did you tried to empty application folder inside apps folder and regenerate this app to see if regenerates ok?

#4

Giu, I’m wondering if this could not be a general solution to where re-generating an app seems to fail to fix a problem. Do you mean here to simply go to "sc_install_folder/app/projectname and then delete the folder for your app? Or is this some other folder?

#5

Yes, this is what I mean, and no, not a general solution. Just a test to be sure app is generated and is not a permission problem or something like that.

I had problem in the past with executing old querys, but was related to parser going crazy and generating a commented line.

#6

Hi CurtisB,

I’ve tried to perform the same error of you, but I couldn’t. See below the steps of my test:

1 - I’ve created a table, called ‘curtisb’, with three fields(id, firstname and lastname);
2 - I have created a grid on Scriptcase;
3 - Then, I’ve made a PHP method called ‘test’ with the following code:


$sql = "SELECT firstname, lastname FROM curtisb WHERE id = " . {id};

sc_lookup(ds, $sql);

$f = {ds[0][0]};
$l = {ds[0][1]};

$link = "<a href='#$f-$l'>Click here to $f $l</a>";
{xLink} = $link;

4 - I used the method on ‘onRecord’ event;
5 - The application worked fine;
6 - So, I’ve changed the name of field ‘firstname’ to ‘firstnamex’, in the DB;
7 - Then, I have changed the grid SQL and method lookup for the new field’s name;
8 - The application worked fine again, generating the method with the updated field’s name;

Please, if I did something different let me know. What’s your Scriptcase release? Is it a project which was converted from an older Scriptcase version?

I’ll wait for your response, bye.