php pdo sqlsvr driver

INTONE

I am using the sql server driver and trying to get some table structure to build dynamic queries but I cannot get the table structure returned. These standard queries do not work.

SELECT TOP 0 * FROM table; gives empty array
select *  from information_schema.columns  where table_name = 'table_name' order by ordinal_position gives error.

Is there something I can change in the driver to make this work? Any help is appreciated.

allan

Is this using the Editor Database class? If so, can you show me the code you are using please?

Thanks,
Allan

INTONE

Allan,

I am using the pdo sqlsrv as my database abstraction layer for doing none datatable routines. For example with this:

$rows = $db->raw()->exec( "SELECT TOP 0 * FROM ".$table )->fetch();

I can get the column names of any table and dynamically write all my datatables/editor serverside and client side scripts to a file which I can later modify as needed.

tangerine

So are you saying this is now working?

INTONE

Hello tangerine, no it does not work. I am getting no value returned. In sql server this sql above would return the column names but not when I use the php pdo sqlsvr driver above. What I am actually doing is adapting php/mysql, editor/datatables utilities I have written to speed up my work to now work on a php/sql server environment. Sorry for clicking the answer above as the correct answer.

allan

Try this:

$rows = $db->sql( "SELECT TOP 0 * FROM ".$table )->fetchAll();

Allan

INTONE

Hello Allan,

I am still getting an empty array:

Array
(
)

allan

I've just read the SQL again - "TOP 0" is going to select 0 rows unless I'm missing something? What happens if you use "TOP 1"?

Allan

INTONE

Hello Allan,

From what I seeing I will just have to settle with having at least one record in the table, thus:

$rows = $db->sql( "SELECT TOP 1 * FROM ".$table )->fetchAll();

will return a record and I can extract column names. As stated here https://editor.datatables.net/docs/1.6.2/php/class-DataTables.Database.html, not all vendor specific queries can be expected from datatables/editor database abstraction.

allan

Possibly a muppet question, but why would you want to select 0 records? Are you attempting to just find the column names?

If so, you'd be better doing something like select * from information_schema.columns where table_name = 'tableName'. Consider for example the case where your table has 0 rows - that select top 1... wouldn't work.

Allan