nuBuilder Forum

Posted: **Fri Feb 08, 2013 6:26 am**

Steven,

I am using the Activity Procedure to test my code as you suggested in another post.

The following code works and displays 2 date fields from each row in the the violations table.

Code: Select all

// This works

$resultSet = nuRunQuery("SELECT * FROM violations");

while ($violations_id = db_fetch_object($resultSet)) {

    echo "Date 1 - ".$violations_id->vio_date_entered_1.", "."Date 2 - ".$violations_id->vio_date_entered_2."\n";
				echo "<br>\n";
}

All I did was modify the nuRunQuery by adding 'CREATE TABLE #dataTable#' to give the following:

Code: Select all

// This doesn't work

$resultSet = nuRunQuery("CREATE TABLE #dataTable# SELECT * from violations ";);

while ($violations_id = db_fetch_object($resultSet)) {

    echo "Date 1 - ".$violations_id->vio_date_entered_1.", "."Date 2 - ".$violations_id->vio_date_entered_2."\n";
				echo "<br>\n";
}

In the second example, I get nothing when I run the activity procedure.

What am I doing wrong?

John

Posted: **Fri Feb 08, 2013 11:10 pm**

John,

maybe, a syntax error in sql inside nuRunQuery:

Code: Select all

... * from violations");

A simple way to debug sql sentence is:

Code: Select all

$sql = "CREATE ... SELECT ... <your query>";
nuDebug($sql);
nuRunQuery($sql);

In this way, in Setup > Debug (or in database zzsys_trap table) you can see the SQL sent to the database.

Hope this helps.

Posted: **Sat Feb 09, 2013 12:48 am**

massiws,

$resultSet = nuRunQuery("CREATE TABLE #dataTable# SELECT * from violations ";);

while ($violations_id = db_fetch_object($resultSet)) {

echo "Date 1 - ".$violations_id->vio_date_entered_1.", "."Date 2 - ".$violations_id->vio_date_entered_2."\n";
echo "<br>\n";
}

$resultSet doesn't hold a record set.

Code: Select all



nuRunQuery("CREATE TABLE #dataTable# SELECT * from violations ";);

$resultSet = nuRunQuery("SELECT * from #dataTable#";);  //-- this will work

while ($violations_id = db_fetch_object($resultSet)) {

    echo "Date 1 - ".$violations_id->vio_date_entered_1.", "."Date 2 - ".$violations_id->vio_date_entered_2."\n";
            echo "<br>\n";
}

BTW there is never any need to use #dataTable# in a procedure as the result has no report or export to go to.

Steven

Posted: **Sat Feb 09, 2013 7:08 am**

Max and Steven,

Thanks for responding so quickly. Unfortunately, I still can't get it to work properly.

If I copy Steven's code into the procedure and try to run it, I get a blank screen.

If I remove the first line, and change '#dataTable#' to 'violations' I still get a blank screen.

If I remove the first line, change '#dataTable#' to 'violations' and remove the ';' before the second parentheses, then it works.

So this is what it looks like now:

Code: Select all

$resultSet = nuRunQuery("SELECT * from violations ");  //-- this will work

while ($violations_id = db_fetch_object($resultSet)) {

    echo "Date 1 - ".$violations_id->vio_date_entered_1.", "."Date 2 - ".$violations_id->vio_date_entered_2."\n";
            echo "<br>\n";
}

I also tried the following code from Max:

Code: Select all

$sql = "CREATE TABLE #dataTable# SELECT * from violations ";
nuDebug($sql);
nuRunQuery($sql);

$resultSet = nuRunQuery("SELECT * from #dataTable# ");  //-- this will work

while ($violations_id = db_fetch_object($resultSet)) {

    echo "Date 1 - ".$violations_id->vio_date_entered_1.", "."Date 2 - ".$violations_id->vio_date_entered_2."\n";
            echo "<br>\n";
}

and received this error in zzsys_trap:

(nuBuilder Procedure Code) of Test code Using Activity Procedure : Error Reference: 6774d72
An error occurred while running the following query:
CREATE TABLE #dataTable# SELECT * from violations

I thought maybe I was spelling 'violations' wrong but it works fine in the '$resultSet' line.

Finally, I appreciate the comment by Steven,

BTW there is never any need to use #dataTable# in a procedure as the result has no report or export to go to.

What I am trying to do is test my logic for a report. In the report I want to use #dataTable# to get data from one or more tables, add some temporary fields, make some calculations and then update those temporary fields before creating the report.

I appreciate any suggestions you may have.

John

Posted: **Sat Feb 09, 2013 7:36 am**

I just had a thought. Is it possible that you are not allowed to create a table in a procedure?

John

Posted: **Sat Feb 09, 2013 4:02 pm**

John,
if you want to run a report you have to insert the code in Report tab: here the hash #dataTable# is correctly transformed in a temporary name by nuBuilder; the same thing don't happen on code in Procedure tab.

If you want to test your logic in Procedure tab, I suggest to create a table with a fixed name:

Code: Select all

$sql = "CREATE TABLE test SELECT * FROM violations";
nuDebug($sql);
nuRunQuery($sql);

so in phpMyAdmin you can explore the test table content.

Steven, if I run this code in Procedure tab, I get an error from nuBuilder:

Code: Select all

$rs = nuRunQuery("select * from zzsys_user";);   // this don't work

No error if I delete the semicolon before the close parenthesis:

Code: Select all

$rs = nuRunQuery("select * from zzsys_user");   // this will work

Hope this helps,
Max

Posted: **Sat Feb 09, 2013 6:08 pm**

Max and Steven,

Thanks again for your help. I was able to get both of your examples to work once I replaced #dataTable# with a test table. I also had to remove the extra ';' before the right parantheses in Steven's example.

Now that I know that I need to use a test table in Procedure and then replace it with #datatable# in the Report, I can test my code using 'echo'. I am still not toally understanding how to use nuDebug() but I will continue to play with it. I was expecting it to give me the values of a field or array instead of just telling me that it executed the command. Since the test code and echo work, getting nuDebug() to work is a lower priority.

Thanks,

John

Posted: **Sat Feb 09, 2013 7:44 pm**

John,

JohnKlassen wrote:I am still not toally understanding how to use nuDebug() but I will continue to play with it. I was expecting it to give me the values of a field or array instead of just telling me that it executed the command.

have you seen this video? http://www.youtube.com/watch?v=nZU7eJGlVDw

nuDebug(pString) function writes on database the value of pString: so if you want to know the values of your variables you have to insert a nuDebug(my_variable) in your code.

Hope this helps,
Max.

Posted: **Sat Feb 09, 2013 10:39 pm**

John,

This DOES now work.

Code: Select all

$resultSet = nuRunQuery("SELECT * from violations");  //-- this will work

while ($violations_id = db_fetch_object($resultSet)) {

    echo "Date 1 - ".$violations_id->vio_date_entered_1.", "."Date 2 - ".$violations_id->vio_date_entered_2."\n";
            echo "<br>\n";
}

2 things..

1. I was wrong using #dataTable# in my previous example. As I said earlier a procedure doesn't need it, so I forgot that a procedure actually doesn't allow it.

2. The other thing is

Code: Select all

SELECT * from violations ";);

remove the first semicolon.

John when get a problem like this you need to learn to debug it properly.

This is how you do it..

1.Comment out everything and put a nuDebug, or if its a procedure, an echo as the first line and comment out everything else.
This will give you some result.

2. Bit by bit reintroduce part of the code until the echo no longer shows up. That way finding the error by a process of elimination.

This will save you hours staring at a bunch of code trying to find a minor syntax error.

Steven

Posted: **Sun Feb 10, 2013 10:32 pm**

Steven,

Thanks for the advice. I am making good progress now and making sure I only change one thing at a time and then checking the results. I still have a new problem unrelated to this so I will open a new post in the general category.

John

nuBuilder Forum

Why doesn't this procedure work?

Why doesn't this procedure work?

Re: Why doesn't this procedure work?

Re: Why doesn't this procedure work?

Re: Why doesn't this procedure work?

Re: Why doesn't this procedure work?

Re: Why doesn't this procedure work?

Re: Why doesn't this procedure work?

Re: Why doesn't this procedure work?

Re: Why doesn't this procedure work?

Re: Why doesn't this procedure work?