FILESTREAM destek (odbc)

odbc içinde SQL Serveryerel istemci destekleyen Gelişmiş FILESTREAM özelliği. Bu özellik hakkında daha fazla bilgi için bkz: FILESTREAM destek. FILESTREAM desteğini odb gösteren bir örnek için bkz: Artımlı ile FILESTREAM (odbc) veri gönderme ve alma.

Göndermek ve almak için varbinary(max)bir uygulamanın 2 GB'tan büyük değerler kullanarak parametreleri bağlamalısınız SQLBindParameterile ColumnSizeiçin SQL_SS_LENGTH_UNLIMITEDve içeriğini StrLen_or_IndPtriçin SQL_DATA_AT_EXECönce SQLExecDirectveya SQLExecute.

Her yürütme sırasında veri parametre ile veri ile sağlanır gibi SQLParamDatave SQLPutData.

Sen-ebilmek seslenmek SQLGetDatatopakları sütunu ile ilişkili değilse FILESTREAM sütun veri getirmek için SQLBindCol.

İle bağlıysa, FILESTREAM veri güncelleştirebilirsiniz SQLBindCol.

Eğer arama SQLFetchbir bağımlı sütunu, bir "veriler kesildi" alacaksınız Uyarı arabellek yeterince büyük tüm değeri tutmak için değil. Bu uyarıyı dikkate almayıp ile ilişkili bu sütundaki verileri güncelleştirmek SQLParamDatave SQLPutDataçağrı. Kullanarak FILESTREAM veri güncelleştirebilirsiniz SQLSetPosile ilişkili ise SQLBindCol.

Örnek

FILESTREAM sütunları davranır gibi tam olarak varbinary(max)sütunlar, ama olmayan bir boyut sınırı. Onlar SQL_VARBINARY bağlanmıştır. (SQL_LONGVARBINARY görüntü sütunları ile kullanılır ve bu tür kısıtlamalar vardır. Şekillendirebilir SQL_LONGVARBINARY connot output parametresi olarak kullanılmış olabilir.) Aşağıdaki örnekler, doğrudan ntfs erişim için FILESTREAM sütunları Bu örneklerde varsayılmaktadır şu Transact-SQLkodu veritabanında yürütülen:

CREATE TABLE fileStreamDocs(
id uniqueidentifier ROWGUIDCOL NOT NULL UNIQUE,
author varchar(64),
document VARBINARY(MAX) FILESTREAM NULL)

CREATE TABLE fileStreamDocs(
id uniqueidentifier ROWGUIDCOL NOT NULL UNIQUE,
author varchar(64),
document VARBINARY(MAX) FILESTREAM NULL)

Okuma

void selectFilestream (LPCWSTR dstFilePath) {
SQLRETURN r;
SQLCHAR transactionToken[1024];
SQLWCHAR srcFilePath[1024];
SQLINTEGER cbTransactionToken, cbsrcFilePath;

// The GUID columns must be visible to the query, 
// even if it is not used
r = SQLExecDirect(hstmt, (SQLTCHAR *) 
_T("select GET_FILESTREAM_TRANSACTION_CONTEXT(); \
select TOP(1) id, document.PathName() \
from fileStreamDocs WHERE author = 'Chris Lee'"), 
SQL_NTS);
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}

r = SQLFetch(hstmt);
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}

r = SQLGetData(hstmt, 1, SQL_C_BINARY, 
transactionToken, sizeof(transactionToken), &cbTransactionToken);
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}

r = SQLMoreResults(hstmt);
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}

r = SQLFetch(hstmt);
r = SQLGetData(hstmt, 2, SQL_C_TCHAR, 
srcFilePath, sizeof(srcFilePath), &cbsrcFilePath);
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}

if (!copyFileFromSql(srcFilePath, dstFilePath, transactionToken, cbTransactionToken)) {
DeleteFile(dstFilePath);
}
r = SQLTransact(henv, hdbc, SQL_ROLLBACK);
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}
}

void selectFilestream (LPCWSTR dstFilePath) {
SQLRETURN r;
SQLCHAR transactionToken[1024];
SQLWCHAR srcFilePath[1024];
SQLINTEGER cbTransactionToken, cbsrcFilePath;

// The GUID columns must be visible to the query, 
// even if it is not used
r = SQLExecDirect(hstmt, (SQLTCHAR *) 
_T("select GET_FILESTREAM_TRANSACTION_CONTEXT(); \
select TOP(1) id, document.PathName() \
from fileStreamDocs WHERE author = 'Chris Lee'"), 
SQL_NTS);
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}

r = SQLFetch(hstmt);
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}

r = SQLGetData(hstmt, 1, SQL_C_BINARY, 
transactionToken, sizeof(transactionToken), &cbTransactionToken);
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}

r = SQLMoreResults(hstmt);
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}

r = SQLFetch(hstmt);
r = SQLGetData(hstmt, 2, SQL_C_TCHAR, 
srcFilePath, sizeof(srcFilePath), &cbsrcFilePath);
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}

if (!copyFileFromSql(srcFilePath, dstFilePath, transactionToken, cbTransactionToken)) {
DeleteFile(dstFilePath);
}
r = SQLTransact(henv, hdbc, SQL_ROLLBACK);
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}
}

Ekle

void insertFilestream(LPCWSTR srcFilePath) {
SQLRETURN r;
SQLCHAR transactionToken[64];
SQLWCHAR dstFilePath[1024];
SQLINTEGER cbTransactionToken, cbDstFilePath;
SQLUSMALLINT mode;

r = SQLExecDirect(hstmt, (SQLTCHAR *) 
_T("insert into fileStreamDocs (id, author, document)\
    output Get_Filestream_Transaction_Context(), inserted.document.PathName() \
        values (newid(), 'Chris Lee', convert(varbinary, '**Temp**')) "), SQL_NTS);
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}

r = SQLFetch(hstmt);
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}

r = SQLGetData(hstmt, 1, SQL_C_BINARY,
transactionToken, sizeof(transactionToken), &cbTransactionToken);
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}

r = SQLGetData(hstmt, 2, SQL_C_TCHAR,
dstFilePath, sizeof(dstFilePath), &cbDstFilePath);
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}

r = SQLCloseCursor(hstmt);
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}

if (copyFileToSql(
srcFilePath, dstFilePath, 
transactionToken, cbTransactionToken)) {
mode = SQL_COMMIT;
}
else {
mode = SQL_ROLLBACK;
}

r = SQLTransact(henv, hdbc, mode);
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}
}

void insertFilestream(LPCWSTR srcFilePath) {
SQLRETURN r;
SQLCHAR transactionToken[64];
SQLWCHAR dstFilePath[1024];
SQLINTEGER cbTransactionToken, cbDstFilePath;
SQLUSMALLINT mode;

r = SQLExecDirect(hstmt, (SQLTCHAR *) 
_T("insert into fileStreamDocs (id, author, document)\
    output Get_Filestream_Transaction_Context(), inserted.document.PathName() \
        values (newid(), 'Chris Lee', convert(varbinary, '**Temp**')) "), SQL_NTS);
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}

r = SQLFetch(hstmt);
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}

r = SQLGetData(hstmt, 1, SQL_C_BINARY,
transactionToken, sizeof(transactionToken), &cbTransactionToken);
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}

r = SQLGetData(hstmt, 2, SQL_C_TCHAR,
dstFilePath, sizeof(dstFilePath), &cbDstFilePath);
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}

r = SQLCloseCursor(hstmt);
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}

if (copyFileToSql(
srcFilePath, dstFilePath, 
transactionToken, cbTransactionToken)) {
mode = SQL_COMMIT;
}
else {
mode = SQL_ROLLBACK;
}

r = SQLTransact(henv, hdbc, mode);
if (r != SQL_SUCCESS && r!=SQL_SUCCESS_WITH_INFO) {
ODBCError(henv, hdbc, hstmt, NULL, true); exit(-1);
}
}

Yardımcı yordamları

#define COPYBUFFERSIZE 4096
BOOL copyFileContents (HANDLE srcHandle, HANDLE dstHandle) {

BYTE buffer[COPYBUFFERSIZE];
DWORD bytesRead, bytesWritten;
BOOL r;

do {
r = ReadFile(srcHandle, buffer, COPYBUFFERSIZE, &bytesRead,NULL);
if (bytesRead == 0) {
return r;
}
r = WriteFile(dstHandle, buffer, bytesRead, &bytesWritten, NULL);
if (bytesWritten == 0) {
return r;
}
} while (TRUE);
}

BOOL copyFileToSql(LPCWSTR srcFilePath, LPCWSTR dstFilePath, LPBYTE transactionToken, SQLINTEGER cbTransactionToken) {


BOOL r;

HANDLE srcHandle, dstHandle;
unsigned int NtStatus;

srcHandle =  CreateFile(
                         srcFilePath,
                         GENERIC_READ,
                         FILE_SHARE_READ,
                         NULL,
                         OPEN_EXISTING,
                         FILE_FLAG_SEQUENTIAL_SCAN,
 NULL);

if (srcHandle == INVALID_HANDLE_VALUE) {
return FALSE;
}

dstHandle =  OpenSqlFilestream(
                         dstFilePath,
                         Write,
                         0,
                         transactionToken,
                         cbTransactionToken,
                         0);

if (dstHandle == INVALID_HANDLE_VALUE) {
NtStatus = GetLastError();
r = CloseHandle(srcHandle);
return FALSE;
}

//copy file
r = copyFileContents(srcHandle, dstHandle);

CloseHandle(srcHandle);

CloseHandle(dstHandle);

return r;
}

BOOL copyFileFromSql(LPCWSTR srcFilePath, LPCWSTR dstFilePath, LPBYTE transactionToken, SQLINTEGER cbTransactionToken) {


BOOL r;

HANDLE srcHandle, dstHandle;
unsigned int NtStatus;

srcHandle =  OpenSqlFilestream(
                         srcFilePath,
                         Read,
                         0,
                         transactionToken,
                         cbTransactionToken,
                         0);

if (srcHandle == INVALID_HANDLE_VALUE) {
return FALSE;
}

dstHandle =  CreateFile(
                         dstFilePath,
                         GENERIC_WRITE,
                         0,
                         NULL,
                         OPEN_ALWAYS,
                         FILE_ATTRIBUTE_NORMAL,
 NULL);

if (dstHandle == INVALID_HANDLE_VALUE) {
CloseHandle(srcHandle);
return FALSE;
}

r = copyFileContents(srcHandle, dstHandle);

CloseHandle(srcHandle);

CloseHandle(dstHandle);

return r;
}

#define COPYBUFFERSIZE 4096
BOOL copyFileContents (HANDLE srcHandle, HANDLE dstHandle) {

BYTE buffer[COPYBUFFERSIZE];
DWORD bytesRead, bytesWritten;
BOOL r;

do {
r = ReadFile(srcHandle, buffer, COPYBUFFERSIZE, &bytesRead,NULL);
if (bytesRead == 0) {
return r;
}
r = WriteFile(dstHandle, buffer, bytesRead, &bytesWritten, NULL);
if (bytesWritten == 0) {
return r;
}
} while (TRUE);
}

BOOL copyFileToSql(LPCWSTR srcFilePath, LPCWSTR dstFilePath, LPBYTE transactionToken, SQLINTEGER cbTransactionToken) {


BOOL r;

HANDLE srcHandle, dstHandle;
unsigned int NtStatus;

srcHandle =  CreateFile(
                         srcFilePath,
                         GENERIC_READ,
                         FILE_SHARE_READ,
                         NULL,
                         OPEN_EXISTING,
                         FILE_FLAG_SEQUENTIAL_SCAN,
 NULL);

if (srcHandle == INVALID_HANDLE_VALUE) {
return FALSE;
}

dstHandle =  OpenSqlFilestream(
                         dstFilePath,
                         Write,
                         0,
                         transactionToken,
                         cbTransactionToken,
                         0);

if (dstHandle == INVALID_HANDLE_VALUE) {
NtStatus = GetLastError();
r = CloseHandle(srcHandle);
return FALSE;
}

//copy file
r = copyFileContents(srcHandle, dstHandle);

CloseHandle(srcHandle);

CloseHandle(dstHandle);

return r;
}

BOOL copyFileFromSql(LPCWSTR srcFilePath, LPCWSTR dstFilePath, LPBYTE transactionToken, SQLINTEGER cbTransactionToken) {


BOOL r;

HANDLE srcHandle, dstHandle;
unsigned int NtStatus;

srcHandle =  OpenSqlFilestream(
                         srcFilePath,
                         Read,
                         0,
                         transactionToken,
                         cbTransactionToken,
                         0);

if (srcHandle == INVALID_HANDLE_VALUE) {
return FALSE;
}

dstHandle =  CreateFile(
                         dstFilePath,
                         GENERIC_WRITE,
                         0,
                         NULL,
                         OPEN_ALWAYS,
                         FILE_ATTRIBUTE_NORMAL,
 NULL);

if (dstHandle == INVALID_HANDLE_VALUE) {
CloseHandle(srcHandle);
return FALSE;
}

r = copyFileContents(srcHandle, dstHandle);

CloseHandle(srcHandle);

CloseHandle(dstHandle);

return r;
}

Ayrıca bkz.

Diğer Kaynaklar

SQL Server yerel istemci programlama