SQL存取圖檔的問題 |
答題得分者是:Stallion
|
hsiehwei
一般會員 發表:4 回覆:11 積分:3 註冊:2005-02-04 發送簡訊給我 |
我找過這邊很多資料
似乎處理image的方法都是用資料感知元件來做
我想使用SQL語法來做
我有找到一篇
http://delphi.ktop.com.tw/topic.php?topic_id=43997 我的方法如下 if(OpenPictureDialog1->Execute())
{
ZQuary->Close();
ZQuary->SQL->Text = "INSERT INTO attachment (workDate, departmentID, attachmentID, image) VALUES(:d1, :d2, :d3, :d4);";
ZQuary->Params->ParamByName("d1")->Value = workDate;
ZQuary->Params->ParamByName("d2")->Value = departmentID;
ZQuary->Params->ParamByName("d3")->Value = attachNumbers;
ZQuary->Params->ParamByName("d4")->LoadFromFile(OpenPictureDialog1->FileName, ftBlob);
ClientMainForm->ZQAttachment->ExecSQL();
} 執行的時候會出現語法錯誤
就在一排亂碼的地方
請問我要怎麼用SQL語法來完成儲存以及讀取圖檔? 我用Value的方式存入
在Console下 select * from attachment(那個table)
該欄位(image)會顯示檔案路徑
這樣是對的嗎? 我取檔的時候用stream的方式
TJPEGImage *jpg = new TJPEGImage;
TMemoryStream *ms;
TBlobField(ZQAttachment->FieldByName("image")).SaveToStream(ms);
ms->Position = 0;
jpg->LoadFromStream(ms);
ClientMainForm->Image1->Picture->Bitmap->Assign(jpg); 結果出現
'Access violation at address 403261A2 in module dbrtl60.bpl'
這樣的錯誤
它會指向這一行
TBlobField(ZQAttachment->FieldByName("image")).SaveToStream(ms);
我不能用這樣的方式存取資料庫圖檔嗎? 我的環境
Borland C Builder 6.0 update4
Zeos 6.5.1
MySQL 4.1.9 發表人 - hsiehwei 於 2005/05/08 10:46:36
|
Stallion
版主 發表:52 回覆:1600 積分:1995 註冊:2004-09-15 發送簡訊給我 |
我是參考這個連結點:
http://delphi.ktop.com.tw/topic.php?topic_id=44990 procedure TfrmThree_A_Data.s3Click(Sender: TObject);
var
fstrm:TFileStream;
BS:TBlobStream;
begin
with q2 do begin
with sql do begin
clear;
Add('select * from sal_letter');
Add('where LETTER_NO=:no');
end;
ParamByName('no').AsString := LETTER_NO.Text;
ExecSQL;
open;
end;
if OpenDialog1.Execute then
begin
fstrm:=TFileStream.Create(OpenDialog1.FileName,fmOpenRead OR fmShareDenyWrite);
fstrm.Seek(soFromBeginning,0);
q2.Edit;
BS:=TBlobStream.Create(CREATEBLOBSTREAM(q2.FIELDBYNAME('FILE1')),bmWrite);
BS.CopyFrom(fstrm,0);
BS.Free;
q2.post;
end; ,使用SQL語法配合TBLOBSTREAM把二進位檔放入SERVER中的,請參考! -----------------------------------------------
Creation is the fundation of promotion.
|
hsiehwei
一般會員 發表:4 回覆:11 積分:3 註冊:2005-02-04 發送簡訊給我 |
|
hsiehwei
一般會員 發表:4 回覆:11 積分:3 註冊:2005-02-04 發送簡訊給我 |
|
hsiehwei
一般會員 發表:4 回覆:11 積分:3 註冊:2005-02-04 發送簡訊給我 |
可以執行了
但是執行完我去Console下select
該欄位是NULL
哪邊錯了嗎? 我的程式如下
TFileStream *fstrm; TBlobStream *BS; if(ClientMainForm->OpenPictureDialog1->Execute()) { fstrm = new TFileStream(ClientMainForm->OpenPictureDialog1->FileName, fmShareDenyWrite); fstrm->Seek(soFromBeginning,0); // INSERT but blob ClientMainForm->ZQAttachment->Close(); ClientMainForm->ZQAttachment->SQL->Clear(); sqlCommand = "INSERT INTO attachment VALUES('" workDate "', " departmentID ", " attachmentID ", null);"; ClientMainForm->ZQAttachment->SQL->Add(sqlCommand); ClientMainForm->ZQAttachment->ExecSQL(); // Edit ClientMainForm->ZQAttachment->Close(); ClientMainForm->ZQAttachment->SQL->Clear(); sqlCommand = "SELECT * from attachment where workDate='" workDate "' AND departmentID=" departmentID " AND attachmentID=" attachmentID ";"; ClientMainForm->ZQAttachment->SQL->Add(sqlCommand); ClientMainForm->ZQAttachment->ExecSQL(); ClientMainForm->ZQAttachment->Open(); ClientMainForm->ZQAttachment->Edit(); // BS = new TBlobStream((TBlobField*)ClientMainForm->ZQAttachment->FieldByName("image"),bmWrite); BS = (TBlobStream*)ClientMainForm->ZQAttachment->CreateBlobStream(ClientMainForm->ZQAttachment->FieldByName("image"),bmWrite); BS->CopyFrom(fstrm,0); BS->Free(); fstrm->Free(); ClientMainForm->ZQAttachment->Post();發表人 - hsiehwei 於 2005/05/08 20:22:33 |
Stallion
版主 發表:52 回覆:1600 積分:1995 註冊:2004-09-15 發送簡訊給我 |
|
hsiehwei
一般會員 發表:4 回覆:11 積分:3 註冊:2005-02-04 發送簡訊給我 |
引言: 讀檔參數錯了! fstrm = new TFileStream(ClientMainForm->OpenPictureDialog1->FileName,fmOpenRead,fmShareDenyWrite); ----------------------------------------------- Creation is the fundation of promotion.還是一樣吶 mysql> select * from attachment; ------------ -------------- -------------- ------- | workDate | departmentID | attachmentID | image | ------------ -------------- -------------- ------- | 2005-05-08 | 0 | 1 | NULL | ------------ -------------- -------------- ------- |
Stallion
版主 發表:52 回覆:1600 積分:1995 註冊:2004-09-15 發送簡訊給我 |
sqlCommand = "SELECT * from attachment where workDate='" workDate
"' AND departmentID=" departmentID
" AND attachmentID=" attachmentID ";";
ClientMainForm->ZQAttachment->SQL->Add(sqlCommand);
// ClientMainForm->ZQAttachment->ExecSQL(); //這行拿掉看看,因為OPEN就是執行SQL指令,是不是連續執行兩次查詢的關係?
ClientMainForm->ZQAttachment->Open();
ClientMainForm->ZQAttachment->Edit();
// BS = new TBlobStream((TBlobField*)ClientMainForm->ZQAttachment->FieldByName("image"),bmWrite);
BS = (TBlobStream*)ClientMainForm->ZQAttachment->CreateBlobStream(ClientMainForm->ZQAttachment->FieldByName("image"),bmWrite);
BS->CopyFrom(fstrm,0);
BS->Free();
fstrm->Free();
ClientMainForm->ZQAttachment->Post();
ClientMainForm->ZQAttachment->Close(); -----------------------------------------------
Creation is the fundation of promotion. 發表人 - stallion 於 2005/05/09 19:44:17
|
hsiehwei
一般會員 發表:4 回覆:11 積分:3 註冊:2005-02-04 發送簡訊給我 |
引言: // ClientMainForm->ZQAttachment->ExecSQL(); //這行拿掉看看,因為OPEN就是執行SQL指令,是不是連續執行兩次查詢的關係? ----------------------------------------------- Creation is the fundation of promotion. 發表人 - stallion 於 2005/05/09 19:44:17拿掉以後,執行的時候發生SQL語法錯誤,一堆亂碼 請問這是什麼問題呢? |
hsiehwei
一般會員 發表:4 回覆:11 積分:3 註冊:2005-02-04 發送簡訊給我 |
引言:我找到問題在哪了 因為我為了中文字,所以有執行過 SET NAMES BIG5 所以加入下面這些就好了 ClientMainForm->ZQAttachment->Close(); ClientMainForm->ZQAttachment->SQL->Clear(); ClientMainForm->ZQAttachment->SQL->Add("Set NAMES latin1"); ClientMainForm->ZQAttachment->ExecSQL(); ClientMainForm->ZQAttachment->SQL->Clear(); 所以變成 if(ClientMainForm->OpenPictureDialog1->Execute()) { fstrm = new TFileStream(ClientMainForm->OpenPictureDialog1->FileName, fmOpenRead, fmShareDenyWrite); fstrm->Seek(soFromBeginning,0); // INSERT but blob ClientMainForm->ZQAttachment->Close(); ClientMainForm->ZQAttachment->SQL->Clear(); ClientMainForm->ZQAttachment->SQL->Add("Set NAMES latin1"); ClientMainForm->ZQAttachment->ExecSQL(); ClientMainForm->ZQAttachment->SQL->Clear(); sqlCommand = "INSERT INTO attachment VALUES('" workDate "', " departmentID ", " attachmentID ", null);"; ClientMainForm->ZQAttachment->SQL->Add(sqlCommand); ClientMainForm->ZQAttachment->ExecSQL(); // Edit ClientMainForm->ZQAttachment->Close(); ClientMainForm->ZQAttachment->SQL->Clear(); sqlCommand = "SELECT * from attachment where workDate='" workDate "' AND departmentID=" departmentID " AND attachmentID=" attachmentID ";"; ClientMainForm->ZQAttachment->SQL->Add(sqlCommand); // ClientMainForm->ZQAttachment->ExecSQL(); ClientMainForm->ZQAttachment->Open(); ClientMainForm->ZQAttachment->Edit(); // BS = new TBlobStream((TBlobField*)ClientMainForm->ZQAttachment->FieldByName("image"),bmWrite); BS = (TBlobStream*)ClientMainForm->ZQAttachment->CreateBlobStream(ClientMainForm->ZQAttachment->FieldByName("image"),bmWrite); BS->CopyFrom(fstrm, 0); BS->Free(); fstrm->Free(); ClientMainForm->ZQAttachment->Post(); ClientMainForm->ZQAttachment->Close(); ClientMainForm->ZQAttachment->SQL->Clear(); ClientMainForm->ZQAttachment->SQL->Add("Set NAMES big5"); ClientMainForm->ZQAttachment->ExecSQL(); } 或者 if(ClientMainForm->OpenPictureDialog1->Execute()) { ClientMainForm->ZQAttachment->Close(); ClientMainForm->ZQAttachment->SQL->Clear(); ClientMainForm->ZQAttachment->SQL->Add("Set NAMES latin1"); ClientMainForm->ZQAttachment->ExecSQL(); ClientMainForm->ZQAttachment->SQL->Clear(); ClientMainForm->ZQAttachment->SQL->Text = "INSERT INTO attachment (workDate, departmentID, attachmentID, image) VALUES(:d1, :d2, :d3, :d4);"; ClientMainForm->ZQAttachment->Params->ParseSQL(ClientMainForm->ZQAttachment->SQL->Text, true); ClientMainForm->ZQAttachment->Params->ParamByName("d1")->Value = workDate; ClientMainForm->ZQAttachment->Params->ParamByName("d2")->Value = departmentID; ClientMainForm->ZQAttachment->Params->ParamByName("d3")->Value = attachmentID; ClientMainForm->ZQAttachment->Params->ParamByName("d4")->LoadFromFile(ClientMainForm->OpenPictureDialog1->FileName, ftBlob); ClientMainForm->ZQAttachment->ExecSQL(); ClientMainForm->ZQAttachment->SQL->Clear(); ClientMainForm->ZQAttachment->SQL->Add("Set NAMES big5"); ClientMainForm->ZQAttachment->ExecSQL(); ZQAttachmentShow(); } 這樣就可以存圖了 讀檔 ClientMainForm->ZQAttachment->Close(); ClientMainForm->ZQAttachment->SQL->Clear(); ClientMainForm->ZQAttachment->SQL->Add("Set NAMES latin1"); ClientMainForm->ZQAttachment->ExecSQL(); ClientMainForm->ZQAttachment->SQL->Clear(); ZQAttachmentShow(); TMemoryStream *Stream = new TMemoryStream(); TJPEGImage *jpgstream = new TJPEGImage(); Stream = (TMemoryStream *)ClientMainForm->ZQAttachment->CreateBlobStream(ClientMainForm->ZQAttachment->FieldByName("image"), bmRead); Stream->Position = 0; jpgstream->LoadFromStream(Stream); ClientMainForm->Image1->Picture->Assign(jpgstream); Stream->Free(); jpgstream->Free(); ClientMainForm->ZQAttachment->ExecSQL(); ClientMainForm->ZQAttachment->SQL->Clear(); ClientMainForm->ZQAttachment->SQL->Add("Set NAMES big5"); ClientMainForm->ZQAttachment->ExecSQL(); 謝謝您熱心指導 發表人 - hsiehwei 於 2005/05/09 21:26:21引言: // ClientMainForm->ZQAttachment->ExecSQL(); //這行拿掉看看,因為OPEN就是執行SQL指令,是不是連續執行兩次查詢的關係? ----------------------------------------------- Creation is the fundation of promotion. 發表人 - stallion 於 2005/05/09 19:44:17拿掉以後,執行的時候發生SQL語法錯誤,一堆亂碼 請問這是什麼問題呢? |
本站聲明 |
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |