Port MSSql Database driver from CI3

[lonnieezell]

No description provided.

[JakeAi]

I actually have this basically working for a project I am working on. I haven't run into any issues yet but I haven't been doing anything crazy, just selects. I am clueless on how to submit my code though. I know it's through a PR.

[JakeAi]

Just added limit and offset functionality today.

[lonnieezell]

That's awesome. If you could submit a Pull Request when you get things working, I'll get started trying to make TravisCI get a unix-version of it setup, now that they support that. That way live database tests can be ran against it during the automated tests.

[JakeAi]

Sorry, I did a/the SQLSrv driver and not MSSQL. I'm sure they're probably close in terms of what I have done. I can look into it and see how different it is.

[lonnieezell]

Oh, no problem. Eventually SQLSrv is on the list, but MSSQL is one of the most used historically, so we wanted that to be one of the first batch ready for the Alpha/Beta.

[jaynarayan89]

@JakeAi Are you still working on this feature or you drop the idea?

[JakeAi]

@jaynarayan89 Yes, I am. I am using it in my own application. It would need more further testing as I'm only using it for reads right now. But I need to finish my app first before I can do that, and I don't see that happening anytime soon. I can PR it and someone can further check it out?

[jaynarayan89]

I would like to help, so I asked before starting it from scratch.

[JakeAi]

@jaynarayan89 oh, right on! I thought you might have been a "moderator" or something lol. I can send you what I have and you can check it out and further work on it?

[JakeAi]

Oh, also. This is SQLSrv and not MSSQL. I thought they were the same thing at the time. I honestly don't know the difference. I'm using it with SQL Server 2012 and Microsofts sqlserver drivers for php.

[jaynarayan89]

ok , that would be great.

[JakeAi]

@jaynarayan89 https://github.com/JakeAi/CI4-SQLSRV

[jaynarayan89]

@JakeAi Thanks. I did some research and found that sqlsrv is new extension used for connecting mssql server from php7 instead of mssql extension. the mssql extension is removed from php7 see this link

[JakeAi]

@jaynarayan89 cool, I'm glad I didn't waste time with it then. @lonnieezell should read this and maybe remove MSSQL from the project all together then.

[JakeAi]

@jaynarayan89 https://github.com/JakeAi/CI4-SQLSRV/blob/master/Builder.php#L53 this function was made by me to implement limit and offset. The fetch next is only supported in 2012 I believe. Just a warning!

[jaynarayan89]

@JakeAi arer you on codeigniter slack channel. If I need something to understand I will pm you there.

[JakeAi]

@jaynarayan89 No I am not. You can facebook message me or google hangouts.

[JakeAi]

@jaynarayan89 how is this driver coming?

[jaynarayan89]

@JakeAi Actually I was busy with other issues so did not started yet.I will start working on this issue next month because our new year is next week and Diwali festival has been started from today. And as a bonus my exams are going to start on 25th.

[trafficinc]

Should the driver be based on PDO or SQLSRV? The mssql_pconnect & mssql_connect are depreciated.

[JakeAi]

@trafficinc Microsoft has SQLSRV and PDO SQLSRV drivers. I think there should be one for each. MSSQL should go away.

[kovijanny]

@JakeAi This is good work, but then an little correction,
Invalid object name 'INFORMATION_SCHEMA.Columns'.
source line 349 in /Connection.php
valid INFORMATION_SCHEMA.COLUMNS (upper case all characters)

[trafficinc]

Ok, I may try to work on a PDO solution, I need this for one of my projects

[JakeAi]

@kovijanny what?

[kovijanny]

@JakeAi this is https://github.com/JakeAi/CI4-SQLSRV/blob/d1fe0eb43889bf81e478fc76862e1e86970a439e/Connection.php#L349
The _listColumns functions error then because Columns not equal COLUMNS and MsSQL newest version just like this so work

[JakeAi]

@kovijanny I made this for my project and only went far enough to make it work. @jaynarayan89 was going to pick up where I left off.

[kovijanny]

Sorry,
OK, Thanks.

[nfaiz]

@qury Sorry, I mean I can't find your port at the new/sqlsrv repo.

[qury]

@fdurantelp , @nfaiz Sorry, it was my bad, it is a new fork and i was experimenting with signed commits, which apparently did not work out as i've expected. However the driver is there now:

[fdurantelp]

Thanks!

[puschie286]

(* thanks for continue the work :)
@qury insertID() needs a null coalescing with 0 to ensure the int return type
"->insert_id ?? 0"

[qury]

Thanks @puschie286 , i've updated that, and have started adding schema support as well (current code was using 'dbo' most of the time).

Remaining issues to fix:
https://gist.github.com/qury/5642c620d6f6938331e0f66be1f14c56

[qury]

For some reason MetadataTest failes if i run the all the live tests, however it passes if i run only the MetadataTest.php
Does anyone have any idea why?

[qury]

Just an update: replace() is implemented finally (it is ugly though)
The only errors and failures i have are from ModelTest and DEBugTest really.

https://gist.github.com/qury/2cbbe5d8748aace511a1c2f78d9fdc6c

[lonnieezell]

@qury That's awesome to hear! We will be releasing 4.1 later this year and were hoping to get this and the Oracle drivers tested and in place for that release. Thanks so much for this port.

[nfaiz]

Just an update: replace() is implemented finally (it is ugly though)
The only errors and failures i have are from ModelTest and DEBugTest really.

https://gist.github.com/qury/2cbbe5d8748aace511a1c2f78d9fdc6c

@qury Spread operator support in array is for PHP 7.4 and later? Btw good job so far.

[qury]

Thanks! I will re-write that. (not to self: don't skip coffee)

[qury]

progress update:

• tests/system/Database/Builder/ -> All test are passing

• tests/system/Database/Live/ -> 1 error / 5 failures -> https://gist.github.com/qury/d436d9df911fa3ec0d441b0d0a772eae

• tests/system/Database/Migrations/ -> 7 errors on a blank database

• tests/system/Database/DatabaseTestCaseTest -> 1 error

• tests/system/Database/ModelFactoryTest -> all failing

@nfaiz I've replaced array spread with array_merge.

[davislasis]

hi,
so is the ODBC and / or driver for MSSQL connection done or still in development?
Just asking on a progress. How exactly i can see it's state and progress?
I really need this driver in CI4 for axapta connection

If you need any specific help, maybe you can point that out and maybe i will be able to find a free minute to help out with it.

Thanks

[qury]

@davislasis
Check https://github.com/qury/CodeIgniter4/
The driver is in the branch new/sqlsrv, it works 95%

I will pick up development on it again in 2 weeks

[MGatner]

Hi @qury - I'm afraid I haven't looked at it directly since I don't have a MSSQL database handy, but I see you've spent a lot of time on it. Have you made any more progress since the last comment? When you think it is close, open a PR here so we can start reviews and it can run tests against the main repo.

[qury]

@MGatner , i've just updated my fork, there are still a few issues so i'm catching up on them. Will submit a PR soon.

[dsoulayrol]

For what it's worth, I have began to use @qury 's branch to work with a MS database, and so far so good. It's true my requests are quite simple at the moment... Thanks for the work. I am eagerly waiting for a merge.

[malikdoksoz]

@MGatner , i've just updated my fork, there are still a few issues so i'm catching up on them. Will submit a PR soon.

I'm looking forward :)

[qury]

I've created a PR, i'm sure the port could be further refined, but i think it is at a stage where it is usable enough.

[warden1975]

@qury I'm having issues using prepared query, it's expecting Closure but Query is being passed. Any idea? Thanks in advance.

[qury]

Are you using my out of date driver or the one that was cleaned up/completed by michalsn and is merged into CI4 develop branch? Also would be nice to see what you are trying to do and what the error message is.

[warden1975]

Are you using my out of date driver or the one that was cleaned up/completed by michalsn and is merged into CI4 develop branch? Also would be nice to see what you are trying to do and what the error message is.

I'm using yours and nope I haven't checked @michalsn - I'll take a look as well

I'm trying execute this line of code:

use CodeIgniter\Database\Query;

$pQuery = $db->prepare(function($db)
{
    $sql = "INSERT INTO user (name, email, country) VALUES (?, ?, ?)";

    return (new Query($db))->setQuery($sql);
});

but it's returning error:

Argument 1 passed to CodeIgniter\\Database\\BaseConnection::prepare() must be an instance of Closure, instance of CodeIgniter\\Database\\Query given

[qury]

Well at this stage you should check out the development branch of Codeigniter4 as the driver has been merged

[warden1975]

ok thanks @qury

[hdrodriguez]

Hi. Recently the handler for sqlsrv has been incorporated into the develop version of codeigniter, doing some tests I have not been able to configure the settings for the use of schemas, everything is being stored in dbo. and for example I have different schemes like adm, transactios, blgs etc. anyone have any idea how to solve this problem.

[MGatner]

@hdrodriguez you're probably better off asking for help on the forums or Slack

[paulbalandan]

@lonnieezell I think this can be closed already as the sqlsrv driver has been in place?