BlogGang.com : : mitrapap : Excel VBA with Firebird Database

...ส บ า ย ๆ ส ไ ต ล์ มื อ ไ ม่ PRO แ ถ ม ยั ง... LOWFESSIONAL ...

มิถุนายน 2561

ประยุกต์ใช้งานเอ็กเซล โดยใช้ firebird เป็นฐานข้อมูลที่เก็บไว้ Server

ขอบคุณเจ้าของบทความทุกท่านครับ

======================================================

การติดตั้งและตั้งค่าด่วนๆ

พร้อมสำหรับการเชื่อมต่อเบื้องต้น...

=====================================================
Add new user
gsec -user SYSDBA -password masterkey ---เรียก gsec ด้วยแอดมินก่อน
GSEC> add elvis -pw Aaron -fname Elvis -lname Presley
GSEC>quit
=====================================================

เปลี่ยนพาสของ SYSDBA ที่อยู่ในเครื่องอื่น (Windows server) "sally" to "hannover"

gsec -user SYSDBA -password masterkey -database sally:"C:Program FilesFirebirdsecurity.fdb" -modify sysdba -pw hannover

เปลี่ยนพาสที่เครื่องตัวเองgsec -user SYSDBA -password masterkeyGSEC> modify elvis -pw chuckGSEC> quitDelete user Joe on local servergsec -user SYSDBA -password masterkey -delete joe

=====================================================

ภาพรวมของโปรเจ็ค

1. ติดตั้ง firebird

2. เปลี่ยนพอร์ทของ Firebird จาก 3050 เป็น 3051 เนื่องจากมีการใช้งานอยู่ก่อนแล้ว

3. ตั้งค่า Forward port ใน router เพื่อชี้พอร์ท 3051 ไปยัง IP ของ Server

ในวินโดวส์เครื่องที่จะเข้าไปใช้งาน...

4. ติดตั้ง Firebird-2.5.8.27089_0_Win32.exe
5. ติดตั้ง Firebird_ODBC_2.0.5.156_Win32.exe

บันทึกประสบการณ์ การดึงข้อมูล Excel ประมาณ 2 หมื่นบรรทัด เข้าสู่ Firebird Database

แต่เดิมเคยบันทึกข้อมูลทีละเร็คคอร์ด ใช้แบบนี้มาตลอด

strSQL = "SELECT * FROM " & fdbTable
        'เพิ่มบรรทัดข้อมูล ต่อจากบรรทัดสุดท้ายที่มี
        oRs strSQL, oADOConn, adOpenKeyset, adLockOptimistic
        oRs.AddNew

เสร็จแล้วตามด้วย

oRs.Update
oRs.Close

เล่นเอาซะค่อนวัน!

เลยต้องค้นเว็บอย่างหนัก เผื่อจะมีทางบ้าง สุดท้ายก็ลองแบบนี้

oADOConn.Execute "INSERT INTO " & fdbTable & " ('' & fFieldname & '') VALUES ('' & VauleToWrite & '')"

*** หมายเหตุ เครื่องหมาย '' เป็นเครื่องหมายคำพูดทั้งหมด ไม่ใช่ ' 2 อัน

โดยวิ่งเก็บชื่อฟิลด์(คั่นด้วยคอมม่า) และวิ่งเก็บค่าที่จะเขียน(คั่นด้วยคอมม่า)

ปรากฏว่าใช้เวลาน้อยกว่าเดิมหลายเท่าตัว...

วดป ในแบบ RecordSet ต้องแปลงค่าเป็น Clng(fDate)

วดป ในแบบ oADOConn.Execute ต้องให้เป็นแบบนี้ ...

"'" & Year(fDate) & "-" & Month(fDate) & "-" & Day(fDate) & "'"

'ใช้บ่อย ชอบลืม

'หาเซลที่มีตัวอักษร aaaa ในบรรทัดที่ 5 ได้ค่าเป็นเลขคอลัมน์

'0 ค่าตรงทั้งเซล, 1 บางส่วนของคำ
Application.Match('aaaa", rows(5),0)

'หาเซลที่มีค่า aaaa จากคอลัมน์ F ได้ค่าเป้นเลขบรรทัด

Application.Match('aaaa", columns('F'),0)

'การใช้ Find และ FindNext จะเริ่มต้นจากค่าที่ 2 ของเร้นจ์ที่ค้นเสมอ เพื่อให้การค้นเริ่มจากค่าแรก(ตามที่ควรจะเป็น)ก่อน จึงให้ไปตั้งหลักที่เซลสุดท้ายของเร้นจ์เสียก่อน

with activesheet.Usedrange

      Set xFind = .Find('fdbStyle", LookAt:=xlWhole, After:=.Item(.Count))
เพื่อบังคับให้ค้นหาต่อจาก cell สุดท้ายใน UsedRange (คือวนมาเริ่มที่ Cell แรก)

'แสดงชื่อฟิลด์และรายละเอียดของฟิลด์(ทดลองกับ firebird)

strSQL = "SELECT R.RDB$FIELD_NAME AS field_name, CASE F.RDB$FIELD_TYPE " & _
                                "WHEN 7 THEN 'SMALLINT' " & _
                                "WHEN 8 THEN 'INTEGER' " & _
                                "WHEN 9 THEN 'QUAD' " & _
                                "WHEN 10 THEN 'FLOAT' " & _
                                "WHEN 11 THEN 'D_FLOAT' " & _
                                "WHEN 12 THEN 'DATE' " & _
                                "WHEN 13 THEN 'TIME' " & _
                                "WHEN 14 THEN 'CHAR' " & _
                                "WHEN 16 THEN 'INT64' " & _
                                "WHEN 27 THEN 'DOUBLE' " & _
                                "WHEN 35 THEN 'TIMESTAMP' " & _
                                "WHEN 37 THEN 'VARCHAR' " & _
                                "WHEN 40 THEN 'CSTRING' " & _
                                "WHEN 261 THEN 'BLOB' " & _
                        "Else 'UNKNOWN' " & _
                        "END AS field_type, " & _
                        "F.RDB$FIELD_LENGTH AS field_length, CSET.RDB$CHARACTER_SET_NAME AS field_charset FROM RDB$RELATION_FIELDS R " & _
                                "LEFT JOIN RDB$FIELDS F ON R.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME " & _
                                "LEFT JOIN RDB$CHARACTER_SETS CSET ON F.RDB$CHARACTER_SET_ID = CSET.RDB$CHARACTER_SET_ID " & _
                                "WHERE R.RDB$RELATION_NAME= '" & UCase(fdbTable) & "' ORDER BY R.RDB$FIELD_POSITION"

--------------------------------------------------------------------------------------------------

แสดงข้อมูลโดยเอา เลขเดือน และ เลขปี ไปเปรียบเทียบ

strSql = "SELECT * FROM " & dbTable & " WHERE fhrefercode LIKE '" & empNum & "/%' AND NOT fhdateaction IS NULL" & _
" AND EXTRACT(MONTH FROM fhdateaction)=" & mNum & " AND EXTRACT(YEAR FROM fhdateaction)=" & yNum

ความสำคัญอยู่ที่ การเอาเลขเดือนและเลขปีออกมาจากค่าของ date
EXTRACT(MONTH FROM fhdateaction)
EXTRACT(YEAR FROM fhdateaction)
--------------------------------------------------------------------------------------------------

สร้างไฟล์ฐานข้อมูลใหม่ในเครื่อง(สร้างในคอมมอนไลน์ของisql)
ที่ */bin พิมพ์ isql

SQL>CREATE DATABASE ‘C:|Folder|datafile.fdb’ user ‘SYSDBA’ password ‘xxxxxxxx’ DEFAULT CHARACTER SET TIS620;

สร้างไฟล์ฐานข้อมูลใหม่ในเซิฟเวอร์ ผ่านเน็ต (สร้างในคอมมอนไลน์ของisql)
SQL>CREATE DATABASE ‘abcd.serveftp.com/3051:C:|Folder|datafile.fdb’ user ‘SYSDBA’ password ‘xxxxxxxx’ DEFAULT CHARACTER SET TIS620;

ตรวจสอบไฟล์ที่สร้างใหม่เมื่อครู่
SQL>CONNECT “abcd.serveftp.com/3051:C:|User|Administrator|Documents|datafile.fdb” user ‘SYSDBA’ password ‘xxxxxxxx’;

ระบบจะรายงานออกมาโดยไม่มีข้อผิดพลาดแสดงว่าการสร้างไฟล์ถูกต้อง

ออกจาก isql ให้พิมพ์ exit;

อย่าลืม...ท้ายคำสั่งต้องมี ; เสมอ

---------------------------------------------------------------------------------------------------
แสดงรายชื่อเทเบิ้ล(Table List)ของงานที่เราสร้างไว้ในระบบ

        FirebirdDbConnect
        'ตรวจสอบจากรายชื่อเทเบิ้ลในฐานข้อมูล
        Set TablesSchema = oADOConnSchema(adSchemaTables)
        Do While Not TablesSchema.EOF
                '+++++
                'ชื่อเทเบิ้ลที่สร้างใหม่ จะไม่มี $
                'ชื่อเทเบิ้ลที่มี $ จะเป็นเทเบิ้ลของระบบฐานข้อมูล
                '+++++
                If TablesSchema("TABLE_NAME") Like "*$*" = False Then
                        aRow = aRow + 1
                        With Range("B" & aRow)
                                .Formula = TablesSchema("TABLE_NAME")
                        End With
                End If
                TablesSchema.MoveNext
        Loop
        fdbReset
--------------------------------------------------------------------------------------------

REPAIR .FDB DATABASE จากที่นี่ https://ib-aid.com/ru/articles/how-to-repair-a-corrupt-firebird-database/
มีงานในที่ทำงานที่ใช้ firebird แล้วเข้าโปรแกรมไม่ได้ ฟ้องว่า Microsoft Visual foxpro has stopped working
ก็โทษนั่นนี่อยู่นาน สุดท้ายพบว่าไฟล์ .FDB เสีย ไม่เคยทำ backup ไว้เลย เสี่ยงใช้วิธีตามที่ค้นพบในเว็บดู

เป็นการสำรวจและซ่อมแซมเพื่อสร้างไฟล์แบ็คอัพ จากนั้นจึงกู้คืนจากไฟล์แบ็คอัพอีกที

Stop Service เซิร์ฟเวอร์ Firebird แล้วคัดลอกข้อมูลไปไว้ที่อื่นกันพลาด จากนั้น Start Service ใหม่และรันคำสั่งต่อไปนี้:

(ให้ cmd เข้าไปก่อน เพราะต้องทำใน command line แล้วเข้าไปที่ bin ของ firebird ใน programfiles)

  gfix.exe -v -full -user SYSDBA -pass masterkey pathเต็มและชื่อไฟล์database  
  gfix.exe -mend -user SYSDBA -pass masterkey pathเต็มและชื่อไฟล์database  
  gbak -b -v -user SYSDBA -pass masterkey pathเต็มและชื่อไฟล์database pathและชื่อไฟล์ที่จะbackup  

*** พยายามเรียกใช้งานทั้งหมดแม้ว่าจะมีข้อผิดพลาดเกิดขึ้นก็ตาม ***       
ถ้าการสำรองข้อมูลเสร็จสมบูรณ์ ให้เรียกคืนฐานข้อมูล:      

  gbak -c -v -user SYSDBA -pass masterkey pathและชื่อไฟล์ที่backup pathเต็มและชื่อไฟล์์database

จบ กลับมาใช้ได้เหมือนเดิม...

- กำหนด Type ของ Field ที่ต้องการให้จัดเรียงภาษาไทยได้ถูกต้อง
char(50) collate tis620_unicode

- นำข้อความมากกว่า 1 ฟิลด์มาต่อกัน
field1 || field2 ต่อตรงๆ
TRIM(field3) || '/' || TRIM(field2) ต่อแบบมีอักขระใดๆเชื่อม

* * * ไม่เกี่ยวกับ firebird แต่มาบันทึกไว้กันลืม * * *
ใส่สูตรในเซล เพื่อเรียกไปยัง google map เพื่อค้นหาตามข้อความ กี่จุดก็ได้ / ไปเรื่อยๆ
=HYPERLINK("https://www.google.com/maps/dir/สถานที่1/สถานที่2/สถานที่3","ข้อความที่แสดง")

rs Source, Connection, CursorType, LockType, Option

Source คือแหล่งที่มาของข้อมูลส่วนมากจะเป็นคำสั่ง SQL
Connection คือการติดต่อฐานข้อมูล ซึ่งปกติจะอยู่ในรูปแบบของ Object Connection
CursorType นั้นจะหมายถึงชนิดการเลื่อนของ pointer
LockType นั้นจะเป็นชนิดการ Lock ของ Cursor ซึ่งมีความสัมพันธ์โดยตรงกับ CursorType

=======================

CursorType
-------------------------------------
adOpenForwardOnly มีค่า CursorType เท่ากับ 0 (ถ้าเราไม่กำหนดมันก็จะถือเป็น 0 คือค่าตั้งต้น )
adOpenKeySet มีค่า CursorType เท่ากับ 1
adOpenDynamic มีค่า CursorType เท่ากับ 2
adOpenStatic มีค่า CursorType เท่ากับ 3

adOpenForwardOnly
การกำหนด CursorType เป็นค่านี้ (ค่า 0 )จะเป็นการให้ pointer เลื่อนข้อมูลไปข้างหน้าเพียงอย่างเดียว ไม่สามารถย้อนหลังได้ กล่าวคือข้อมูลที่แสดงจะเริ่มต้นจากข้อมูลที่ 1 แล้วจึงไปข้อมูลที่ 2 จะเริ่มจาก 2 แล้วย้อนมา 1 ไม่ได้
การเลื่อน pointer ในลักษณะนี้จะทำได้เร็วและไม่สิ้นเปลืองหน่วยความจำของ Server มากนักเนื่องจากไม่มีการถ่ายโอนข้อมูลมาเก็บไว้ในหน่วยความจำ (RAM) ดังนั้วิธีการนี้เหมาะสำหรับการแสดงข้อมูลเพียงอย่างเดียว

adOpenKeySet
การกำหนดเลื่อน pointer แบบนี้จะเป็นการกำหนดให้ pointer เลื่อนไปมาได้อย่างอิสระทั้งไปข้างหน้า และไปข้างหลัง หรือจะเลื่อนจากหลังก่อน แล้วค่อยมาหน้าก็ได้ แล้วแต่ว่าเราจะกำหนดให้ pointer ไปอยู่ที่ไหน เมื่อใดก็ตามที่มีผู้ใช้งานคนอื่นเข้ามาเปลี่ยนแปลงข้อมูลในฐานข้อมูลเราก็จะเห็นการเปลี่ยนแปลงนั้นด้วย
แต่ถ้าเป็นการเพิ่มหรือลดข้อมูลแล้ว เราจะไม่เห็นการเปลี่ยนแปลงนั้นสาเหตุเพราะตำแหน่งของ pointer ได้ถูกกำหนดไว้ตั้งแต่ตอนติดต่อฐานข้อมูลทีแรกแล้ว
ดังนั้นถ้าเราอยากเห็นการเปลี่ยนแปลงเราต้องใช้ Method Requery เพื่อเรียกดูข้อมูลใหม่อีกครั้งหนึ่ง

adOpenDynamic
การกำหนดเลื่อน pointer แบบนี้จะเป็นการกำหนดให้ pointer เลื่อนไปมาได้อย่างอิสระทั้งไปข้างหน้า และไปข้างหลัง หรือจะเลื่อนจากหลังก่อน แล้วค่อยมาหน้าก็ได้ แล้วแต่ว่าเราจะกำหนดให้ pointer ไปอยู่ที่ไหน เมื่อใดก็ตามที่มีผู้ใช้งานคนอื่นเข้ามาเปลี่ยนแปลงข้อมูลในฐานข้อมูลเราก็จะเห็นการเปลี่ยนแปลงนั้นด้วย
และยิ่งไปกว่านั้นเรายังสามารถมองเห็นข้อมูลปัจจุบันได้ตลอดเวลาไม่ว่าข้อมูลจะเพิ่มหรือลดลง ที่กระทำโดยผู้ใช้รายอื่นก็ตาม

สาเหตุก็เพราะว่าการเลื่อ point แบบนี้จะมีการถ่ายโอนข้อมูลไปไว้ที่หน่วยความจำก่อน และมีการอัพเดทข้อมูลใหม่เรื่อยๆในหน่วยความจำเสมอ แล้วจึงจะแสดงให้เราดูอีกที
แต่ก็มีข้อเสียตรงที่สิ้นเปลืองหน่วยความจำมากนั่นเอง ซึ่งการกำหนด CursorType ในลักษณะนี้เหมาะกับกรณีที่ต้องอัพเดทข้อมูลบ่อยๆ

adOpenStatic
การกำหนดเลื่อน pointer แบบนี้จะเป็นในลักษณะเดียวกันกับแบบ adOpenDynamic เพราะมีการถ่ายโอนข้อมูลเข้าห่วยความจำ
แต่ก็มีจุดที่แตกต่างกันอย่างสิ้นเชิงก็คือ การกำหนดในแบบนี้จะไม่มีการอัพเดทข้อมูลใหม่ๆลงในหน่วยความจำเลย ดังนั้นข้อมูลที่ได้ก็จะเป็นข้อมูลเดิมๆ ที่ไม่ มีการเปลี่ยนแปลงใดๆ อย่างไรก็ตามถ้าเราต้องการดูข้อมูลใหม่ เราก็ใช้ Method Reqyery เช่นเดียวกับแบบ adOpenKeySet

========================

LockType
---------------------------------------
adLockReadOnly มีค่า LockType เท่ากับ 1 (ถ้าเราไม่กำหนดมันก็จะถือเป็น 1 คือค่าตั้งต้น )
adLockPessimistic มีค่า LockType เท่ากับ 2
adLockOptimistic มีค่า LockType เท่ากับ 3
adLockBatchOptimistic มีค่า LockType เท่ากับ 4

adLockReadOnly
เมื่อกำหนดวิธีการปกป้องข้อมูลในรูปแบบนี้แล้ว ผู้ใช้จะสามารถอ่านได้เพียงอย่างเดียว ไม่สามารถเพิ่ม ลบ หรือแก้ไขข้อมูลใดๆได้

adLockPessimistic
เมื่อกำหนดวิธีการปกป้องข้อมูลในรูปแบบนี้แล้วผู้ใช้จะสามารถเปลี่ยนแปลงแก้ไขข้อมูลได้ แต่จะทำได้คลั้งละเพียงคนเดียวเท่านั้น คือไม่สามรถเปลี่ยนแปลงได้พร้อมๆกัน

adLockOptimistic
เมื่อกำหนดวิธีการปกป้องข้อมูลในรูปแบบนี้แล้วผู้ใช้จะสามารถเปลี่ยนแปลงแก้ไขข้อมูลได้ โดยสามารถกระทำพร้อมกันได้หลายๆคน แต่การแก้ไขจะเป็นไปทีละ 1 รายการ หรือ 1 Record เท่านั้น ถ้ามีการเปลี่ยนแปลงข้อมูลที่ 1 แล้ว pointer เลื่อนไปตำแหน่งที่ 2 เพื่อแก้ไขต่อไป ADO ก็จะเรียก Method Update ขึ้นมาให้เองเพื่อจัดเก็บข้อมูลที่แก้ไขลงฐานข้อมูลก่อน

adLockBatchOptimistic
วิธีการปกป้องข้อมูลในรูปแบบนี้จะคล้ายกับวิธี adLockOptimistic แต่ว่าวิธีนี้จะสามารถแก้ไขข้อมูลได้ทีละมากๆ จึงเหมาะที่จะใช้งานในกรณีบางอย่างเช่น
ต้องการแก้ไขรหัสสมาชิกที่ขึ้นต้นด้วย A ก็สามารถเขียนโปรแกรมวนลูปแก้ไขในฐานข้อมูลก่อน จากนั้นจึงทำการอัพเดทลงฐานข้อมูลทีเดียวเลย