Issue running on SQL Server

[rotunomp]

Hello, I am running this for the first time on a SQL Server connection and I am getting the below error. I was able to get it working on a local postgres DB, but not the SQL Server connection.

[PDOException (HY093)]
SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

• DB: SQL Server 2019
• Laravel Version: 8.75.0
• PHP Version: 8.1.9
• Migrations Generator Version: 6.5.1
• Doctrine DBAL Version [doctrine/dbal]: 3.5.1

Stack Trace:

  at /vendor/doctrine/dbal/src/Driver/PDO/Statement.php:134

 PDOStatement->execute() at /vendor/doctrine/dbal/src/Driver/PDO/Statement.php:134

 Doctrine\DBAL\Driver\PDO\Statement->execute() at /vendor/doctrine/dbal/src/Driver/Middleware/AbstractStatementMiddleware.php:69

 Doctrine\DBAL\Driver\Middleware\AbstractStatementMiddleware->execute() at /vendor/doctrine/dbal/src/Connection.php:1063

 Doctrine\DBAL\Connection->executeQuery() at /vendor/doctrine/dbal/src/Schema/SQLServerSchemaManager.php:406

 Doctrine\DBAL\Schema\SQLServerSchemaManager->selectTableNames() at /vendor/doctrine/dbal/src/Schema/AbstractSchemaManager.php:356

 Doctrine\DBAL\Schema\AbstractSchemaManager->doListTableNames() at /vendor/doctrine/dbal/src/Schema/SQLServerSchemaManager.php:40

 Doctrine\DBAL\Schema\SQLServerSchemaManager->listTableNames() at /vendor/kitloong/laravel-migrations-generator/src/DBAL/DBALSchema.php:33

 KitLoong\MigrationsGenerator\DBAL\DBALSchema->getTableNames() at /vendor/kitloong/laravel-migrations-generator/src/MigrateGenerateCommand.php:185

 KitLoong\MigrationsGenerator\MigrateGenerateCommand->filterTables() at /vendor/kitloong/laravel-migrations-generator/src/MigrateGenerateCommand.php:110

 KitLoong\MigrationsGenerator\MigrateGenerateCommand->handle() at /vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php:36

[kitloong]

Hi @rotunomp , thank you for reporting this issue.

Looks like the error is caused by

Doctrine\DBAL\Schema\SQLServerSchemaManager->selectTableNames() at /vendor/doctrine/dbal/src/Schema/AbstractSchemaManager.php:356

Doctrine\DBAL\Schema\AbstractSchemaManager->doListTableNames() at /vendor/doctrine/dbal/src/Schema/SQLServerSchemaManager.php:40

Do you able to find any related issues in doctrine/dbal ? If you believe this is a bug, you could raise a PR to doctrine/dbal.

[rotunomp]

Sorry for the late response, thank you for replying to this issue.

I opened a bug report with DBAL over here:

https://github.com/doctrine/dbal/issues/5816

I took quite some time to replicate the issue without the Laravel DB facade but couldn't get it to work. I figure the devs over at dbal are going to ask me to try without Laravel and just using the regular php sqlsrv connection. So we'll see what they say.

[rotunomp]

Wanted to give some closure on this. I spent far too much time trying to set up an SQL Server connection from scratch as opposed to using Laravel. I can't believe how hard it is! It ended up never working and I don't have the time at my job to mess around with it more, unfortunately. I think I'll have to find another way to make the migrations :(

[kitloong]

@rotunomp I am sorry to hear this.

Could you share the schema and steps to repro issue at here? With Laravel is fine.

[rotunomp]

Thank you for your time. I can give you a rundown of our stack. I did just realize that our company is not using the Microsoft ODBC, but FreeTDS instead. I am attempting to set up a version on my local environment using ODBC to see if the same issue will happen.

OS: Amazon Linux 2
Database: SQL Server 2019
PHP: 8.1.9
Laravel:  9.39.0
FreeTDS: 1.3.3

Here's an example table which does not work:

CREATE TABLE [dbo].[account_types] (
    [account_type_id] varchar(2),
    [account_type_name] varchar(100)
);

Here is the database.php entry for the sqlsrv connection:

        'sqlsrv' => [
            'driver' => 'sqlsrv',
            'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '1433'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'prefix' => '',
            'prefix_indexes' => true,
            'options' => [
                PDO::ATTR_TIMEOUT => 300,
            ],
        ],

I am running php artisan migrate:generate --tables="account_types"

[rotunomp]

I was able to get my local environment (Mac OS) set up and running properly using Microsoft ODBC version 17 (version 18 had some new requirement with connection encryption or something like that). The migration generation works now!

I tried for a little while to install FreeTDS and confirm it was the issue, but I could not find good instructions online on how to configure it with PHP on Mac OS. Using Microsoft ODBC on my Mac to generate the migrations works well enough for my purposes so I'll just use this for now

[kitloong]

I am glad to hear that

[Sammyjo20]

Hey @rotunomp

I'm currently experiencing the same issue with this package. I'm using an M1 Mac and I used this article to setup an Azure SQL Edge database on my machine. Everything seems to work in Laravel with the SQL Server driver except for this command. Did you have any more information on how you fixed this? I'm quite new to SQL Server so I wasn't sure how to implement ODBC. Anything you can point me to?

This was the article I used to configure SQL server

https://medium.com/geekculture/docker-express-running-a-local-sql-server-on-your-m1-mac-8bbc22c49dc9

Any help would be really appreciated ❤️

[Sammyjo20]

php artisan migrate:generate --connection=sqlsrv
Using connection: sqlsrv

Doctrine\DBAL\Exception\DriverException

An exception occurred while executing a query: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

at vendor/doctrine/dbal/src/Driver/API/SQLSrv/ExceptionConverter.php:67
     63▕             case 18456:
     64▕                 return new ConnectionException($exception, $query);
     65▕         }
     66▕
  ➜  67▕         return new DriverException($exception, $query);
     68▕     }
     69▕ }
     70▕

      +28 vendor frames
  29  artisan:37

[rotunomp]

@Sammyjo20 is ODBC required for Azure SQL Edge? If so have you already installed it? For SQL Server at least, I wouldn't be able to make the Laravel DB connection work without ODBC.

I'm running an Intel Macbook so I don't have any specific instructions for M1, although this is the guide I used to setup ODBC on my Mac:

https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-sql-server-macos?view=sql-server-ver16

I used Version 17 instead of 18 of the ODBC because version 18 had something involving encryption that I didn't understand. But our SQL Server instance might be configured differently than yours because ours has been around for a very long time.

[Sammyjo20]

Thanks so much for the quick reply @rotunomp!

I'm using Docker so I didn't have to worry about the hardware side of things, luckily that command worked and I had a SQL Edge server running in Docker that I could connect to in Laravel + Table Plus.

Do you have your SQL server hosted somewhere then? Or does it run locally on your Macs?

[Sammyjo20]

Sorry just to clarify - the command in the article worked, I installed ODBC (Thank you) but still a bit stuck, perhaps I need to setup an Azure account to have a SQL server to connect to.

[rotunomp]

@Sammyjo20 Yes, our SQL Server is running on a Linux instance. Sorry I can't give you much other advice, it's a mystery to me as well why it suddenly worked for me.

I ended up digging into this library's code as well as Doctrine\DBAL, which I mentioned earlier on in this thread. You can try bringing up the issue with them, although they are going to ask you to connect with their code instead of Laravel's. Below is the code I used to connect and get table names (the code which is throwing an error for you) using their library.

I wasn't able to get it to connect due to some missing PHP plugins or something, and I wasn't going to mess with my company's PHP instance. But you probably have the freedom to do so. Good luck!

        $connectionParams = [
            'dbname' => 'mydb',
            'user' => 'user',
            'password' => 'secret',
            'host' => 'localhost',
            'port' => 'port',
            'driver' => 'pdo_sqlsrv',
        ];
        $conn = \Doctrine\DBAL\DriverManager::getConnection($connectionParams);
        $manager = $conn->createSchemaManager();
        $manager->listTableNames();

[Sammyjo20]

Thanks so much for the help!

[rw152]

I modified vendor/doctrine/dbal/src/Connection@executeQuery and set

$params=[];

on line 1081 (approximately) and it seemed to work.

My thought is when DBAL attempts to run the selectTableNames method in vendor/doctrine/dbal/src/Schema/SQLServerSchemaManager@selectTableNames it passes in a parameter (in my case, the DB name). The executed statement, however, should not have a parameter and thus too many are passed in.

I didn't dig any further than that, but setting that parameter to an empty array patched it for now.