Home News Contact Us Forum About Us Demos Products F.A.Q.
Shopping Cart
You currently have 0 items in your cart.


Recent Events
  • 31/12/2023 New Year SALE

    We are glad to announce New Year SALE. 25% discount for all our extensions. Use NY24 coupon code. Hurry up the discount is valid till 7 January.

  • 21/11/2023 BLACK FRIDAY 23 is coming

    BIG SALE, 35% discount for all our extensions. Use BF23 coupon code. Hurry up the discount is valid till 27 November.


2Checkout.com, Inc. is an authorized retailer of goods and services provided by ARI Soft. 2CheckOut




Follow us on twitter



Welcome, Guest
Please Login or Register.    Lost Password?

MS SQL Query fails - 'NAMES' is not a recognized
(1 viewing) (1) Guest
Go to bottomPage: 12
TOPIC: MS SQL Query fails - 'NAMES' is not a recognized
#50814
MS SQL Query fails - 'NAMES' is not a recognized 9 Years, 7 Months ago Karma: 0
Issue: invalid characters is displayed. So ISO-8859-1 is entered into Charset Name - workes fine for CSV files but not for MS SQL / ODBC

'NAMES' is not a recognized SET option.

listing data into Ari Data Table and using ODBC against MS SQL 2008 (Only way it connects).

Debug dialog:
(odbc): SET NAMES 'ISO-8859-1' (Comment I set Charset Name to ISO-8859-1 - Module generates Invalid SELECT statement )
Error (37000): [Microsoft][ODBC SQL Server Driver][SQL Server]'NAMES' is not a recognized SET option.

(odbc): SELECT [Product_Categorization_Tier_1],[Product_Categorization_Tier_2],[Product_Categorization_Tier_3] ,dbo.HPD_Help_Desk.Incident_Number FROM [ARSystem].[dbo].[HPD_Help_Desk] WHERE Categorization_Tier_1 = 'Change'
Error (37000): [Microsoft][ODBC SQL Server Driver][SQL Server]'NAMES' is not a recognized SET option.
No data available.

My query: SELECT [Product_Categorization_Tier_1],[Product_Categorization_Tier_2],[Product_Categorization_Tier_3]
,dbo.HPD_Help_Desk.Incident_Number
FROM [ARSystem].[dbo].[HPD_Help_Desk]

WHERE Categorization_Tier_1 = 'Change'

What to do?

Sincerely - Johnny Ejs
The administrator has disabled public write access.
 
#50824
Re:MS SQL Query fails - 'NAMES' is not a recognized 9 Years, 7 Months ago Karma: 746
Hello,

"SQL -> Chartset name" parameter is used only for MySQL database, it is not supported by MS SQL.

Could you provide a link to a page where we can see the problem and a temporary access to your J! backend by email so we can investigate it?

Regards,
ARI Soft
The administrator has disabled public write access.
 
#50847
Re:MS SQL Query fails - 'NAMES' is not a recognized 9 Years, 7 Months ago Karma: 0
Thank You for response, unfortunately my project is behind corporate firewall and I am not able to give You access.
I did some thinking though.
Same setup works fine for me when I use CSV data (same data as my SQL) input and the CSV module. (Picture 000.png)

In the CSV module i set encoding to ISO-8859-1 and the file "class.DataTables Data Model.php" 'filters' TABLE output according to that. And the Danish æøå ÆØÅ letters are displayed correct.
In the Case of SQL it seems that the file "class.DataTables Data Sql Model.php" is trying to instruct the SQL to deliver the data in the correct format and this makes no sense since the data has the relevant correlation and the site also, also the SET NAMES instruction does not work for MS SQL.
The issue here is the same as in the case of CSV file based data - instruct the module to transform the table output to the appropriate data correlation.

Does this make sense?
Last Edit: 2014/08/18 18:06 By johnny@ejs.dk.
The administrator has disabled public write access.
 
#50854
Re:MS SQL Query fails - 'NAMES' is not a recognized 9 Years, 7 Months ago Karma: 746
"SQL -> Chartset" parameter is working only for MySQL database. It suppose "SET NAME" command, MS SQL doesn't similar command.

Regards,
ARI Soft
The administrator has disabled public write access.
 
#50878
Re:MS SQL Query fails - 'NAMES' is not a recognized 9 Years, 7 Months ago Karma: 0
I think that we are not on the same page here, and I admit my Subject is misleading!

My problem is, that ARI Data Table only displays SQL data in UTF-8. In the case of CSV based table that module does accept ISO-8859-1 as a setting and displays the data correct.

Anyway we have modified the file "class.DataTablesDataSqlModel.php" and the Table output is now showing correct national characters.

Sincerely
Johnny Ejs
The administrator has disabled public write access.
 
#52425
Re:MS SQL Query fails - 'NAMES' is not a recognized 9 Years, 3 Months ago Karma: 0
Just a follow up on this issue.

If i use CSV files Your module then accepts Charset Name: ISO-8859-1 setting in the module and displays data correct in the tables.

But I am using table data from a MS SQL 2008 server in Danish correlation and in the ARI Data Table the national Danish characters "æøåÆØÅ" does not show correct.

My own solution is to add two functions into the file: modules\mod_aridatatables\includes\kernel\DataTables\Models\class.DataTablesDataSqlModel.php

<?php
/*
* ARI Data Tables
*
* @package ARI Data Tables
* @version 1.0.0
* @author ARI Soft
* @copyright Copyright (c) 2010 www.ari-soft.com. All rights reserved
* @license GNU/GPL (www.gnu.org/copyleft/gpl.html)
*
*/

function check_utf8($string) {
return preg_match('%^(?:
[\x09\x0A\x0D\x20-\x7E] # ASCII
| [\xC2-\xDF][\x80-\xBF] # non-overlong 2-byte
| \xE0[\xA0-\xBF][\x80-\xBF] # excluding overlongs
| [\xE1-\xEC\xEE\xEF][\x80-\xBF]{2} # straight 3-byte
| \xED[\x80-\x9F][\x80-\xBF] # excluding surrogates
| \xF0[\x90-\xBF][\x80-\xBF]{2} # planes 1-3
| [\xF1-\xF3][\x80-\xBF]{3} # planes 4-15
| \xF4[\x80-\x8F][\x80-\xBF]{2} # plane 16
)*$%xs', $string);
}

function encode_items(&$item, $key)
{
if(!check_utf8($item)) {
$item = utf8_encode($item);
}
}

defined('ARI_FRAMEWORK_LOADED') or die('Direct Access to this location is not allowed.');

__________________________
. . . .
__________________________


global $ADODB_FETCH_MODE;

$query = $this->prepareQuery($params['query']);
$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
$data = $conn->GetArray($query);
array_walk_recursive($data, 'encode_items');

if ($conn->ErrorNo())
{
$data = null;
}

if ($conn && $conn->IsConnected())
$conn->Close();

return $data;
}

Above i call the function array_walk_recursive($data, 'encode_items'); and this works and is fine. I have to modify every time the module is updated and that is not a problem for me.

BUT

I guess many other people (Scandinavia / Germany e.t.c.) have similar correlation issues and I have ask that You consider the following:

1) Will this hack-fix make the module unstable or cause malfunction. I have not noticed problems - maybe one thing: Hidden Columns does not to work.
2) Maybe You take this to future enhancment and implement this using the module parameter "Charset Name"!

Sincerely Johnny Ejs
Last Edit: 2014/12/05 08:36 By johnny@ejs.dk.
The administrator has disabled public write access.
 
Go to topPage: 12