SQLite读写二进制数据


- (MRecTemplate*)GetDataRecTemplate
{
    MRecTemplate* data = [[[MRecTemplate alloc] init] autorelease];

    sqlite3_stmt* stmt;
    NSString* str = @"SELECT * FROM RecTemplates";
    int nResult = sqlite3_prepare_v2(m_db, [str UTF8String], -1, &stmt, NULL);
    if (nResult == SQLITE_OK)
    {
        if (sqlite3_step(stmt) == SQLITE_ROW)
        {
            int i = 0;
            char* Disclaimer = (char*)sqlite3_column_text(stmt, i++);
            data.Disclaimer = Disclaimer ? [NSString stringWithUTF8String:Disclaimer] : @"";
            data.Logo = [[[NSData alloc]
                         initWithBytes:sqlite3_column_blob(stmt,i) length:sqlite3_column_bytes(stmt,i++)]
                         autorelease];
            char* DateModified = (char*)sqlite3_column_text(stmt, i++);
            data.DateModified = [m_dfDateTime dateFromString:[NSString stringWithUTF8String:DateModified]];
            char* ServerTime = (char*)sqlite3_column_text(stmt, i++);
            data.ServerTime = [m_dfDateTime dateFromString:[NSString stringWithUTF8String:ServerTime]];
        }
        sqlite3_finalize(stmt);
    }
    else
    {
        LOG(@"Problem in DB, return=%d, SQL=%@", nResult, str);
    }

    return data;
}

- (BOOL)AddDataRecTemplate:(MRecTemplate*)data
{
    if (data.ServerTime == nil)
        data.ServerTime = [NSDate date];

    sqlite3_stmt* stmt;
    NSString* str = [NSString stringWithFormat:
                     @"INSERT INTO RecTemplates(Disclaimer, Logo, ServerTime) VALUES('%@', ?, '%@')",
                     data.Disclaimer,
                     [m_dfDateTime stringFromDate:data.ServerTime]];
    int nResult = sqlite3_prepare_v2(m_db, [str UTF8String], -1, &stmt, NULL);
    if (nResult == SQLITE_OK)
    {
        if (data.Logo)
            sqlite3_bind_blob(stmt, 1, [data.Logo bytes], [data.Logo length], NULL);
        else
            sqlite3_bind_blob(stmt, 1, nil, -1, NULL);
        sqlite3_step(stmt);
        sqlite3_finalize(stmt);
    }
    else
    {
        LOG(@"Problem in DB, return=%d, SQL=%@", nResult, str);
        return NO;
    }

    return YES;
}

- (BOOL)UpdateDataRecTemplate:(MRecTemplate*)data
{
    if (data.ServerTime == nil)
        data.ServerTime = [NSDate date];

    sqlite3_stmt* stmt;
    NSString* str = [NSString stringWithFormat:
                     @"UPDATE RecTemplates SET Disclaimer='%@', Logo=?, ServerTime='%@'",
                     data.Disclaimer,
                     [m_dfDateTime stringFromDate:data.ServerTime]];
    int nResult = sqlite3_prepare_v2(m_db, [str UTF8String], -1, &stmt, NULL);
    if (nResult == SQLITE_OK)
    {
        if (data.Logo)
            sqlite3_bind_blob(stmt, 1, [data.Logo bytes], [data.Logo length], NULL);
        else
            sqlite3_bind_blob(stmt, 1, nil, -1, NULL);
        sqlite3_step(stmt);
        sqlite3_finalize(stmt);
    }
    else
    {
        LOG(@"Problem in DB, return=%d, SQL=%@", nResult, str);
        return NO;
    }

    return YES;
}

Advertisements

Database is in transition. Try the statement later.

–get the dbid which is in Transition mode, check the status is 0 or not.

select * from sys.databases where name =

–if status is not zero reset db status

exec sp_resetstatus

–look for blocking spid or IF WaitRsource column has that DBID

select * from sys.sysprocesses

–Look for waitresource column if that dbid is blocking

select * from sys.dm_exec_requests

–kill the spid or sessionid which is Bloking, OR try to kill session/process which is waiting on resurces.

kill

— immediatly execute put db offline with rolback

ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE

SQL Server Base64编码


CREATE FUNCTION ToBase64 (@Input varchar(6000))
RETURNS varchar(8000)
AS
BEGIN
   DECLARE
      @Output varchar(8000),
      @Bits varbinary(3),
      @Pos int
   SET @Pos = 1
   SET @Output = ''
   WHILE @Pos <= Len(@Input) BEGIN
      SET @Bits = Convert(varbinary(3), Substring(@Input, @Pos, 3))
      SET @Output = @Output + Substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/', Substring(@Bits, 1, 1) / 4 + 1, 1)
      SET @Output = @Output + Substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/', Substring(@Bits, 1, 1) % 4 * 16 + Substring(@Bits, 2, 1) / 16 + 1, 1)
      SET @Output = @Output + Substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/', Substring(@Bits, 2, 1) % 16 * 4 + Substring(@Bits, 3, 1) / 64 + 1, 1)
      SET @Output = @Output + Substring('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/', Substring(@Bits, 3, 1) % 64 + 1, 1)
      SET @Pos = @Pos + 3
   END
   RETURN (Left(@Output, Len(@Output) - 3 + Len(@Bits)) + Replicate('=', 3 - Len(@Bits)))
END
GO
SELECT dbo.ToBase64('Base 64 encoding')
-- QmFzZSA2NCBlbmNvZGluZw==

SQL Server十六进制字符串转二进制


CREATE FUNCTION dbo.HexStrToVarBin(@hexstr VARCHAR(max))
RETURNS varbinary(max)
AS

BEGIN
   DECLARE @hex CHAR(2), @i INT, @count INT, @b varbinary(max), @odd BIT, @start bit
   SET @count = LEN(@hexstr)
   SET @start = 1
   SET @b = CAST('' AS varbinary(1))
   IF SUBSTRING(@hexstr, 1, 2) = '0x'
       SET @i = 3
   ELSE
       SET @i = 1
   SET @odd = CAST(LEN(SUBSTRING(@hexstr, @i, LEN(@hexstr))) % 2 AS BIT)
   WHILE (@i <= @count)
    BEGIN
       IF @start = 1 AND @odd = 1
       BEGIN
           SET @hex = '0' + SUBSTRING(@hexstr, @i, 1)
       END
       ELSE
       BEGIN
           SET @hex = SUBSTRING(@hexstr, @i, 2)
       END
       SET @b = @b +
               CAST(CASE WHEN SUBSTRING(@hex, 1, 1) LIKE '[0-9]'
                   THEN CAST(SUBSTRING(@hex, 1, 1) AS INT)
                   ELSE CAST(ASCII(UPPER(SUBSTRING(@hex, 1, 1)))-55 AS INT)
               END * 16 +
               CASE WHEN SUBSTRING(@hex, 2, 1) LIKE '[0-9]'
                   THEN CAST(SUBSTRING(@hex, 2, 1) AS INT)
                   ELSE CAST(ASCII(UPPER(SUBSTRING(@hex, 2, 1)))-55 AS INT)
               END AS binary(1))
       SET @i = @i + (2 - (CAST(@start AS INT) * CAST(@odd AS INT)))
       IF @start = 1
       BEGIN
           SET @start = 0
       END
    END
    RETURN @b
END

用VBS将Access数据库表结构转SQL语句


Sub Class1()
    Dim db As Database
    Dim tdf As TableDef
    Dim fld As DAO.Field
    Dim ndx As DAO.Index
    Dim strSQL As String
    Dim strFlds As String
    Dim strCn As String

    Dim fs, f

    Set db = CurrentDb

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.CreateTextFile("D:Schema.txt")

    For Each tdf In db.TableDefs
        If Left(tdf.Name, 4)  "Msys" Then
            strSQL = "CREATE TABLE [" & tdf.Name & "] ("

            strFlds = ""

            For Each fld In tdf.Fields

                strFlds = strFlds & ",[" & fld.Name & "] "

                Select Case fld.Type

                    Case dbText
                        'No look-up fields
                        strFlds = strFlds & "varchar(" & fld.Size & ")"

                    Case dbLong
                        If (fld.Attributes And dbAutoIncrField) = 0& Then
                            strFlds = strFlds & "integer"
                        Else
                            strFlds = strFlds & "integer"
                        End If

                    Case dbBoolean
                        strFlds = strFlds & "bit"

                    Case dbByte
                        strFlds = strFlds & "byte"

                    Case dbInteger
                        strFlds = strFlds & "integer"

                    Case dbCurrency
                        strFlds = strFlds & "decimal"

                    Case dbSingle
                        strFlds = strFlds & "float"

                    Case dbDouble
                        strFlds = strFlds & "float"

                    Case dbDate
                        strFlds = strFlds & "datetime"

                    Case dbBinary
                        strFlds = strFlds & "image"

                    Case dbLongBinary
                        strFlds = strFlds & "image"

                    Case dbMemo
                        If (fld.Attributes And dbHyperlinkField) = 0& Then
                            strFlds = strFlds & "text"
                        Else
                            strFlds = strFlds & "hyperlink"
                        End If

                    Case dbGUID
                        strFlds = strFlds & "uniqueidentifier"

                End Select

            Next

            strSQL = strSQL & Mid(strFlds, 2) & ")"
            f.WriteLine vbCrLf & strSQL

            'Indexes
            For Each ndx In tdf.Indexes
                Exit For

                If ndx.Unique Then
                    strSQL = "strSQL=""CREATE UNIQUE INDEX "
                Else
                    strSQL = "strSQL=""CREATE INDEX "
                End If

                strSQL = strSQL & "[" & ndx.Name & "] ON [" & tdf.Name & "] ("

                strFlds = ""

                For Each fld In tdf.Fields
                    strFlds = ",[" & fld.Name & "]"
                Next

                strSQL = strSQL & Mid(strFlds, 2) & ") "

                strCn = ""

                If ndx.Primary Then
                    strCn = " PRIMARY"
                End If

                If ndx.Required Then
                    strCn = strCn & " DISALLOW NULL"
                End If

                If ndx.IgnoreNulls Then
                    strCn = strCn & " IGNORE NULL"
                End If

                If Trim(strCn)  vbNullString Then
                    strSQL = strSQL & " WITH" & strCn & " "
                End If

                f.WriteLine vbCrLf & strSQL & """" & vbCrLf & "Currentdb.Execute strSQL"
            Next
        End If
    Next

    f.Close
End Sub

 

Sql Server枚举表结构

–输入参数
–@tableName

SELECT column_name=syscolumns.name, datatype=systypes.name, length=syscolumns.length
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE sysobjects.xtype=’U’
AND sysobjects.name=@tableName
ANDsystypes.name <> ‘sysname’
ANDsyscolumns.name <> ‘isactive’

Sql Server读取ADO样式的XML文件

–存储过程输入参数
–@Path varchar(500)
–@FileName varchar(500)

set @sql = ‘DECLARE @idoc int ‘
set @sql += ‘DECLARE @xmlDocument varchar(MAX) ‘
set @sql += ‘DECLARE @Status int’

set @sql += ‘set @xmlDocument = dbo.ufsReadfileAsString(‘ + char(39) + @Path + char(39) + ‘,’ + char(39) + @FileName + char(39) + ‘) ‘

set @sql += ‘EXEC @Status = sp_xml_preparedocument @idoc OUTPUT, ‘
set @sql += ‘@xmlDocument,’ + char(39) + ‘<xml xmlns:s=”uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882″
xmlns:dt=”uuid:C2F41010-65B3-11d1-A29F-00AA00C14882″
xmlns:rs=”urn:schemas-microsoft-com:rowset”
xmlns:z=”#RowsetSchema”/>’ + char(39)

–sp_xml_preparedocument第三个参数[xpath_namespaces]定义了XML的格式,与实际XML文件头一致。默认值是<root xmlns:mp=”urn:schemas-microsoft-com:xml-metaprop”>。

–读取XML, tmpSQL1是表结构,例如[UserID] int ‘@UserID’,[ClientFileName] varchar(100) ‘@ClientFileName’,[farmID] int ‘@farmID’,[farmname] nvarchar(200) ‘@farmname’,[Area] float ‘@Area’,[fExclude] bit ‘@fExclude’,[Rainfall] float ‘@Rainfall’,[LocalID] nvarchar(200) ‘@LocalID’,[fSort] int ‘@fSort’,[fNotes] ntext ‘@fNotes’,[DateModified] varchar(50) ‘@DateModified’,[UniqueRefLocal] uniqueidentifier ‘@UniqueRefLocal’,[ClientFileNameGUID] varchar(100) ‘@ClientFileNameGUID’

Declare @tmpSQL1 varchar(max)
Declare @sqlXML varchar(max)

set @sqlXML = ‘SELECT *  FROM OPENXML (@idoc,’ + char(39) + ‘/xml/rs:data/z:row’ + char(39) + ‘,1)  WITH (‘
set @sqlXML += @tmpSQL1
set @sqlXML += ‘)) ST ‘