Insert

The problem is there aren't really any errors. An example of the data block sql file would be:

--ACCESS=access content
--This table has only three columns, and was created specifically to accept this input
INSERT INTO
mytable
VALUES
(:val1, --a parameter value provided by a forena data source (a list of values sql file)
 :val2, --a user input double value
 :val3) --a user input date value

I click "add data" in the "data" tab. Select my data block, leaving the parameters blank and click "Preview". I then get "The website encountered an unexpected error. Please try again later."

If instead I try to add the parameters using "val1=parm1&val2=100&eff_date=2013-10-21", I get the same error as above.

Edit:
I apologize for overlooking this before, I did find two error in my recent log messages.

1. PDOException: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: 'Array' for column 'val1' at row 1: insert into mytable values ('Array', 'Array', 'Array') ; Array ( ) in FrxDrupal->sqlData() (line 44 of /srv/www/htdocs/sites/all/modules/forena/plugins/FrxDrupal.inc).

2. Notice: Undefined index: password in FrxPDO->__construct() (line 49 of /srv/www/htdocs/sites/all/modules/forena/plugins/FrxPDO.inc).</li>

Before, my columns were

val1 --int(11)
val2 --double
val3 --date

For the sake of getting past the error I changed them all to varchar(45) and then encountered this error

PDOException: SQLSTATE[HY000]: General error in FrxDrupal::sqlData() (line 47 of /srv/www/htdocs/sites/all/modules/forena/plugins/FrxDrupal.inc).

and I still received the password error from above, also.

Thanks for the help,
Zak

I did attempt this

--IF=:val1
INSERT INTO
mytable
VALUES
(:val1, --a parameter value provided by a forena data source (a list of values sql file)
:val2, --a user input double value
:val3) --a user input date value
--ELSE
select 'Missing Required Parameters' as X
--END

to no avail. I received the same general error in recent log messages. What is interesting though, is that Array|Array|Array is being inserted into the database, even though I continue to receive the error: "The website encountered an unexpected error. Please try again later."

I would be happy to update, if you could give me a little guidance. This site is in production, but this module is not yet being used, as I must first complete all the reports. However, I do currently have a few reports working and running correctly. All of my sql files are located in sites/default/files/custom*. Will these all still work correctly if I was to delete "forena" from sites/all/modules and replace it with the new "forena"?

Thanks again,
Zak

Ok, I finally got the chance to upgrade. I attempted to recreate the report using:

--ACCESS=access content
--IF=:val1
INSERT INTO
mytable
VALUES
(:val1, --a parameter value provided by a forena data source (a list of values sql file)
:val2, --a user input double value
:val3) --a user input date value
--ELSE
select 'Missing Required Parameters' as X
--END

and I recieved the "The website encountered an unexpected error. Please try again later." error. In the log messages I found roughly the same error as before:

PDOException: SQLSTATE[HY000]: General error in FrxDrupal::sqlData() (line 54 of /srv/www/htdocs/sites/all/modules/forena/plugins/FrxDrupal.inc).

Glancing at the function, it looks like it is expecting the variable $rs to be an array (returned from a select statement I'm assuming). But since this is an insert I'm guessing nothing is returned. Any way to modify the function to not expect a return value if an "insert" statement is found?

<?php
 public function sqlData($sql, $options = array()) {
    if ($this->database != 'default') {
      db_set_active($this->database);
    }
    // Load the types array based on data
    $this->types = isset($options['type']) ? $options['type'] : array();

    // Load the block from the file
    $xml ='';
    $sql = $this->te->replace($sql);
    $rs = db_query($sql);
    $entity_map = array();
    $select_fields = array();
    $rownum = 0;



    $xml = new SimpleXMLElement('<table/>');
    foreach ($rs as $data) {                 //this is line 54 that the error is referring to
      $rownum++;
      $row_node = $xml->addChild('row');
        $row_node['num'] = $rownum;
      /* If we are querying entities, we will store away IDs
       * for later querying and XML processing in the
       * loadEntitys method
       */
      if (@$options['entity_type']  && @$options['entity_id'] ) {
        $id_key = $options['entity_id'];
        $type = $options['entity_type'];
        $id = $data->$id_key;
        if ($id) {
          $entity_map[$id] = $row_node;
          $select_fields[$id] = $data;
        }

      } else {
        foreach ($data as $key => $value) {
          $row_node->addChild($key, htmlspecialchars($value));
          //$row_node->$key = $value;
        }
      }

    }


    if ($this->database != 'default') {
      db_set_active();
    }
    if ($entity_map) {
      $this->loadEntities($type, $entity_map, $select_fields);
    }
    if ($this->debug) {
      $d = ($xml) ? htmlspecialchars($xml->asXML()) : '';
      $this->debug('SQL: ' . $sql, '<pre> SQL:' . $sql . "\n XML: " . $d . "\n</pre>");
    }
    return $xml;
  }
?>

Thanks for the patch, but no luck. The patch was successful, and I double checked the files to ensure if worked correctly. I again received the "The website encountered an unexpected error. Please try again later." error after attempting to add the insert datablock. In the log messages there were two additional errors:

1. PDOException: SQLSTATE[HY000]: General error in FrxDrupal::sqlData() (line 54 of /srv/www/htdocs/sites/all/modules/forena/plugins/FrxDrupal.inc).
2. Notice: Undefined index: password in FrxPDO->__construct() (line 49 of /srv/www/htdocs/sites/all/modules/forena/plugins/FrxPDO.inc).

Well, it seemed like it kind of worked but at the same time it didn't. Leaving the table columns as varchar I added the datablock and it seemed at first to accept it (as in, it didn't give the "unexpected error" message); however, it asked me to insert the parameters and upon doing so and clicking "Preview" nothing happened on the front end (it was inserted to the db on the backend), the page simply reloaded and the datablock was never truly added. And, these errors were in the logs

1. PDOException: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: 'Array' for column 'val1' at row 1: insert into mytable ('Array', 'Array', 'Array') ; Array ( ) in FrxDrupal->sqlData() (line 46 of /srv/www/htdocs/sites/all/modules/forena/plugins/FrxDrupal.inc).

2. Notice: Undefined index: password in FrxPDO->__construct() (line 49 of /srv/www/htdocs/sites/all/modules/forena/plugins/FrxPDO.inc).

For testing, I then changed the columns back to the appropriate int, double, date and tried again. First I updated my .sql file to this:

--IF=:val1
INSERT INTO
mytable
VALUES
(:val1, --a parameter value provided by a forena data source (a list of values sql file)
:val2, --a user input double value
:val3) --a user input date value
--ELSE
select 'Missing Required Parameters' as X
--END
--INFO
type[val1]=int
type[val2]=double
type[val3]=date

After which I received the "The website encountered an unexpected error. Please try again later." message as before and found these errors in the logs:

1. Exception: DateTime::__construct(): Failed to parse time string (Array) at position 0 (A): The timezone could not be found in the database in DateTime->__construct() (line 519 of /srv/www/htdocs/sites/all/modules/forena/FrxDataSource.inc).

2. Notice: Undefined index: password in FrxPDO->__construct() (line 49 of /srv/www/htdocs/sites/all/modules/forena/plugins/FrxPDO.inc).

Ok, I altered the table columns all back to varchar(45) and loaded the .sql file in the Forena Reports -> Data area by selecting my file and clicking "Load".

The page then showed my data security and source and asked for parameters. I inserted parameters and clicked "Preview" and the page refreshed with the parameters still showing (appearing as though nothing happend). I checked the db and the parameters had been inserted. I again typed in parameters then clicked "Reload" and the page refreshed with the parameters still showing, and again they had been inserted into the db. So, why then doesn't it allow me to add the datablock on a report page?

Also, is there a way to make this work with specific datatypes? And, is it possible to show a message for insertion success, as well as remove the parameters?

Thanks again for all the quick and precise help!

I see what you are saying. I will have to give a try to adding the datablock manually and see where it gets me. I had hoped to use forena for inserting the data simply for uniformity for the users in how the pages appear and where they are located. I'm sure with some trial and error I could craft a form insertion node myself, but I had hoped with forena it might not only be possible, but much quicker. The url crafting to data insertion would definitely be a concern. Also, sorry for the ambiguity, but when I said remove the parameters, I actually meant "clear" the text fields of the data that had just been inserted and display success, else leave them and display failure.

Thanks again for all the help. I'm off for a long weekend, so I won't get a chance to look back at this until Monday. Hopefully I have enough to get it going then.

Thanks,
Zak

Something interesting happened this morning. I began writing a tutorial for you to follow in order to reproduce the "array" being inserted into the db and ended up creating the report and having it insert the data correctly first try. I had assumed that the reason array was being inserted was because one of the parameters was from a "select" "input control type" (a list of values coming from another db table). And to answer your question, no, I never did write any custom code with hook_forena_parameters_alter.

I would be happy to attempt to write some form code haha. I will begin looking in to how to do so, and await your reply.

Also, some additional information on the somewhat successful data insertion I had this morning:

1. I was able to add my datablock both through Edit>Data>Add Data and Layout><div frx:block="my_repos/my_data_block" />
2. The issue still exists that after filling in the parameters (which would more accurately be described as the data to be inserted) the fields still hold the selected values, giving no clear signal to the user that the data was indeed inserted (I know your aware of this, just reminding you of all the issues that I will need to address)
3. Like you said a correctly formatted url will insert the data (which is definitely a serious problem)
4. Along with the url problem comes that fact that refreshing on the same url will insert identical data a second time, or more
5. Lastly, when attempting to use the form to insert back to back information, clicking "Submit" will insert the data from the first insertion that still exists in the url, as well as the new values that have been entered. (ie. you get a duplicate of the first, as well as the second)

I look forward to getting this working correctly, and thank you again for all you help. And please, if I am attempting to do something that Forena truly was not built for doing, you can certainly point me in a more appropriate direction to solve my problem if you are aware of one. Otherwise, if you would like to make it a feature that Forena can be capable of, I am happy to continue working in this direction, because I personally think it would be a great feature. So just let me know, I don't want to waste your time!