Php – ODBC Driver 17 for SQL Server incorrect syntax near . ”

odbcPHPsqlsql server

I am trying to create table in MS SQL server management 17 using php.

my query is:

                            $mid=$row['MASTER_ID'];
                            echo $mid;
                            $table_solution="CREATE TABLE Q_MASTERID_'$mid'_Solution(
                                            MASTER_ID INT ,  
                                            PRODUCT_NAME varchar(255),
                                            CUSTOMER_NAME varchar(255),
                                            Q_TEMP_ID INT IDENTITY(1,1),
                                            Q_DESC nvarchar(600) NULL,
                                            Q_CATEGORY nvarchar(255)  NULL,
                                            Q_SUB_CATEGORY nvarchar(255) NULL,
                                            SCOPE nvarchar(255) NULL 
                                            ) ON PRIMARY"; 

                            $solution_alter="ALTER TABLE Q_MASTERID_'$mid'_Solution ADD Q_ID AS CONCAT ('A',CAST(Q_TEMP_ID AS VARCHAR(100)))PERSISTED" ;

                             $res=sqlsrv_query($con,$table_solution) or die("Couldn't execute create query".print_r(sqlsrv_errors(), true));
                            $res1=sqlsrv_query($con,$solution_alter) or die("Couldn't execute alter query");

i am getting following error :

Couldn't execute create queryArray ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 102 [code] => 102 [2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ''. [message] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ''. ) )

if i remove $mid in create query im getting following error

Couldn't execute create queryArray ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 156 [code] => 156 [2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'PRIMARY'. [message] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'PRIMARY'. ) )

i want to add dynamic variable called $mid while creating table "CREATE TABLE Q_MASTERID_'$mid'_Solution"
help me to resolve this…

Best Answer

I think you are trying for:

CREATE TABLE Q_MASTERID_Solution (
    MASTER_ID INT ,  
    PRODUCT_NAME varchar(255),
    CUSTOMER_NAME varchar(255),
    Q_TEMP_ID INT IDENTITY(1,1),
    Q_DESC nvarchar(600) NULL,
    Q_CATEGORY nvarchar(255)  NULL,
    Q_SUB_CATEGORY nvarchar(255) NULL,
    COPE nvarchar(255) NULL ,
    Q_ID AS ( CONCAT('A', CAST(Q_TEMP_ID AS VARCHAR(100))) ) PERSISTED
) ON "default"

I see no reason to include the ON "default" because that is the default partition scheme if none is specified. If you do specify one, then you need to specify an existing one.

You can include the definition of q_id in the name.

It is a really bad practice to create multiple tables with identical schemas. Instead, include MASTER_ID as a column in the table and filter for each master id.

Related Topic