none
Run SQL Script using PHP Driver - Possible??

    Question

  • Hello all,

    Is there actually a way to run a SQL script (file.sql) using the PHP Driver?

    I have searched and searched and all I could get is using SQLCMD to run sql scripts, but I don't want to use SQLCMD - I actually swicthed from it since it was another program that was running unnecesailyy.

    Can I use sqlsrv_query to do this?

    Thanks all for any help


    Friday, November 27, 2009 6:06 PM

Answers

  • No, but this should not be a tough job for you to retrieve the T-SQL statements from the file first. You can first read the T-SQL script from file.sql via the function fopen and then use the function sqlsrv_query to execute the T-SQL script. For example:
    $myFile = "file.sql";
    $fh = fopen($myFile, 'r');
    $tsql= fread($fh, filesize($myFile));
    fclose($fh);
    $serverName = "(local)";
    $connectionInfo = array("Database"=>"AdventureWorks");
    $conn = sqlsrv_connect( $serverName, $connectionInfo);

    /* Execute the query. */
    if( sqlsrv_query( $conn, $tsql))
    {
          echo "Statement executed.\n";
    }
    else
    {
          echo "Error in statement execution.\n";
          die( print_r( sqlsrv_errors(), true));
    }

    /* Free connection resources. */
    sqlsrv_close( $conn);




    Best regards,
    Charles Wang
    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Monday, November 30, 2009 10:56 AM

All replies

  • No, but this should not be a tough job for you to retrieve the T-SQL statements from the file first. You can first read the T-SQL script from file.sql via the function fopen and then use the function sqlsrv_query to execute the T-SQL script. For example:
    $myFile = "file.sql";
    $fh = fopen($myFile, 'r');
    $tsql= fread($fh, filesize($myFile));
    fclose($fh);
    $serverName = "(local)";
    $connectionInfo = array("Database"=>"AdventureWorks");
    $conn = sqlsrv_connect( $serverName, $connectionInfo);

    /* Execute the query. */
    if( sqlsrv_query( $conn, $tsql))
    {
          echo "Statement executed.\n";
    }
    else
    {
          echo "Error in statement execution.\n";
          die( print_r( sqlsrv_errors(), true));
    }

    /* Free connection resources. */
    sqlsrv_close( $conn);




    Best regards,
    Charles Wang
    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Monday, November 30, 2009 10:56 AM
  • Sorry for the very late reply, but that is a fantastic idea! I will be using that.

    Seasons greetings all. :)
     
    Thanks,
    GMan
    Thursday, December 24, 2009 7:57 PM
  • There's a very useful php function, file_get_contents() , that reads a whole file into a string in one call.
    Wednesday, December 30, 2009 9:40 AM
  • Hi, were you able to run your scripts? If so, how? I think there's a limit to running a single query using sqlsrv_query.

    I'm trying to run a script file that contains an ALTER PROC, but I get errors.

    Hope you still get to read this :)

    Rafa

    Wednesday, February 15, 2012 1:17 AM
  • Some commands like ALTER PROC or CREATE PROC must be the first and only commands in a statement.

    If your statement works, it will. also work in sqlsrv_query.

    If you want to run several such commands in one statement you could try this:

    $tsql = <<<SQL
    EXEC(?);
    EXEC(?);
    EXEC(?);
    SQL;
    
    $conn = sqlsrv_connect($server, $options);
    
    $params = array(
    'ALTER PROC myProc as ...',
    'ALTER PROC myOtherProc as ...',
    'CREATE PROC myNewProc as ...');
    
    $stmt = sqlsrv_query($conn, $tsql, $params);
    
    if ( $stmt )
    {
        do 
        {
            // something...
        } while (sqlsrv_next_result($stmt)) ;
    }


    Rob

    Wednesday, February 15, 2012 1:11 PM