Thursday, June 15, 2017

every one access tomcat web directory add authentication

i want to add xxx web directory in tomcat with specify user and password access.

webapps/xxx/WEB-INF/web.xml:
  <security-constraint>
    <web-resource-collection>
      <web-resource-name>HTML Manager interface (for humans)</web-resource-name>
      <url-pattern>/*</url-pattern>
    </web-resource-collection>
    <auth-constraint>
       <role-name>manager-my</role-name>
    </auth-constraint>
  </security-constraint> 
   <login-config>
    <auth-method>BASIC</auth-method>
    <realm-name>Tomcat Manager Application</realm-name>
  </login-config>

conf/tomcat-users.xml:
  
<role rolename="manager-my">
  <user password="hx98.com.cn" roles="manager-my" username="hx98">
</user></role>

restart tomcat . ok!

Saturday, June 3, 2017

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 3

below is my mysql(MariaDB) server stored procedure code


# call `pkrss_get_rssitem_vo`(2, 0, 0, 0, 1, 0, 0, 0);
#   flag = 1: select items
#   flag =-1: select items count
#   flag = 2: select items with image mode
#   flag =-2: select items with image mode count
#   flag = 3: get top 1 item with each table
#   flag = 4: get top n item with each table
CREATE PROCEDURE `pkrss_get_rssitem_vo`(IN `lid` INT, IN `cid` INT, IN `tid` INT, IN `offset` INT, IN `limit` INT, IN `idOrderBy` INT, IN `timeOrderBy` INT, IN `flag` INT)
BEGIN
    DECLARE s VARCHAR(512);
    DECLARE s2 VARCHAR(512);
    DECLARE t VARCHAR(128);
    
    DECLARE selImgMode INT DEFAULT 0;
    DECLARE selCountMode INT DEFAULT 0;
    
    IF flag = -1 THEN
     SET selCountMode = 1;
    END IF;
    
    IF flag = -2 THEN
     SET selCountMode = 1;
     SET selImgMode = 1;
 END IF;
    IF flag = 2 THEN
     SET selImgMode = 1;
 END IF;
 
 SET s = '';
        
    IF selCountMode = 1 THEN
     SET s = CONCAT(s,'SELECT COUNT(*) FROM ');
     
     SET s = CONCAT(s,'pkrss_rssitem_l', lid, ' AS a WHERE 1=1');
     
     IF tid != 0 THEN
         SET s = CONCAT(s,' AND a.tid=', tid);
     END IF;
 
     IF cid != 0 THEN
         SET s = CONCAT(s,' AND a.cid=', cid);
     END IF;
     
     IF selImgMode = 1 THEN
         SET s = CONCAT(s,' AND a.item_imgs is not null');
     END IF;
     
     SET @t2 = s;
     PREPARE stmt FROM @t2;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
     
 ELSE
    
  SET s = CONCAT(s,'SELECT a.*');
  
  IF tid = 0 THEN
         SET s = CONCAT(s,',t.title AS table_name');
     END IF;
 
     IF cid = 0 THEN
         SET s = CONCAT(s,',c.text AS catalog_name,c.`cls` AS catalog_cls');
     END IF;
 
  SET s = CONCAT(s,' FROM pkrss_rssitem_l', lid, ' AS a');
  
     IF tid = 0 THEN
         SET s = CONCAT(s,' LEFT JOIN pkrss_rsstable AS t ON t.id = a.tid');
     END IF;
 
     IF cid = 0 THEN
         SET s = CONCAT(s,' LEFT JOIN pkrss_rsscatalog AS c ON c.id = a.cid');
     END IF;
 
     SET s = CONCAT(s,' WHERE 1=1');
     
     IF selImgMode = 1 THEN
      SET s = CONCAT(s,' AND a.item_imgs is not null');
     END IF;
 
     IF tid != 0 THEN
         SET s = CONCAT(s,' AND a.tid=', tid);
     END IF;
 
     IF cid != 0 THEN
         SET s = CONCAT(s,' AND a.cid=', cid);
     END IF;
     
     IF flag = 3 THEN
      # get top 1 item with each table
      SET s = CONCAT(s,' AND a.id IN (SELECT MAX(id) FROM pkrss_rssitem_l', lid, ' GROUP BY tid)');
      # select * from pkrss_rssitem_l2 where id in (select max(id) from pkrss_rssitem_l2 group by tid) order by tid,id desc limit 20;
     ELSEIF flag = 4 THEN
   # get top 3 records with each table   
   SET s = CONCAT('SET @num := 0, @tid := 0;', s);
   SET s = CONCAT(s,' AND a.id IN (SELECT id FROM (SELECT id,@num := if(@tid=tid, @num + 1, 1) as row_number,@tid := tid as other_tid');
   SET s = CONCAT(s,' FROM pkrss_rssitem_l', lid, ' ORDER BY tid,id DESC) WHERE row_number<=3)');
   # set @num := 0, @tid := 0;
   # select b.* from (
   #  select a.*, 
   #  @num := if(@tid=tid, @num + 1, 1) as row_number, 
   #  @tid := a.tid as other_tid 
   #  from pkrss_rssitem_l2 as a 
   #  order by a.tid,a.id desc
   # ) as b 
   # where b.row_number <= 3 order by b.tid,b.id desc limit 20;
     END IF;
 
     SET s = CONCAT(s,' ORDER BY');
 
     IF idOrderBy != 0 THEN
         IF idOrderBy > 0 THEN
             SET s = CONCAT(s,' a.id ASC');
         ELSE
             SET s = CONCAT(s,' a.id DESC');
         END IF;
     ELSE
      IF timeOrderBy > 0 THEN
           SET s = CONCAT(s,' a.item_pubdate ASC');
         ELSE
           SET s = CONCAT(s,' a.item_pubdate DESC');
         END IF;
     END IF;
 
     SET s = CONCAT(s,' LIMIT ', offset, ',', `limit`);
          
     SET t = CONCAT('_tmp_',UUID());
     SET @t2 = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS `', t,'` (`id` int(11),
    `tid` int(11),
    `cid` int(11),
    `item_pubdate` datetime,
    `item_title` varchar(127) COLLATE utf8_bin,
    `item_desc` varchar(246) COLLATE utf8_bin,
    `item_link` varchar(246) COLLATE ascii_bin,
    `item_authors` varchar(64) COLLATE utf8_bin,
    `item_imgs` text COLLATE utf8_bin,
    `table_name` varchar(127) COLLATE utf8_bin,
    `catalog_name` varchar(64) COLLATE utf8_bin,
    `catalog_cls` varchar(32) COLLATE utf8_bin
    );');
    
     PREPARE stmt FROM @t2;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
     
     SET s2 = CONCAT('INSERT INTO `', t, '` (`id`,`tid`,`cid`,`item_pubdate`,`item_title`,`item_desc`, `item_link`,`item_authors`,`item_imgs`');
     IF tid = 0 THEN
         SET s2 = CONCAT(s2,',`table_name`');
     END IF;
 
     IF cid = 0 THEN
         SET s2 = CONCAT(s2,',catalog_name,catalog_cls');
     END IF;
     
     SET @t2 = CONCAT(s2,') ', s, ';');
     
  PREPARE stmt FROM @t2;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
     
     set @t2 = CONCAT('SELECT * FROM `',t,'`');
     PREPARE stmt FROM @t2;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
    END IF;
   
END;;
DELIMITER ;

And mysql return below error message

CREATE PROCEDURE `pkrss_get_rssitem_vo`(IN `lid` INT, IN `cid` INT, IN `tid` INT, IN `offset` INT, IN `limit` INT, IN `idOrderBy` INT, IN `timeOrderBy` INT, IN `flag` INT)
BEGIN
    DECLARE s VARCHAR(512)
MySQL 返回: 文档

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 3

How to fixed

add "DELIMITER ;;" to front and end rows:

DELIMITER ;;
CREATE PROCEDURE `pkrss_get_rssitem_vo`(IN `lid` INT, IN `cid` INT, IN `tid` INT, IN `offset` INT, IN `limit` INT, IN `idOrderBy` INT, IN `timeOrderBy` INT, IN `flag` INT)
BEGIN
    DECLARE s VARCHAR(512);
    DECLARE s2 VARCHAR(512);
    DECLARE t VARCHAR(128);
    
    DECLARE selImgMode INT DEFAULT 0;
    DECLARE selCountMode INT DEFAULT 0;
    
    IF flag = -1 THEN
     SET selCountMode = 1;
    END IF;
    
    IF flag = -2 THEN
     SET selCountMode = 1;
     SET selImgMode = 1;
 END IF;
    IF flag = 2 THEN
     SET selImgMode = 1;
 END IF;
 
 SET s = '';
        
    IF selCountMode = 1 THEN
     SET s = CONCAT(s,'SELECT COUNT(*) FROM ');
     
     SET s = CONCAT(s,'pkrss_rssitem_l', lid, ' AS a WHERE 1=1');
     
     IF tid != 0 THEN
         SET s = CONCAT(s,' AND a.tid=', tid);
     END IF;
 
     IF cid != 0 THEN
         SET s = CONCAT(s,' AND a.cid=', cid);
     END IF;
     
     IF selImgMode = 1 THEN
         SET s = CONCAT(s,' AND a.item_imgs is not null');
     END IF;
     
     SET @t2 = s;
     PREPARE stmt FROM @t2;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
     
 ELSE
    
  SET s = CONCAT(s,'SELECT a.*');
  
  IF tid = 0 THEN
         SET s = CONCAT(s,',t.title AS table_name');
     END IF;
 
     IF cid = 0 THEN
         SET s = CONCAT(s,',c.text AS catalog_name,c.`cls` AS catalog_cls');
     END IF;
 
  SET s = CONCAT(s,' FROM pkrss_rssitem_l', lid, ' AS a');
  
     IF tid = 0 THEN
         SET s = CONCAT(s,' LEFT JOIN pkrss_rsstable AS t ON t.id = a.tid');
     END IF;
 
     IF cid = 0 THEN
         SET s = CONCAT(s,' LEFT JOIN pkrss_rsscatalog AS c ON c.id = a.cid');
     END IF;
 
     SET s = CONCAT(s,' WHERE 1=1');
     
     IF selImgMode = 1 THEN
      SET s = CONCAT(s,' AND a.item_imgs is not null');
     END IF;
 
     IF tid != 0 THEN
         SET s = CONCAT(s,' AND a.tid=', tid);
     END IF;
 
     IF cid != 0 THEN
         SET s = CONCAT(s,' AND a.cid=', cid);
     END IF;
     
     IF flag = 3 THEN
      # get top 1 item with each table
      SET s = CONCAT(s,' AND a.id IN (SELECT MAX(id) FROM pkrss_rssitem_l', lid, ' GROUP BY tid)');
      # select * from pkrss_rssitem_l2 where id in (select max(id) from pkrss_rssitem_l2 group by tid) order by tid,id desc limit 20;
     ELSEIF flag = 4 THEN
   # get top 3 records with each table   
   SET s = CONCAT('SET @num := 0, @tid := 0;', s);
   SET s = CONCAT(s,' AND a.id IN (SELECT id FROM (SELECT id,@num := if(@tid=tid, @num + 1, 1) as row_number,@tid := tid as other_tid');
   SET s = CONCAT(s,' FROM pkrss_rssitem_l', lid, ' ORDER BY tid,id DESC) WHERE row_number<=3)');
   # set @num := 0, @tid := 0;
   # select b.* from (
   #  select a.*, 
   #  @num := if(@tid=tid, @num + 1, 1) as row_number, 
   #  @tid := a.tid as other_tid 
   #  from pkrss_rssitem_l2 as a 
   #  order by a.tid,a.id desc
   # ) as b 
   # where b.row_number <= 3 order by b.tid,b.id desc limit 20;
     END IF;
 
     SET s = CONCAT(s,' ORDER BY');
 
     IF idOrderBy != 0 THEN
         IF idOrderBy > 0 THEN
             SET s = CONCAT(s,' a.id ASC');
         ELSE
             SET s = CONCAT(s,' a.id DESC');
         END IF;
     ELSE
      IF timeOrderBy > 0 THEN
           SET s = CONCAT(s,' a.item_pubdate ASC');
         ELSE
           SET s = CONCAT(s,' a.item_pubdate DESC');
         END IF;
     END IF;
 
     SET s = CONCAT(s,' LIMIT ', offset, ',', `limit`);
          
     SET t = CONCAT('_tmp_',UUID());
     SET @t2 = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS `', t,'` (`id` int(11),
    `tid` int(11),
    `cid` int(11),
    `item_pubdate` datetime,
    `item_title` varchar(127) COLLATE utf8_bin,
    `item_desc` varchar(246) COLLATE utf8_bin,
    `item_link` varchar(246) COLLATE ascii_bin,
    `item_authors` varchar(64) COLLATE utf8_bin,
    `item_imgs` text COLLATE utf8_bin,
    `table_name` varchar(127) COLLATE utf8_bin,
    `catalog_name` varchar(64) COLLATE utf8_bin,
    `catalog_cls` varchar(32) COLLATE utf8_bin
    );');
    
     PREPARE stmt FROM @t2;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
     
     SET s2 = CONCAT('INSERT INTO `', t, '` (`id`,`tid`,`cid`,`item_pubdate`,`item_title`,`item_desc`, `item_link`,`item_authors`,`item_imgs`');
     IF tid = 0 THEN
         SET s2 = CONCAT(s2,',`table_name`');
     END IF;
 
     IF cid = 0 THEN
         SET s2 = CONCAT(s2,',catalog_name,catalog_cls');
     END IF;
     
     SET @t2 = CONCAT(s2,') ', s, ';');
     
  PREPARE stmt FROM @t2;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
     
     set @t2 = CONCAT('SELECT * FROM `',t,'`');
     PREPARE stmt FROM @t2;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;
    END IF;
   
END;;
DELIMITER ;






Friday, June 2, 2017

中国大陆交易所2017年最新行情数据Level1费用与证券财经数据服务咨询汇总

背景

现在我在福建海峡互联网金融信息服务有限公司,老板考虑企业转型,才被逼着考虑买上深行情,但是因为钱的问题,不知道会不会买。他最近一直让我问上、深所的数据报价。以前像我们公司这种做现货的小金融公司太多,国家查得严,最近倒了很多,我们老板不想倒,才考虑做合规产品。 这样没有后顾之忧,以后要上市要融资了,这些都会查的。

下面是个人最近2017年上半年咨询行情报价的情况汇总,仅供参考,不做为确切真实依据,如果有侵权,请告知,第一时间删除。

汇总情况

于上深交所Level1服务费用包括:数据提示牌照费用+数据传输费用。
      如我们现在急着只需要微信和移动的Level1单平台无线增值平台证券行情费用,预计需要上交所牌照费用30/+上交所Internet数据传输免费+深交所牌照费用30/+深圳通云数据传输费用(8000/+8/年)=68.8/年(预计)。其中正式购买后才可以申请最长不超过2个月的测试期。
      另外关于目前炒股软件里的财经F10新闻资讯巨潮资讯由深交所信息公司提供,价格见附件《巨潮数据库服务报价单2016.pdf》,因为隐私问题,如需商务使用,可私人联系我索要
    

Level1数据提示牌照授权费

深交所牌照费用: PC电脑终端30/年,无线终端(微信+APP30/年,或者同时采购两款平台价格打8=30+30*0.8=48/年。
上交所牌照费用: 单平台终端报价与深交所类似,但两款平台同时采购价约45/年。
注意:深交所有要求,一次性申请两个应用可以打八折,如果你们 分开申请,比如先申请无线, 过半年后再申请软件,是不能打折的。以后年度就是续约了。。。按照合同来续约的。
        上交所暂未确认此打折疑问。

Level1数据汇总费用 

采购一款深Level1平台价格=1平台数据提示牌照授权费+数据传输费=30/+15/=45/
采购两款深Level1平台价格=2平台数据提示牌照授权费+数据传输费=48/+15/=63/
数据传输方面单独申请接入,也需要先采购申请提示牌照授权。
Level1据传输费多选1a.因系统升级要求,现有旧协议暂弃(费用好像也是免费),新协议预计2017年第4季度预计推出(可能免费); b.基于api接口供客户端直接调用形式预计2018年初上线,可以先填申请表;c.与深圳通合作,提供专线与云行情两种形式预计省外均为15/年(未确认);d.与第三方公司合作,可能像我们之前联系的汇通网,汇通网之前报过行情数据10/;
深圳通在公司取得行情授权后提供上证与深证行情数据有偿数据传输服务,报价分两种:a.云行情,提供低档云服务器租售5000/年,高档云服务器租售8000/年,提供互联网数据传输带宽租售25M带宽8/年;b.提供专线6M带宽10几万/条,深Level1行情数据预计需拉2条;

采购一款上Level1平台价格=1平台数据提示牌照授权费+数据传输费=30/+数据传输费
采购两款上Level1平台价格=2平台数据提示牌照授权费+数据传输费=45/+数据传输费
Level1数据传输费31a.卫星5; b.专线免费; c.Internet传输免费。

深平台界限范围

网页浏览、查询: PC电脑网页
软件应用揭示: 传统WindowsLinuxMac桌面终端软件应用
无线增值类:IOSAndroidWindows Mobile手机app软件应用及微信H5网页
3款平台一起订深交所牌照费用也是打8

汇总回答2

== 上证所L1行情 申请要求 ==
我们公司应该可以申请,无特殊要求(但是上一次说需要上一年收入材料,如年检)。

== 上证所L2行情 费用 ==
35万许可费+按每终端授权使用费用*终端用户数+3条专线费用。
按每终端授权使用费用计算方法:PC电脑30元/月/台,月保底7.2万,超出另计;手机15元/月/终端,月保底5万,超出另计。
3条专线费用:以深交所为例20万/年*3条,上交所费用稍低。

== 上证所L2行情 申请要求 ==
需要至少签过2、3年上证所L1行情数据后,且需要获取证监会牌照后方可申请。

== 上证所L1、L2行情 区别 ==
L2数据的主要特点是能看到资金流向和十档买卖盘,比L1数据更加清晰和全面。具体可以在上交所网站上下载对应文档进行查看。

== 数据销售与使用 ==
不允许以原始数据进行销售与使用,需要对数据进行有所加工方可销售与使用。

== 之前的几千万费用是什么服务 ==
07年左右有上过通赢数据,后来就中止该服务。

 相关联系人

深交所Level1联系电话:因为隐私问题,如需商务使用,可私人联系我索要
深圳通联系信息: http://www.sscc.com/main/index.shtml
深圳通接入联系人:
        卫星、专线接入联系:因为隐私问题,如需商务使用,可私人联系我索要
        云行情接入联系:深证通,因为隐私问题,如需商务使用,可私人联系我索要 
上交所Level2联系电话:因为隐私问题,如需商务使用,可私人联系我索要

上交所Level1联系电话:因为隐私问题,如需商务使用,可私人联系我索要