mam problem z apache2 i php 5.4.14 na debianie (raspbian-ie).
Napisałem dość spore zapytanie i nie mogę uzyskać jego wyników przy odpytaniu ms sql servera 2008 r2 i FreeTDS. Może ktoś pomoże, bo nie działa mssql_query() anie mssql_init() i mssql_execute() dla procedury składowanej, którą stworzyłem z zapytania aby bardziej to wszystko czytelne było.
Oto ustawienia z phpinfo() serwera:
PHP Version 5.4.4-14+deb7u12
System Linux pi 3.12.22+ #691 PREEMPT Wed Jun 18 18:29:58 BST 2014 armv6l
Build Date Jul 8 2014 21:59:57
Server API Apache 2.0 Handler
PHP API 20100412
PHP Extension 20100525
Zend Extension 220100525
Zend Extension Build API220100525,NTS
PHP Extension Build API20100525,NTS
Debug Build no
Thread Safety disabled
Apache Version Apache/2.2.22 (Debian)
Apache API Version 20051115
User/Group www-data(33)/33
Max Requests Per Child: 0 - Keep Alive: on - Max Per Connection: 100
Timeouts Connection: 300 - Keep-Alive: 5
Virtual Server Yes
Server Root /etc/apache2
Loaded Modules core mod_log_config mod_logio mod_version prefork http_core mod_so mod_alias mod_auth_basic mod_authn_file mod_authz_default mod_authz_groupfile mod_authz_host mod_authz_user mod_autoindex mod_cgi mod_deflate mod_dir mod_env mod_mime mod_negotiation mod_php5 mod_reqtimeout mod_setenvif mod_status
Directive Local Value Master Value
engine 1 1
last_modified 0 0
xbithack 0 0
Apache Environment
Variable Value
HTTP_HOST 192.168.0.101
HTTP_USER_AGENT Mozilla/5.0 (Macintosh; Intel Mac OS X 10.10; rv:31.0) Gecko/20100101 Firefox/31.0
DOCUMENT_ROOT /var/www
SERVER_ADMIN webmaster@localhost
SCRIPT_FILENAME /var/www/phpinfo.php
Core
PHP Version 5.4.4-14+deb7u12
mail.add_x_header On On
mail.force_extra_parameters no value no value
mail.log no value no value
max_execution_time 30 30
max_file_uploads 20 20
max_input_nesting_level 64 64
max_input_time 60 60
max_input_vars 1000 1000
memory_limit 128M 128M
open_basedir no value no value
output_buffering 4096 4096
mssql
MSSQL Support enabled
Active Persistent Links 0
Active Links 0
Library version FreeTDS
Directive Local Value Master Value
mssql.allow_persistent On On
mssql.batchsize 0 0
mssql.charset no value no value
mssql.compatability_mode Off Off
mssql.connect_timeout 5 5
mssql.datetimeconvert On On
mssql.max_links Unlimited Unlimited
mssql.max_persistent Unlimited Unlimited
mssql.max_procs Unlimited Unlimited
mssql.min_error_severity 10 10
mssql.min_message_severity 10 10
mssql.secure_connection Off Off
mssql.textlimit Server default Server default
mssql.textsize Server default Server default
mssql.timeout 180 180
a to zawartość pliku php:
$link = mssql_connect("testsrv\insoft2008", "raport", "raport"); mssql_select_db("TT_Sklep", $link); $sql = " IF OBJECT_ID('tempdb..#tmpOstMiesDokId') IS NOT NULL DROP TABLE #tmpOstMiesDokId IF OBJECT_ID('tempdb..#tmpOstMiesDetale') IS NOT NULL DROP TABLE #tmpOstMiesDetale IF OBJECT_ID('tempdb..#tmpDelta1M') IS NOT NULL DROP TABLE #tmpDelta1M IF OBJECT_ID('tempdb..#tmpDelta3M') IS NOT NULL DROP TABLE #tmpDelta3M IF OBJECT_ID('tempdb..#tmpDelta6M') IS NOT NULL DROP TABLE #tmpDelta6M IF OBJECT_ID('tempdb..#tmpDelta0') IS NOT NULL DROP TABLE #tmpDelta0 IF OBJECT_ID('tempdb..#tmpD1M') IS NOT NULL DROP TABLE #tmpD1M IF OBJECT_ID('tempdb..#tmpD3M') IS NOT NULL DROP TABLE #tmpD3M IF OBJECT_ID('tempdb..#tmpD6M') IS NOT NULL DROP TABLE #tmpD6M declare @miesAkt int declare @rokAkt int declare @poprzMies int declare @poprz3Mies int declare @poprz6mies int declare @poprzRok int set @miesAkt = month(getdate()) set @rokAkt = YEAR(GETDATE()) IF @miesAkt - 1 <= 0 BEGIN set @poprzMies = 12 set @poprzRok = @rokAkt - 1 END ELSE set @poprzMies = @miesAkt - 1 set @poprzRok = @rokAkt IF @miesAkt - 3 <= 0 BEGIN set @poprz3Mies = 12 - 3 + @miesAkt set @poprzRok = @rokAkt - 1 END ELSE set @poprz3Mies = @miesAkt - 3 set @poprzRok = @rokAkt IF @miesAkt - 6 <= 0 BEGIN set @poprz6mies = 12 - 6 + @miesAkt set @poprzRok = @rokAkt - 1 END ELSE set @poprz6mies = @miesAkt - 6 set @poprzRok = @rokAkt create table #tmpOstMiesDokId ( rownum int NOT NULL IDENTITY(1,1), DokId int, primary key (rownum) ) create table #tmpDelta1M ( rownum int NOT NULL IDENTITY(1,1), DokId int, TowId int, Ilosc numeric(10,4), CenaDomyslna numeric(10,4), Netto numeric(10,4), Podatek numeric(10,4), Wartsc numeric(10,4) ) create table #tmpDelta3M ( rownum int NOT NULL IDENTITY(1,1), DokId int, TowId int, Ilosc numeric(10,4), CenaDomyslna numeric(10,4), Netto numeric(10,4), Podatek numeric(10,4), Wartsc numeric(10,4) ) create table #tmpDelta6M ( rownum int NOT NULL IDENTITY(1,1), DokId int, TowId int, Ilosc numeric(10,4), CenaDomyslna numeric(10,4), Netto numeric(10,4), Podatek numeric(10,4), Wartsc numeric(10,4) ) create table #tmpDelta0 ( rownum int NOT NULL IDENTITY(1,1), DokId int, TowId int, Kod varchar(20), Nazwa varchar(100), StanMag numeric(10,4), Minimum numeric(10,4), JM varchar(10), Ilosc numeric(10,4), IloscDelta1M numeric(10,4), IloscDelta3M numeric(10,4), IloscDelta6M numeric(10,4), CenaDomyslna numeric(10,4), Netto numeric(10,4), Podatek numeric(10,4), Wartosc numeric(10,4), Wartosc1M numeric(10,4), Wartosc3M numeric(10,4), Wartosc6M numeric(10,4) ) Insert into #tmpOstMiesDokId (DokId) Select D.DokID From Dok D Where D.Data >= cast(@poprzRok as varchar(4)) + '-' + CAST(@poprzMies as varchar(2)) + '-01' and D.Data < GETDATE() and D.TypDok in (2,20,31) order by Data desc declare @count int declare @rowNumMax int set @count = 1 select @rowNumMax = MAX(rownum) from #tmpOstMiesDokId as int; while (@count <= @rownumMax) BEGIN insert into #tmpDelta1M SELECT PD.DokId, PD.TowId, SUM(PD.IloscPlus) as Ilosc, PD.CenaDomyslna, PD.Netto, PD.Podatek, sum(PD.Wartosc) as Wartosc FROM PozDok PD WHERE PD.DokId = (SELECT TOM.DokId FROM #tmpOstMiesDokId TOM WHERE TOM.rownum = @count) GROUP BY PD.DokId, PD.CenaDomyslna, PD.Netto, PD.Podatek, PD.TowId order by PD.TowId SET @count = @count + 1 END Insert into #tmpOstMiesDokId (DokId) Select D.DokID From Dok D Where D.Data >= cast(@poprzRok as varchar(4)) + '-' + CAST(@poprz3Mies as varchar(2)) + '-01' and D.Data < GETDATE() and D.TypDok in (2,20,31) order by Data desc set @count = 1 select @rowNumMax = MAX(rownum) from #tmpOstMiesDokId as int; while (@count <= @rownumMax) BEGIN insert into #tmpDelta3M SELECT PD.DokId, PD.TowId, SUM(PD.IloscPlus) as Ilosc, PD.CenaDomyslna, PD.Netto, PD.Podatek, sum(PD.Wartosc) as Wartosc FROM PozDok PD WHERE PD.DokId = (SELECT TOM.DokId FROM #tmpOstMiesDokId TOM WHERE TOM.rownum = @count) GROUP BY PD.DokId, PD.CenaDomyslna, PD.Netto, PD.Podatek, PD.TowId order by PD.TowId SET @count = @count + 1 END Insert into #tmpOstMiesDokId (DokId) Select D.DokID From Dok D Where D.Data >= cast(@poprzRok as varchar(4)) + '-' + CAST(@poprz6mies as varchar(2)) + '-01' and D.Data < GETDATE() and D.TypDok in (2,20,31) order by Data desc set @count = 1 select @rowNumMax = MAX(rownum) from #tmpOstMiesDokId as int; while (@count <= @rownumMax) BEGIN insert into #tmpDelta6M SELECT PD.DokId, PD.TowId, SUM(PD.IloscPlus) as Ilosc, PD.CenaDomyslna, PD.Netto, PD.Podatek, sum(PD.Wartosc) as Wartosc FROM PozDok PD WHERE PD.DokId = (SELECT TOM.DokId FROM #tmpOstMiesDokId TOM WHERE TOM.rownum = @count) GROUP BY PD.DokId, PD.CenaDomyslna, PD.Netto, PD.Podatek, PD.TowId order by PD.TowId SET @count = @count + 1 END Insert into #tmpDelta0 (TowId, Kod, Nazwa, StanMag, Minimum, JM, Ilosc, CenaDomyslna, Wartosc) SELECT PD.TowId, cast(T.Kod as varchar(20)), T.Nazwa, I.StanMag, SUM(I.StanMin) AS MinMagazyn, JM.Nazwa, SUM(PD.IloscPlus) as IlSprzed, AVG(PD.CenaDomyslna) as CenaDomyslna, SUM(PD.Wartosc) as Wartosc FROM Dok D RIGHT JOIN PozDok PD ON PD.DokId = D.DokId LEFT JOIN Towar T ON PD.TowId = T.TowId LEFT JOIN Istw I ON T.TowId = I.TowId LEFT JOIN JM JM ON T.JMId = JM.JMId WHERE I.StanMag <= I.StanMin GROUP BY PD.TowId, T.Kod, T.Nazwa, I.StanMag, JM.Nazwa ORDER BY IlSprzed DESC SELECT TowId as TowId, SUM(ilosc) as SumIl, SUM(wartsc) as SumWar INTO #tmpD1M FROM #tmpDelta1M --where TowId = 1325 or TowId = 1273 or TowId = 2301 or TowId = 2532 group by TowId; SELECT TowId as TowId, SUM(ilosc) as SumIl, SUM(wartsc) as SumWar INTO #tmpD3M FROM #tmpDelta3M --where TowId = 1325 or TowId = 1273 or TowId = 2301 or TowId = 2532 group by TowId; SELECT TowId as TowId, SUM(ilosc) as SumIl, SUM(wartsc) as SumWar INTO #tmpD6M FROM #tmpDelta6M --where TowId = 1325 or TowId = 1273 or TowId = 2301 or TowId = 2532 group by TowId; SELECT TOP 10 TD0.TowId AS ID_TOWARU ,TD0.Nazwa AS NAZWA ,TD0.Kod AS KOD ,TD0.JM AS JM ,TD0.StanMag AS STAN_MAG ,TD0.Minimum AS IL_MIN ,TD0.CenaDomyslna AS CENA_DOMYSLNA ,(TD1M.SumIl) AS IL_DELTA_1M ,(TD3M.SumIl) AS IL_DELTA_3M ,(TD6M.SumIl) AS IL_DELTA_6M ,TD0.Ilosc AS IL_TOT ,(TD1M.SumWar) AS WART_DELTA_1M ,(TD3M.SumWar) AS WART_DELTA_3M ,(TD6M.SumWar) AS WART_DELTA_6M ,TD0.Wartosc AS WART_TOT FROM #tmpDelta0 TD0 LEFT JOIN #tmpD1M TD1M ON TD0.TowId = TD1M.TowId LEFT JOIN #tmpD3M TD3M ON TD0.TowId = TD3M.TowId LEFT JOIN #tmpD6M TD6M ON TD0.TowId = TD6M.TowId GROUP BY TD0.TowId, TD0.Nazwa, TD0.Kod, TD0.JM, TD0.StanMag, TD0.Minimum, TD0.CenaDomyslna, TD0.Ilosc, TD0.Wartosc, TD1M.SumIl, TD1M.SumWar, TD3M.SumIl, TD3M.SumWar, TD6M.SumIl, TD6M.SumWar order by IL_TOT desc"; $qry = mssql_query($sql, $link); if(!$qry) { } $row = mssql_fetch_row($qry);
Ostatni var_dump($row) zwraca null.
Skrypt łączy się z serwerem i odpytuje go ale nic nie zwraca. Dlaczego?
Czy zapytanie jest za długie??
czy można je ew 'na raty' rozbić?
Pozdrawiam i ziękuję za odpowiedzi.
Marcin