sqlserver 调用qq map 服务,根据地点获取所在城市 首先需要解决的是如何在SQL SERVER中调用web service,其次是针对web service的返回json进行解析; 1、调用web service create proc getCityByPosition( @url varchar(2048) --Your Web Service Url (invoke
sqlserver 调用qq map 服务,根据地点获取所在城市
首先需要解决的是如何在SQL SERVER中调用web service,其次是针对web service的返回值json进行解析;
1、调用web service
create proc getCityByPosition( @url varchar(2048) --Your Web Service Url (invoked) ) as declare @Object as Int; declare @ResponseText as Varchar(8000); declare @resultStr varchar(2048) -- exec getCityByPosition 'http://apis.map.qq.com/ws/geocoder/v1/?location=34.287100,117.255000&key=K76BZ-W3O2Q-RFL5S-GXOPR-3ARIT-6KFE5&output=json&&callback=?' Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT; Exec sp_OAMethod @Object, 'open', NULL, 'get',@url,'false' Exec sp_OAMethod @Object, 'send' Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT Select top 1 StringValue from parseJSON(@ResponseText) where name = 'city'; Exec sp_OADestroy @Object
执行测试可能报错,提示更改全局配置,需管理员执行下面代码:
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO
2、解析json字符串,使用自定义函数parseJSON
USE [pm_v3] GO /****** Object: UserDefinedFunction [dbo].[parseJSON] Script Date: 01/02/2015 18:06:05 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[parseJSON]( @JSON NVARCHAR(MAX)) RETURNS @hierarchy TABLE ( element_id INT IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */ parent_ID INT,/* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */ Object_ID INT,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */ NAME VARCHAR(2000),/* the name of the object */ StringValue VARCHAR(MAX) NOT NULL,/*the string representation of the value of the element. */ ValueType VARCHAR(10) NOT null /* the declared type of the value represented as a string in StringValue*/ ) AS BEGIN DECLARE @FirstObject INT, --the index of the first open bracket found in the JSON string @OpenDelimiter INT,--the index of the next open bracket found in the JSON string @NextOpenDelimiter INT,--the index of subsequent open bracket found in the JSON string 【本文来自鸿网互联 (http://www.68idc.cn)】 @NextCloseDelimiter INT,--the index of subsequent close bracket found in the JSON string @Type NVARCHAR(10),--whether it denotes an object or an array @NextCloseDelimiterChar CHAR(1),--either a '}' or a ']' @Contents NVARCHAR(MAX), --the unparsed contents of the bracketed expression @Start INT, --index of the start of the token that you are parsing @end INT,--index of the end of the token that you are parsing @param INT,--the parameter at the end of the next Object/Array token @EndOfName INT,--the index of the start of the parameter at end of Object/Array token @token NVARCHAR(200),--either a string or object @value NVARCHAR(MAX), -- the value as a string @name NVARCHAR(200), --the name as a string @parent_ID INT,--the next parent ID to allocate @lenJSON INT,--the current length of the JSON String @characters NCHAR(36),--used to convert hex to decimal @result BIGINT,--the value of the hex symbol being parsed @index SMALLINT,--used for parsing the hex value @Escape INT --the index of the next escape character DECLARE @Strings TABLE /* in this temporary table we keep all strings, even the names of the elements, since they are 'escaped' in a different way, and may contain, unescaped, brackets denoting objects or lists. These are replaced in the JSON string by tokens representing the string */ ( String_ID INT IDENTITY(1, 1), StringValue NVARCHAR(MAX) ) SELECT--initialise the characters to convert hex to ascii @characters='0123456789abcdefghijklmnopqrstuvwxyz', /* firstly we process all strings. This is done because [{} and ] aren't escaped in strings, which complicates an iterative parse. */ @parent_ID=0; WHILE 1=1 --forever until there is nothing more to do BEGIN SELECT @start=PATINDEX('%[^a-zA-Z]["]%', @json);--next delimited string IF @start=0 BREAK --no more so drop through the WHILE loop IF SUBSTRING(@json, @start+1, 1)='"' BEGIN --Delimited Name SET @start=@Start+1; SET @end=PATINDEX('%[^\]["]%', RIGHT(@json, LEN(@json+'|')-@start)); END IF @end=0 --no end delimiter to last string BREAK --no more SELECT @token=SUBSTRING(@json, @start+1, @end-1) --now put in the escaped control characters SELECT @token=REPLACE(@token, FROMString, TOString) FROM (SELECT '\"' AS FromString, '"' AS ToString UNION ALL SELECT '\\', '\' UNION ALL SELECT '\/', '/' UNION ALL SELECT '\b', CHAR(08) UNION ALL SELECT '\f', CHAR(12) UNION ALL SELECT '\n', CHAR(10) UNION ALL SELECT '\r', CHAR(13) UNION ALL SELECT '\t', CHAR(09) ) substitutions SELECT @result=0, @escape=1 --Begin to take out any hex escape codes WHILE @escape>0 BEGIN SELECT @index=0, --find the next hex escape sequence @escape=PATINDEX('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%', @token) IF @escape>0 --if there is one BEGIN WHILE @index<4 --there are always four digits to a \x sequence BEGIN SELECT --determine its value @result=@result+POWER(16, @index) *(CHARINDEX(SUBSTRING(@token, @escape+2+3-@index, 1), @characters)-1), @index=@index+1 ; END -- and replace the hex sequence by its unicode value SELECT @token=STUFF(@token, @escape, 6, NCHAR(@result)) END END --now store the string away INSERT INTO @Strings (StringValue) SELECT @token -- and replace the string with a token SELECT @JSON=STUFF(@json, @start, @end+1, '@string'+CONVERT(NVARCHAR(5), @@identity)) END -- all strings are now removed. Now we find the first leaf. WHILE 1=1 --forever until there is nothing more to do BEGIN SELECT @parent_ID=@parent_ID+1 --find the first object or list by looking for the open bracket SELECT @FirstObject=PATINDEX('%[{[[]%', @json)--object or array IF @FirstObject = 0 BREAK IF (SUBSTRING(@json, @FirstObject, 1)='{') SELECT @NextCloseDelimiterChar='}', @type='object' ELSE SELECT @NextCloseDelimiterChar=']', @type='array' SELECT @OpenDelimiter=@firstObject WHILE 1=1 --find the innermost object or list... BEGIN SELECT @lenJSON=LEN(@JSON+'|')-1 --find the matching close-delimiter proceeding after the open-delimiter SELECT @NextCloseDelimiter=CHARINDEX(@NextCloseDelimiterChar, @json, @OpenDelimiter+1) --is there an intervening open-delimiter of either type? SELECT @NextOpenDelimiter=PATINDEX('%[{[[]%', RIGHT(@json, @lenJSON-@OpenDelimiter))--object IF @NextOpenDelimiter=0 --then we are done. BREAK SELECT @NextOpenDelimiter=@NextOpenDelimiter+@OpenDelimiter IF @NextCloseDelimiter<@NextOpenDelimiter --we have found the next leaf BREAK --we prepare to walk the document further IF SUBSTRING(@json, @NextOpenDelimiter, 1)='{' SELECT @NextCloseDelimiterChar='}', @type='object' ELSE SELECT @NextCloseDelimiterChar=']', @type='array' SELECT @OpenDelimiter=@NextOpenDelimiter END /*and now we can parse out the list or name/value pairs. We first pull out the structure into the variable '@contents' and replace it in the JSON document with a token representing it.*/ SELECT @contents=SUBSTRING(@json, @OpenDelimiter+1, @NextCloseDelimiter-@OpenDelimiter-1) SELECT @JSON=STUFF(@json, @OpenDelimiter, @NextCloseDelimiter-@OpenDelimiter+1, '@'+@type+CONVERT(NVARCHAR(5), @parent_ID)) /*and do each name/value pair, or just value, in the case of an ordered value list. */ WHILE (PATINDEX('%[A-Za-z0-9@+.e]%', @contents))<>0 BEGIN IF @Type='Object' /*it will be a 0-n list containing a string followed by a string, number,boolean, or null*/ BEGIN SELECT @end=CHARINDEX(':', ' '+@contents) /*if there is anything, it will be a string-based name.*/ SELECT @start=PATINDEX('%[^A-Za-z@][@]%', ' '+@contents)--find out what the token is SELECT @token=SUBSTRING(' '+@contents, @start+1, @End-@Start-1), @endofname=PATINDEX('%[0-9]%', @token),--and find out the number so as to fish out the string @param=RIGHT(@token, LEN(@token)-@endofname+1) SELECT--separate the token from the contents of the structure (chop, chop0 @token=LEFT(@token, @endofname-1), @Contents=RIGHT(' '+@contents, LEN(' '+@contents+'|')-@end-1) --now we get the string we have stored (names are stored as strings) SELECT @name=stringvalue FROM @strings WHERE string_id=@param --fetch the name END ELSE --it is merely a value in an ordered list, without a name SELECT @Name=null SELECT @end=CHARINDEX(',', @contents)-- a string-token, object-token, list-token, number,boolean, or null IF @end=0 --then we're at the end of the list SELECT @end=PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%', @Contents+' ') +1 SELECT @start=PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%', ' '+@contents) SELECT --get the value and snip the contents @Value=RTRIM(SUBSTRING(@contents, @start, @End-@Start)), @Contents=RIGHT(@contents+' ', LEN(@contents+'|')-@end) IF SUBSTRING(@value, 1, 7)='@object' INSERT INTO @hierarchy (NAME, parent_ID, StringValue, Object_ID, ValueType) SELECT @name, @parent_ID, SUBSTRING(@value, 8, 5), SUBSTRING(@value, 8, 5), 'object' ELSE IF SUBSTRING(@value, 1, 6)='@array' INSERT INTO @hierarchy (NAME, parent_ID, StringValue, Object_ID, ValueType) SELECT @name, @parent_ID, SUBSTRING(@value, 7, 5), SUBSTRING(@value, 7, 5), 'array' ELSE IF SUBSTRING(@value, 1, 7)='@string' --it is a string INSERT INTO @hierarchy (NAME, parent_ID, StringValue, ValueType) SELECT @name, @parent_ID, stringvalue, 'string' FROM @strings WHERE string_id=SUBSTRING(@value, 8, 5) ELSE IF @value IN ('true', 'false')--a boolean! INSERT INTO @hierarchy (NAME, parent_ID, StringValue, ValueType) SELECT @name, @parent_ID, @value, 'boolean' ELSE IF @value='null' --it is a null INSERT INTO @hierarchy (NAME, parent_ID, StringValue, ValueType) SELECT @name, @parent_ID, @value, 'null' ELSE IF PATINDEX('%[^0-9]%', @value)>0 --a real number INSERT INTO @hierarchy (NAME, parent_ID, StringValue, ValueType) SELECT @name, @parent_ID, @value, 'real' ELSE --it must be an INT INSERT INTO @hierarchy (NAME, parent_ID, StringValue, ValueType) SELECT @name, @parent_ID, @value, 'int' END END --and so lastly we put the root into the hierarchy. INSERT INTO @hierarchy (NAME, parent_ID, StringValue, Object_ID, ValueType) SELECT '-', NULL, '', @parent_id-1, @type -- RETURN END

对于 SQL Server 数据库中已存在同名对象,需要采取以下步骤:确认对象类型(表、视图、存储过程)。如果对象为空,可使用 IF NOT EXISTS 跳过创建。如果对象有数据,使用不同名称或修改结构。使用 DROP 删除现有对象(谨慎操作,建议备份)。检查架构更改,确保没有引用删除或重命名的对象。

当 SQL Server 服务无法启动时,可采取以下步骤解决:检查错误日志以确定根本原因。确保服务帐户具有启动服务的权限。检查依赖项服务是否正在运行。禁用防病毒软件。修复 SQL Server 安装。如果修复不起作用,重新安装 SQL Server。

要查看 SQL Server 端口号:打开 SSMS,连接到服务器。在对象资源管理器中找到服务器名称,右键单击它,然后选择“属性”。在“连接”选项卡中,查看“TCP 端口”字段。

SQL Server 数据库文件通常存储在以下默认位置:Windows: C:\Program Files\Microsoft SQL Server\MSSQL\DATALinux: /var/opt/mssql/data可通过修改数据库文件路径设置来自定义数据库文件位置。

问题发现这次使用的是SqlServer数据库,之前并没有使用过,但是问题不大,我按照需求文档的步骤连接好SqlServer之后,启动SpringBoot项目,发现了一个报错,如下:刚开始我以为是SqlServer连接问题呢,于是便去查看数据库,发现数据库一切正常,我首先第一时间问了我的同事,他们是否有这样的问题,发现他们并没有,于是我便开始了我最拿手的环节,面向百度编程。开始解决具体报错信息是这样,于是我便开始了百度报错:ERRORc.a.d.p.DruidDataSource$CreateCo

SQL Server 英文安装可通过以下步骤更改为中文:下载相应语言包;停止 SQL Server 服务;安装语言包;更改实例语言;更改用户界面语言;重启应用程序。

有网友反馈,在win11上无法安装sqlserver这款软件,不知道是怎么回事,根据目前的测试来看,win11存在硬盘问题,部分接口硬盘无法安装这款软件。win11为啥不能安装sqlserver:答:win11不能安装sqlserver是硬盘的问题。1、据了解,win11存在对于硬盘的检测bug。2、这导致sqlserver无法在“三星m.2接口”硬盘上安装。3、因此,如果我们要安装的话,需要准备一块其他硬盘。4、然后将该硬盘安装到电脑里,如果没有额外插槽的话就要换掉之前的硬盘。5、安装完成后,

可以通过以下步骤查询 SQL Server 数据库日志:1. 打开 SQL Server Management Studio,连接到数据库服务器;2. 展开“管理”节点,导航到“SQL Server 日志”;3. 选择要查询的日志文件,右键单击并选择“查看日志文件”;4. 浏览日志记录。其他查询日志方法:使用 Transact-SQL 查询、PowerShell Cmdlet。


ホットAIツール

Undresser.AI Undress
リアルなヌード写真を作成する AI 搭載アプリ

AI Clothes Remover
写真から衣服を削除するオンライン AI ツール。

Undress AI Tool
脱衣画像を無料で

Clothoff.io
AI衣類リムーバー

AI Hentai Generator
AIヘンタイを無料で生成します。

人気の記事

ホットツール

DVWA
Damn Vulnerable Web App (DVWA) は、非常に脆弱な PHP/MySQL Web アプリケーションです。その主な目的は、セキュリティ専門家が法的環境でスキルとツールをテストするのに役立ち、Web 開発者が Web アプリケーションを保護するプロセスをより深く理解できるようにし、教師/生徒が教室環境で Web アプリケーションを教え/学習できるようにすることです。安全。 DVWA の目標は、シンプルでわかりやすいインターフェイスを通じて、さまざまな難易度で最も一般的な Web 脆弱性のいくつかを実践することです。このソフトウェアは、

PhpStorm Mac バージョン
最新(2018.2.1)のプロフェッショナル向けPHP統合開発ツール

SublimeText3 Mac版
神レベルのコード編集ソフト(SublimeText3)

MinGW - Minimalist GNU for Windows
このプロジェクトは osdn.net/projects/mingw に移行中です。引き続きそこでフォローしていただけます。 MinGW: GNU Compiler Collection (GCC) のネイティブ Windows ポートであり、ネイティブ Windows アプリケーションを構築するための自由に配布可能なインポート ライブラリとヘッダー ファイルであり、C99 機能をサポートする MSVC ランタイムの拡張機能が含まれています。すべての MinGW ソフトウェアは 64 ビット Windows プラットフォームで実行できます。

ZendStudio 13.5.1 Mac
強力な PHP 統合開発環境
