Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
Is it possible to use Forena to insert data into a db? When I try and use a .sql file that already has an insert statement written into it, Forena won't accept it. I get the error "The website encountered an unexpected error. Please try again later." If I temporarily use a .sql file that has a select, in order to get Forena to accept it, then rewrite the select as an insert, Forena will insert the data on submit, but then I get the same error I mentioned before.
Thanks,
Zak
Comment | File | Size | Author |
---|---|---|---|
#9 | forena_insert_2115351.patch | 1.13 KB | metzlerd |
#7 | forena_insert_2115351.patch | 1.03 KB | metzlerd |
Comments
Comment #1
metzlerd CreditAttribution: metzlerd commentedAlthough I haven't tried this, (not what forena was intended for) it might work. The one caveat is that you cannot embed multiple insert statements into a single forena SQL file. So an insert select might work, but muliple inserts in a single file would not.
I did try to make sure that all queries built using the forena query module (not .sql files) begin with select.
If you post an example data block of what you're trying to do, I might be able to better help you figure out why you might be getting these errors.
Dave
Comment #2
zakaryus CreditAttribution: zakaryus commentedThe problem is there aren't really any errors. An example of the data block sql file would be:
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.
Before, my columns were
For the sake of getting past the error I changed them all to varchar(45) and then encountered this error
and I still received the password error from above, also.
Thanks for the help,
Zak
Comment #3
metzlerd CreditAttribution: metzlerd commentedA couple of issues.
First, You may need to manually add the data block to your report because the gui tool is expecting output and won't let you "add the block" without it. So you'll need to manually add something like
Also you don't want this data block to fire without parameters, so you may want to use the --IF= syntax to make sure that this doesn't fire without the minimum required parameters. One way to get around both of these problems is to specify an alternative select statement for when there are no parameters... something like:
That being said, it looks like we're having a problem with the data being interpreted as an array.... which seems odd. Could you upgrade to 7.x-3.11 and see if this problem persists. The latest version of forena also has some typing support for parameters in the datablock which will help. You'll be able to specify an info section:
Comment #4
zakaryus CreditAttribution: zakaryus commentedI did attempt this
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
Comment #5
metzlerd CreditAttribution: metzlerd commentedThere are no api changes or anything like that in the 7.3.x branch, so you should be safe to download a newer revision of forena, you could always revert back quickly if you needed to. That being said, I always advise people to upgrade a test version first just in case.
Comment #6
zakaryus CreditAttribution: zakaryus commentedOk, I finally got the chance to upgrade. I attempted to recreate the report using:
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?
Comment #7
metzlerd CreditAttribution: metzlerd commentedYes, better bounds checking would solve this problem. I'll make it not return any data in the case that no data is returned. Will make this fix in the 7.x-3.x and 7.x-4.x branches.
Here's a patch rolled against the 7.x-3.11 release. Could you let me know if this strategy works for you?
Comment #8
zakaryus CreditAttribution: zakaryus commentedThanks 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:
Comment #9
metzlerd CreditAttribution: metzlerd commentedHere's a different patch to try... This worked for me.
Comment #10
zakaryus CreditAttribution: zakaryus commentedWell, 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
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:
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:
Comment #11
metzlerd CreditAttribution: metzlerd commentedSo this seeems to be a couple of different issues:
Indicates that you have a PDO database configured without a password. Do you have an extra data provider configured in forena by any chance, one that is not being used? Could you delete this to remove that as an erroneous error?
This is suggesting a problem in the data add portion of Forena. I've encountered this a couple of times but not when the data block returns proper data.
Could you retry the data add with the SQL code you showed me. I think the (array) issues are about what happens when the block doesn't return data, but I haven't confirmed this.
If you want to actually test the data block, I'd recommend doing so using the forena query tool rather than the add block portion of forena. Does that make sense? The data block testing can be done in Admin->Structure->Forena Reports -> Data.
Comment #12
zakaryus CreditAttribution: zakaryus commentedOk, 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!
Comment #13
metzlerd CreditAttribution: metzlerd commentedI believe that the reason that you're having trouble adding the report using the GUI tool is that when you specify parameters it doesn't actually return any data. I'll be looking to see if there is some bug that is causing this to occur.
In the mean time, my recommendation would be to get comfortable adding the data block manually to the report... That is inserting code of the following code into your report:
Most drivers don't allow multi-statement execution, so you'll want to invent a separate data block to query the data that was just entered. You can then use attributes to control which messages it diesplays.
I believe you'll find that if you change the data block back to use the types you want, then it will work in the data query tool as well... The real issue is that you keep trying to "Add the data block" to the report that has no data. If you let the if sytax generate the "Missing Required Parmaters" and add that to the report, it would probably work.
I don't know what you're use case is but this seems a very odd way to be getting data in a database. Are you sure you want to be doing this? Realize that forena is a query tool and isn't really designed to be inserting data to the database. I'd be a bit worried that data could be inserted just by crafting the right URL's here, and you are not quite getting the level of forms validation that you might get if you created an application yourself.
Parameters can be removed at any time after the data has been added using the parameters tab. Or by removing them from the FRX file directly.
Comment #14
zakaryus CreditAttribution: zakaryus commentedI 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
Comment #15
metzlerd CreditAttribution: metzlerd commentedSounds good. Have a great weekend!
Two things to clarify...
First, the fact array values are showing up on the data add screen when no data is returned is a bug. I'm looking into trying to reproduce this for a fix. Eventually this will get fixed.
Second, I am looking into hook implementations for custom modules that would allow you to easily embed a custom built form into a forena report. We do this currently, but it takes a bit of code to get it working... I'm thinking about simpifying that in D7.
I don't know about resetting the form values. I'd have to think about that for a bit. Everything forena does is based on parameters coming in on the url. The deeply re-entrant deep linkable reports is one of the things that gives forena it's power.
When you get back on monday, give some thought to whether you might be willing to do a tiny bit of drupal forms coding to make this happen. If so I think I can give you a more sustainable approach to this.
Comment #16
metzlerd CreditAttribution: metzlerd commentedHm... I was unable to recreate your values getting converted to arrays(). You haven't implemented any custom code here have you.... hook_forena_parameters_alter by any chance?
No rush... just asking.
Comment #17
zakaryus CreditAttribution: zakaryus commentedSomething 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:
<div frx:block="my_repos/my_data_block" />
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!
Comment #18
metzlerd CreditAttribution: metzlerd commentedI think we've decided that inserting code on a datablock run is outside the scope of forena. There are other ideas about inserting forms that could make this feature available in an addon module, which is what I think should be done here.