sc_lookup / sc_select gives no results - but same query directly in database works?

Scriptcase 9 General
#1

Hi

I have this code snippet that is supposed to return the file names of uploaded timesheets for a specific user:


$my_sql = "select signedtimesheet from tbltimesheets where user = '{login}'";
sc_lookup(my_data, $my_sql);

if ({my_data} === false) {
	echo "<script type='text/javascript'>alert('Access error. Message=" . {my_data_erro} . " - " .
	     "Delete cancelled.');</script>";
	
} elseif (!isset({my_data[0][0]})) {
	echo "<script type='text/javascript'>alert('No Files!');</script>";
	
}

When run, the “elseif” fires saying “No files”. But this should not be happening! When I run the same SELECT directly against the database (whether via phpMyadmin, or SC’s own database builder tool) I get the results I expect to get. For example, using the following (I have substituted {login} with a real user name. ‘adam’):

select signedtimesheet from tbltimesheets where user = 'adam'

I get (see image below):

So - the data is definitely in the database, and the query to extract it is correct - yet when using sc_lookup (or sc_select - I have tried both) I get told there is no data. For some reason the my_data variable is not being initialized?

Anyone have any ideas please?

Thanks

Adz

Capture.JPG

#2

First go to application section on your left menu and set all the debug options on. It will show you all the sql statements which are fired before execution. I expect that {login} will be empty or contains an invalid value. In what event are you currently using this??

#3

Try …


sc_lookup(my_data, $my_sql);
var_dump(my_data);

… and check the output.

#4

@Albert - I already had those debug options on, and they verify the SQL is good and that {login} is populated correctly; see the last (mysqlt) line in image below.

The event that code is in is “OnAfterDelete” in the security module’s “app_form_edit_users” form application. Its purpose is that when a user is deleted, all their uploaded files get deleted too. On fail a rollback will be initiated…

@Reinhard - In same image the output of var_dump is also shown:


array(0) {
}

Which again seems to indicate that although the SQL is good, the resultant dataset is not assigned…??

Capture2.JPG

#5

Just a thought …Do have a foreign key/reference with ON DELETE CASCADE in your table?

jsb

#6

Hi

Thanks for the reply…

On “tbltimesheets” the following foreign keys are defined…

However, I don’t see the relevance of your question? The field I want returned is from a SELECT query that works perfectly when entered against the database directly (either say in phpMyadmin or SC’s own database builder tool), but when run in scriptcase application (there are no SC dependencies defined btw) it seems to return nothing. Plus - the “delete” I am doing is not an SQL DELETE, but deleting files that are referenced from in the database.

That all said, you reply has just given me a thought! As my code is being run “afterdelete”, I guess it makes potential sense that the records I am expecting to be returned have already just been “deleted” (although not committed at that point)?

I will do a simple experiment and hard code a real user name in the code, but which is different to the user name I am “deleting”. That should return some data them if my theory is correct as those records will not have been deleted. If that proves to be true, then I guess I’ll have to collect that information in the “beforedelete” event and do the “afterdelete” clean up afterwards…

I’ll post my findings…

Capture3.JPG

#7

Yep - I can confirm what the problem was.

Me!

My theory was right - I am trying to SELECT records in an “afterdelete” event - which means, at that point, the records I am trying to read have just been deleted! D’oh!

So, I just need to move that logic to “beforedelete”, save the retrieved information as a global array - and then in “afterdelete” read that array. After doing the other stuff I can then reset the array.

Thanks all for replying :slight_smile: