Hi
I keep getting the following error when trying to create a report:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1065 Query was empty: ; Array ( ) in FrxDrupal->data() (line 45 of /home/sltru/public_html/aphasia/sites/all/modules/forena/plugins/FrxDrupal.inc).

The SQL file is:

--ACCESS=access content select count(*)from node

And the report file produced is:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE root [
<!ENTITY nbsp "&#160;">
]>
<html xmlns:frx="urn:FrxReports">
  <head>
    <title>test</title>
    <frx:category/>
    <frx:options/>
    <frx:fields/>
    <frx:parameters/>
    <frx:docgen/>
  </head>
  <body/>
</html>

I followed the video regarding setup but I assume I must of missed something??

Regards
Julie

Comments

metzlerd’s picture

metzlerd commented

A couple of things.
The access_control comment should be on its own line: 

--ACCESS=access content
SELECT COUNT(1) FROM node

Also there is a bug in the 7.2.0 code tree that sends a blank SQL query when it can't find the data file for the block. It should fail silently, but isn't. I'm pretty close to releasing 7.2.1 which has a few bug fixes including this one.

It would be helpful to know what repository you created, and how you are trying to add the block to the report. If you created a new repository called "sitesql", and saved a file called node_count.sql, then you should be adding the sitesql/node_count block to your report. Is that what you're doing when you get this error?

Dave

jward50’s picture

jward50 commented

Hi Dave
Thanks for the feedback, the error I was getting was a permissions issue.
I am now trying to run the following SQL

--ACCESS=access content
select a.title AS SOFTWARE,
COALESCE((select 'Y'
from taxonomy_index b, taxonomy_term_data c
where c.tid = b.tid
and c.vid = 3
and b.tid = 17
and a.nid = b.nid), 'N') AS 'Results Recorded',
COALESCE((select 'Y'
from taxonomy_index b, taxonomy_term_data c
where c.tid = b.tid
and c.vid = 3
and b.tid = 13
and a.nid = b.nid), 'N') AS 'Free Trial',
COALESCE((select 'Y'
from taxonomy_index b, taxonomy_term_data c
where c.tid = b.tid
and c.vid = 3
and b.tid = 19
and a.nid = b.nid), 'N') AS 'Tutorials',
COALESCE((select 'Y'
from taxonomy_index b, taxonomy_term_data c
where c.tid = b.tid
and c.vid = 3
and b.tid = 15
and a.nid = b.nid), 'N') AS 'Personalise',
COALESCE((select 'Y'
from taxonomy_index b, taxonomy_term_data c
where c.tid = b.tid
and c.vid = 3
and b.tid = 16
and a.nid = b.nid), 'N') AS 'Advice',
COALESCE((select 'Y'
from taxonomy_index b, taxonomy_term_data c
where c.tid = b.tid
and c.vid = 3
and b.tid = 17
and a.nid = b.nid), 'N') AS 'Record Self'
from node a
where a.type = 'Software'

but am getting a lot of errors like the following:

Warning: SimpleXMLElement::__construct() [simplexmlelement.--construct]: Entity: line 2: parser error : Specification mandate value for attribute Recorded in forena_add_data_block_form() (line 1693 of /home/sltru/public_html/aphasia/sites/all/modules/forena/forena.admin.inc).

Is there a set of instructions as to how the SQL code has to be modified to run in Forena Reports.
Many Thanks
Julie

metzlerd’s picture

metzlerd commented

Because forena prerenders data into XML you need to make sure that your column names are valid XML tag names. Remove the spaces in your column names and all should be well.

metzlerd’s picture

metzlerd commented

Looks like this was filxed.

metzlerd’s picture

metzlerd commented
Status: Active » Closed (fixed)