php pdo sqlsvr driver

php pdo sqlsvr driver

INTONEINTONE Posts: 153Questions: 58Answers: 6

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.

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

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

    Thanks,
    Allan

  • INTONEINTONE Posts: 153Questions: 58Answers: 6

    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.

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394
    Answer ✓

    So are you saying this is now working?

  • INTONEINTONE Posts: 153Questions: 58Answers: 6
    edited April 2017

    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.

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin
    edited April 2017

    Try this:

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

    Allan

  • INTONEINTONE Posts: 153Questions: 58Answers: 6

    Hello Allan,

    I am still getting an empty array:

    Array
    (
    )
    
  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    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

  • INTONEINTONE Posts: 153Questions: 58Answers: 6

    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.

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin

    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

This discussion has been closed.