Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE development » Framework » RADICORE V 1.76 (SQLSRV DATABASE (MS SQLServer 2012))
RADICORE V 1.76 [message #4046] Thu, 16 January 2014 20:26 Go to next message
juanguilarte is currently offline  juanguilarte
Messages: 27
Registered: June 2012
Junior Member
I am using the framework with MYSQL and ORACLE database and the queries are very fast but when I do the same query using SQLSRV database it take tree or more time to make it, including using Menú System or Dictionary options from the Home page.


I will appreciate your comments or advices

Thank you an advance

Best regards

Juan Guilarte
Re: RADICORE V 1.76 [message #4049 is a reply to message #4046] Sat, 18 January 2014 06:28 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2276
Registered: April 2006
Location: Surrey, UK
Senior Member
I am running Radicore version 1.81.0 on my humble little laptop, and I am also running the following database servers:
- MySQL version 5.6.15
- PostgreSQL version 9.2.5
- Oracle version 11g Express Edition Release 11.2.0.2.0
- SQL Server version 2008 10.00.5500

I can switch between any of these database engines without noticing any difference in speed, so I would suspect that something is wrong with your SQL Server installation.

What version of PHP are you using? What version of SQL Server?

If you try running queries through SQL Server Management Studio are they slow as well?


Re: RADICORE V 1.76 [message #4052 is a reply to message #4049] Mon, 20 January 2014 22:38 Go to previous messageGo to next message
juanguilarte is currently offline  juanguilarte
Messages: 27
Registered: June 2012
Junior Member
Thank you for your comments

I am moving to the versionb 1.81 but the module dml.sqlsrv.class give me some errors. The last one I am sending to you


This application has encountered an unrecoverable error

The following has been reported to the administrator:


2014-01-20 22:49:48

Fatal Error: [Microsoft][SQL Server Native Client 11.0][SQL Server]La columna 'task_desc' se ha especificado varias veces para 'x'.
[Microsoft][SQL Server Native Client 11.0][SQL Server]El cursor no se declaró. (# 8156)

SQL query: SELECT * FROM ( SELECT x.task_id, x.task_desc, x.script_id, x.pattern_id, mnu_nav_button.button_text, mnu_nav_button.context_preselect, CASE WHEN (SELECT count(ip_address) FROM mnu_task_ip_address WHERE mnu_task_ip_address.task_id=mnu_nav_button.task_id_jnr) = 0 THEN 1 WHEN (SELECT count(ip_address) FROM mnu_task_ip_address WHERE mnu_task_ip_address.task_id=mnu_nav_button.task_id_jnr AND ip_address='127.0.0.1') = 1 THEN 1 ELSE 0 END AS ip_address_valid, COALESCE((SELECT task_desc FROM mnu_task_alt WHERE mnu_task_alt.task_id=mnu_nav_button.task_id_jnr AND mnu_task_alt.language_id='es-ve'), x.task_desc) AS task_desc, mnu_nav_button.sort_seq FROM mnu_nav_button LEFT JOIN mnu_task ON (mnu_task.task_id = mnu_nav_button.task_id_snr) LEFT JOIN mnu_task AS x ON (x.task_id = mnu_nav_button.task_id_jnr) WHERE x.is_disabled='N' AND mnu_task.task_id='menu' ) AS x WHERE ip_address_valid=1 ORDER BY sort_seq asc

Error in line 816 of file 'C:\wamp\www\radicore_v181\includes\dml.sqlsrv.class.inc'.

Client Info: DriverDllName: sqlncli11.dll, DriverODBCVer: 03.80, DriverVer: 11.00.2100, ExtensionVer: 3.0.3421.0
Server Info: CurrentDatabase: audit, SQLServerVersion: 11.00.2100, SQLServerName: JUANGUILARTE-HP\SQLEXPRESS

PHP_SELF: /radicore_v181/menu/menu.php

CURRENT DIRECTORY: C:\wamp\www\radicore_v181\menu

SERVER_ADDR: 127.0.0.1

SERVER_NAME: localhost

HTTP_HOST: localhost

User Id: MGR

Role Id: GLOBAL

REMOTE_ADDR: 127.0.0.1

REQUEST_URI: /radicore_v181/menu/menu.php?selection=main_menu&session _name=menu0


I am using MS SQL Server 2012

Thank you an advance

Regards

Juan Guilarte

Re: RADICORE V 1.76 [message #4053 is a reply to message #4052] Tue, 21 January 2014 04:21 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2276
Registered: April 2006
Location: Surrey, UK
Senior Member
It would appear that, unlike MySQL and PostgreSQL, SQL Server does not like the same column name appearing more than once in the SELECT clause.

I've amended two scripts (see attached zip file) and tested the fix on my laptop. Try this out on your machine and see if it works for you.


Re: RADICORE V 1.76 [message #4054 is a reply to message #4053] Tue, 21 January 2014 20:48 Go to previous messageGo to next message
juanguilarte is currently offline  juanguilarte
Messages: 27
Registered: June 2012
Junior Member

Thank you for the scripts.

I am receiving this message when I am using the dictionary options for update the field´s table.

2014-01-21 21:07:46

Fatal Error: [Microsoft][SQL Server Native Client 11.0][SQL Server]El nombre de objeto 'menu.mnu_task_alt' no es válido.
[Microsoft][SQL Server Native Client 11.0][SQL Server]El cursor no se declaró. (# 208)

SQL query: SELECT dict_column.column_id, dict_column.column_desc, dict_table.table_desc, COALESCE((SELECT task_desc FROM "menu".mnu_task_alt WHERE mnu_task_alt.task_id=mnu_task.task_id AND mnu_task_alt.language_id='es-ve') , mnu_task.task_desc) AS task_desc FROM dict_column LEFT JOIN dict_table ON (dict_table.database_id=dict_column.database_id AND dict_table.table_id=dict_column.table_id) LEFT JOIN "menu".dbo.mnu_task ON (mnu_task.task_id=dict_column.task_id) WHERE dict_column.database_id='sistema_pagos' AND dict_column.table_id='reg_act_fin_th' ORDER BY dict_column.column_seq asc

Error in line 816 of file 'C:\wamp\www\radicore_v181\includes\dml.sqlsrv.class.inc'.

Client Info: DriverDllName: sqlncli11.dll, DriverODBCVer: 03.80, DriverVer: 11.00.2100, ExtensionVer: 3.0.3421.0
Server Info: CurrentDatabase: audit, SQLServerVersion: 11.00.2100, SQLServerName: JUANGUILARTE-HP\SQLEXPRESS

PHP_SELF: /radicore_v181/dict/table(list2).php

CURRENT DIRECTORY: C:\wamp\www\radicore_v181\dict

SERVER_ADDR: 127.0.0.1

SERVER_NAME: localhost

HTTP_HOST: localhost

User Id: MGR

Role Id: GLOBAL

REMOTE_ADDR: 127.0.0.1

REQUEST_URI: /radicore_v181/dict/table(list2).php?session_name=menu2

Regard

Juan Guilarte
Re: RADICORE V 1.76 [message #4055 is a reply to message #4054] Wed, 22 January 2014 03:53 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2276
Registered: April 2006
Location: Surrey, UK
Senior Member
Try the attached fix.

Re: RADICORE V 1.76 [message #4060 is a reply to message #4055] Fri, 07 February 2014 06:39 Go to previous messageGo to next message
juanguilarte is currently offline  juanguilarte
Messages: 27
Registered: June 2012
Junior Member
My database is too big and I want to limit the maximum rows retrieve (for example 1000) from any requirements (list, search) but can´t find the way to do that.

I will appreciate your comments or advices

Thank you an advance


Best regards

Juan Guilarte


Re: RADICORE V 1.76 [message #4061 is a reply to message #4060] Fri, 07 February 2014 06:50 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2276
Registered: April 2006
Location: Surrey, UK
Senior Member
By default every LIST screen will only show 10 records within each page, with pagination controls to help you step from one page to another. The default of 10 rows per page can be changed to either 25, 50 or 100, as described in Navigation Bar.

Re: RADICORE V 1.76 [message #4062 is a reply to message #4061] Fri, 07 February 2014 08:49 Go to previous messageGo to next message
juanguilarte is currently offline  juanguilarte
Messages: 27
Registered: June 2012
Junior Member
yes, but "list" pattern retrieve all the records from the database and i want to know if i can limit the rows read,for example if the table has 1.000.000 rows "list" show the page from 1 to 100.000 if we use the default 10. I want to retrieve in this example at most 100 page.

I will appreciate your comments or advices

Thank you an advance


Best regards

Juan Guilarte

Re: RADICORE V 1.76 [message #4063 is a reply to message #4061] Fri, 07 February 2014 08:50 Go to previous messageGo to next message
juanguilarte is currently offline  juanguilarte
Messages: 27
Registered: June 2012
Junior Member
yes, but "list" pattern retrieve all the records from the database and i want to know if i can limit the rows read,for example if the table has 1.000.000 rows "list" show the page from 1 to 100.000 if we use the default 10. I want to retrieve in this example at most 100 page.

I will appreciate your comments or advices

Thank you an advance


Best regards

Juan Guilarte

Re: RADICORE V 1.76 [message #4064 is a reply to message #4063] Fri, 07 February 2014 09:01 Go to previous message
AJM is currently offline  AJM
Messages: 2276
Registered: April 2006
Location: Surrey, UK
Senior Member
The LIST pattern does not retrieve *ALL* the records from the database - it only retrieves one page at a time. The page size can be set to 10, 25, 50 or 100 rows. Although the screen shows the number of rows which are available, this value is obtained using the COUNT() function and not by reading all the rows individually.

Previous Topic: Issues with Radicore V 1.75
Next Topic: PROBLEM WITH DECIMAL POINT
Goto Forum:
  


Current Time: Thu Dec 09 08:54:42 EST 2021

Total time taken to generate the page: 0.01327 seconds