2. PHPRunner General questions
  3. PHPR builds wrong MSSQL Query
This topic is locked

PHPR builds wrong MSSQL Query

 		4/25/2008 7:26:45 AM
PHPRunner General questions
rjks author

Hello Support,
I am using a complicated SQL String to build a View.
Here the Query which works in the edit sql query window and returns the correct result and order.
[codebox]SELECT [ACCOUNT] , [DESCR] , sum ([OP]) as [Gesamt OP] , sum ([<030]) as [<030] , sum ([030-060]) as [030-060] , sum ([060-090]) as [060-090] , sum ([090-120]) as [090-120] , sum ([120-150]) as [120-150] , sum ([150+]) as [150+] , sum ([090-120]) + sum ([120-150]) + sum ([150+]) as [>90] FROM [SUNDB_DEU_Internet].[dbo].[tbliDEB_OP_Basis] group by [ACCOUNT] , [DESCR] having sum(OP) != 0 order by 3[/codebox]
After building my project I get an SQL error,
[codebox]Technische Information

Fehlertyp 256

Fehlerbeschreibung Source: Microsoft OLE DB Provider for SQL Server

Description: Zeile 1: Falsche Syntax in der Nähe von ')'.

URL server01/procedure/Gesamt_list.php?

Fehlerdatei C:\Inetpub\wwwroot\webroot\isoft\procedure\include\dbconnection.mssql.win.php

Fehlerzeile 39

SQL Abfrage SELECT [ACCOUNT] , [DESCR] , sum ([OP]) as [Gesamt OP] , sum ([<030]) as [<030] , sum ([030-060]) as [030-060] , sum ([060-090]) as [060-090] , sum ([090-120]) as [090-120] , sum ([120-150]) as [120-150] , sum ([150+]) as [150+] , sum ([090-120]) + sum ([120-150]) + sum ([150+]) as [>90] FROM [SUNDB_DEU_Internet].[dbo].[tbliDEB_OP_Basis] group by [ACCOUNT] , [DESCR] having sum(OP) != 0 order by 3[/codebox]
The SQL Sting which is called in the db_query function is like this
[codebox]select count(*) from (SELECT [ACCOUNT] , [DESCR] , sum ([OP]) as [Gesamt OP] , sum ([<030]) as [<030] , sum ([030-060]) as [030-060] , sum ([060-090]) as [060-090] , sum ([090-120]) as [090-120] , sum ([120-150]) as [120-150] , sum ([150+]) as [150+] , sum ([090-120]) + sum ([120-150]) + sum ([150+]) as [>90] FROM [SUNDB_DEU_Internet].[dbo].[tbliDEB_OP_Basis] group by [ACCOUNT] , [DESCR] having sum(OP) != 0 )[/codebox]
Why??
How can I prevent this, the *select count() from** is added automatically.
Thanks in advance
Robert
rjks author 4/25/2008

Hello Jane,
the Problem with the incorrect SQL Query is really urgent, could you please have a look at it and give me some help.
If it is possible to build the Query with the Query Des. then please tell me how.
Your inbox is full so I coudn´t send a personal message.
Thanks in advance
Robert
J
 		Jane 4/25/2008

Robert,
it's difficult to tell you what's happening without seeing actual files.
Please publish your project on Demo Account and send to support@xlinesoft.com a URL to your pages along with instructions on reproducing this error.

I'll find what's wrong with your project inspecting it at Demo account site.
rjks author 4/25/2008

Hello Jane,
I´ve uploaded the projekt to my demo site, still waiting for my verification mail though.
The problem seems to be that my custom sql is not recognised correctly and a "select count (*) from " is added before execution.
Robert
rjks author 4/25/2008

Hello again,
I have posted the projekt to the demo site, link and access in mail on support.
The demo site is a great tool, ran straight away without a hitch.
Robert
rjks author 4/25/2008

<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=28571&image=1&table=forumreplies' class='bbc_emoticon' alt=':D' /> <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=28571&image=2&table=forumreplies' class='bbc_emoticon' alt=':D' />
Hello all,
this Problem has now been solved, it was an error in the common file, here the correction which will be implemented in the next update.
-- Reply from Jane

To make your pages working open C:/Program Files/PHPRunner4.2/source/include/commonfunctions.php file, locate gSQLRowCount function, find this code:

--------------------------------------------------------------------------

if @BUILDER.nDatabaseType!=DATABASE_MySQL##

$countstr = "select count(*) from (".gSQLWhere($where).")";

else##

--------------------------------------------------------------------------

and replace it with this one:

--------------------------------------------------------------------------

if @BUILDER.nDatabaseType!=DATABASE_MySQL##

$countstr = "select count(*) from (".gSQLWhere($where).") a";

else##

--------------------------------------------------------------------------
Also PHPRunner doesn't support TOP in the SQL query.

Remove TOP 100 percent from your SQL query and rebuild your pages.
-- end of Reply
Also a problem with ajax was found which will also be repaired in the next update.
Thanks for the great support, I finished the first version of my Projekt, just in time.
Robert