why is server putting [] around the middle parameter?

crcucb

$sqlStr = '(select * from tvf_StatReport2(' . $IsProd . ',' . $groupLevel . ',' . $vsTimeIncrementHrs . ' ) ) as JYStats';
Editor::inst( $db, $sqlStr, 'StatsAID' )
the above os giving me:
An SQL error occurred: SQLSTATE[42S22]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name '1'.

when I look at the console devmode, the query looks like:

.. FROM (select * from tvf_StatReport2(0, [1], 0 ) ) JYStats...

allan

I guess it is an array. You'd need to show me the full setup though. What is $groupLevel?

Allan

crcucb

$groupLevel is a variable that I was first setting to $_POST['groupLevel']

Then I thought I would just assign based on the value of $_POST['groupLevel']:

$groupLevel=1;
if ( isset($_POST['groupLevel']) ) {
     if ($_POST['groupLevel'] === 1) { $groupLevel=1 ;};
     if ($_POST['groupLevel'] === 2) { $groupLevel=2 ;};
};

this had no effect.

Then I wondered what would happen if just hardcoded:

$sqlStr = '(select * from tvf_StatReport2( 1 , 1 , 24 ) ) as JYStats';
Editor::inst( $db, $sqlStr, 'StatsAID' )

And it's still doing it (from console devmode):
FROM (select * from tvf_StatReport2( 1, [1], 24 ) ) JYStats

Here is the start of my function declaration:

```js
CREATE FUNCTION [crcole_crc].[tvf_StatReport2]
(
-- Add the parameters for the function here
@IsProd as bit = 1,
@GroupLevel as tinyint = 1,
@VsStatsHours as tinyint = 24

)
```js

The second and third parameters are tinyint and for some reason, even when I hardcode numbers in the call it's putting [] around the second parameter.

allan

$sqlStr = '(select * from tvf_StatReport2( 1 , 1 , 24 ) ) as JYStats';
Editor::inst( $db, $sqlStr, 'StatsAID' )

I'd be surprised if that worked at all. It wasn't designed to work like that I'm afraid. You'd need to create a VIEW with that query, and then use the view as a table name.

Are you trying to make this fully editable, or just get the data?

Allan

crcucb

I am trying to just get data.

allan

Ah! Don't use an Editor instance for that.

$data = $db->sql('select * from ...')->fetchAll();

Will do it. The $db handle instance instance of Database which has a bunch of handy methods on it.

Allan