PowerBuilder: DBMS not supported in your current installation

เห็น error DBMS not supported in your current installation ในขั้นตอนที่ connect database sql server ตอนแรกเข้าใจว่าตอนที่ติดตั้งไม่ได้ลงพวก adodb, jdbc, odbc driver ลอง install ใหม่ก็เหมือนเดิม กี่ครั้งก็เหมือนเดิม

รุ่นพี่บอกให้ลง sql server 2000 หลังจากนั้นก็ connect ได้ปกติเลย เข้าใจว่าในการติดตั้งมันจะลงไดร์เวอร์ให้เป็นตัวที่ยังใช้โปรโตคอลเดี่ยวกับตัว PB8

โปรแกรมจัดการ mysql ง่ายๆ เร็วๆ

การจัดการ database คู่บุญ PHP อย่าง MySql หรือ mariadb ร้อยทั้งร้อย และทุกโฮสต์เตรียมเอาไว้ให้ใช้คือ phpMyAdmin แต่มันตัวอ้วนใหญ่ขึ้นทุกๆปี ตามลูกเล่นที่มากขึ้น ถ้ารีบเอางานขึ้นหรือเตรียมไว้เป็นช่องทางฉุกเฺฉินขอแนะนำ Adminer เป็นมายเอสคิวแอล management ที่ตัวเล็กกว่าเยอะ ฟรี ที่สำคัญคือมีไฟล์แค่ไฟล์เดียว

การติกตั้งแค่โหลดมากจากเว็บ เปลี่ยนชื่อซะหน่อยเพื่อความปลอดภัย ให้เป็นชื่อแบบที่เรารู้อยู่แค่คนเดียว แล้ว ftp ขึ้นไป เรียกใช้ได้เลย อาจจะไม่ถนัดเท่าตัวเดิมแต่การติดตั้ง script import export data แบ็คอัพแก้ตารางก็ทำได้ดี สะดวกพอใช้

สร้าง Data Dictionary แค่คลิก

สร้าง Data Dictionary ใน sql server แบบง่ายๆ เพียงแค่คลิกเท่านั้น บทความนี้เป็นเวอร์ชั้นปรับปรุงของ สร้าง Data Dictionary แบบด่วนๆ ให้ทำได้ง่ายขั้น

เปิด sql server management studio เลือก database ที่ต้องการ คลิกขวา New Query
copy[code language=”sql” title=”generates a full data dictionary for all tables in a SQL Server by Pitt Phunsanit”]
SET ANSI_PADDING OFF;
GO

SET NOCOUNT ON;
GO

SET TEXTSIZE 8192;
GO

PRINT ‘<!doctype html>’;
PRINT ‘<html lang="th">’;
PRINT ‘<head>’;
PRINT ‘<meta charset="utf-8" />’;
PRINT ‘<title>script generates a full data dictionary for all tables in a SQL Server by Pitt Phunsanit</title>’;
PRINT ‘<style>table {background: #7ba0cd;border-collapse: collapse;border-left: solid 1px #7ba0cd;border-right: solid 1px #7ba0cd;font-family: "Calibri", "sans-serif";font-size: 11pt;width: 100%;}table tr {background: #FFFFFF;border-bottom: solid 1px #7ba0cd;}table thead tr {background-color: #4e80bc;color: white;}table tbody tr:nth-of-type(odd) {background: #d2deed;border-bottom: solid 1px #FFFFFF;box-shadow: 0 2px 0 -1px #7ba0cd;}table tbody tr.case,table tfoot tr {background: #d7d7d7;}</style>’;
PRINT ‘</head>’;
PRINT ‘<body>’;
PRINT ‘<h1>’ + DB_NAME() + ‘</h1>’;
PRINT ‘<h3>create date : ‘ + convert(VARCHAR(10), GETDATE(), 111) + ‘</h3>’;

DECLARE @record CURSOR;
DECLARE @table VARCHAR(128);
DECLARE @object_id INT;
DECLARE @schema_id INT;

SET @record = CURSOR
FOR
SELECT [name] AS [table], [object_id], [schema_id]
FROM sys.tables AS t
WHERE type_desc = ‘USER_TABLE’

OPEN @record

FETCH NEXT
FROM @record
INTO @table, @object_id, @schema_id

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT CAST(‘<br><table><caption><b>’ + @table + ‘</b>’ + ISNULL(‘ : ‘+RTRIM(CAST(ep.[value] AS NVARCHAR(4000))), ”) + ‘</caption>’ AS NTEXT)
,CAST(‘<thead><tr>’ + ‘<td style="width:100px;"><b>Primary Key</b></td>’ + ‘<td style="width:200px;"><b>Foreign key</b></td>’ + ‘<td style="width:400px;"><b>Column Name</b></td>’ + ‘<td><b>Description</b></td>’ AS NTEXT)
,CAST(‘<td style="width:100px;"><b>Data Type</b></td>’ + ‘<td style="width:100px;"><b>Allow Nulls</b></td>’ + ‘<td style="width:100px;"><b>Identity</b></td>’ + ‘<td style="width:100px;"><b>Default Value</b></td>’ + ‘</tr></thead><tbody>’ AS NTEXT)
FROM sys.schemas AS s
— get description of table, if available
LEFT OUTER JOIN sys.extended_properties AS ep ON ep.major_id = @object_id
AND ep.minor_id = 0
AND ep.NAME = ‘MS_Description’
AND ep.class = 1
WHERE s.[schema_id] = @schema_id
AND NOT EXISTS (
SELECT *
FROM sys.extended_properties ms
WHERE ms.major_id = @object_id
AND ms.minor_id = 0
AND ms.class = 1
AND ms.[name] = ‘microsoft_database_tools_support’
)

UNION ALL

SELECT ” AS html
,'<tr><td>’ + CASE
WHEN pk.column_id IS NOT NULL
THEN ‘PK’
ELSE ”
END + ‘</td><td>’ + CASE
WHEN fk.primary_table IS NOT NULL
THEN fk.primary_table + ‘.’ + fk.primary_column
ELSE ”
END + ‘</td><td>’ + c.[name] + ‘</td><td>’ + ISNULL(RTRIM(CAST(ep.[value] AS NVARCHAR(4000))), ”) + ‘</td><td>’ + CASE
WHEN uty.[name] IS NOT NULL
THEN uty.[name]
ELSE ”
END + CASE
WHEN uty.[name] IS NOT NULL
AND sty.[name] IS NOT NULL
THEN ‘ (
‘
ELSE ”
END + CASE
WHEN sty.[name] IS NOT NULL
THEN sty.[name]
ELSE ”
END + CASE
WHEN sty.[name] IN (
‘CHAR’
,’NCHAR’
,’VARCHAR’
,’NVARCHAR’
,’BINARY’
,’VARBINARY’
)
THEN ‘ (
‘ + CASE
WHEN c.max_length = – 1
THEN ‘max’
ELSE CASE
WHEN sty.[name] IN (
‘NCHAR’
,’NVARCHAR’
)
THEN CAST(c.max_length / 2 AS VARCHAR(MAX))
ELSE CAST(c.max_length AS VARCHAR(MAX))
END
END + ‘
) ‘
WHEN sty.[name] IN (
‘NUMERIC’
,’DECIMAL’
)
THEN ‘ (
‘ + CAST(c.precision AS VARCHAR(MAX)) + ‘
,’ + CAST(c.scale AS VARCHAR(MAX)) + ‘
) ‘
ELSE ”
END + CASE
WHEN uty.[name] IS NOT NULL
AND sty.[name] IS NOT NULL
THEN ‘
) ‘
ELSE ”
END + ‘</td><td>’ + CASE
WHEN c.is_nullable = 1
THEN ‘Y’
ELSE ”
END + ‘</td><td>’ AS html1
,CASE
WHEN c.is_identity = 1
THEN ‘Y’
ELSE ”
END + ‘</td><td>’ + ISNULL(dc.[definition], ”) + ‘</td></tr>’ AS html2
FROM sys.columns AS c
INNER JOIN sys.schemas s ON s.[schema_id] = @schema_id
— get name of user data type
LEFT OUTER JOIN sys.types uty ON uty.system_type_id = c.system_type_id
AND uty.user_type_id = c.user_type_id
AND c.user_type_id <> c.system_type_id
— get name of system data type
LEFT OUTER JOIN sys.types sty ON sty.system_type_id = c.system_type_id
AND sty.user_type_id = c.system_type_id
— get description of column, if available
LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id = @object_id
AND ep.minor_id = c.column_id
AND ep.[name] = ‘MS_Description’
AND ep.[class] = 1
— get default’ s code TEXT
LEFT OUTER JOIN sys.default_constraints dc ON dc.parent_object_id = @object_id
AND dc.parent_column_id = c.column_id
— check for inclusion in primary key
LEFT OUTER JOIN (
SELECT ic.column_id
,i.[object_id]
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON ic.index_id = i.index_id
AND ic.[object_id] = i.[object_id]
WHERE i.is_primary_key = 1
) pk ON pk.column_id = c.column_id
AND pk.[object_id] = @object_id
— check for inclusion in foreign key
LEFT OUTER JOIN (
SELECT CASE
WHEN s.[name] = ‘dbo’
THEN pk.[name]
ELSE s.[name] + ‘.’ + pk.[name]
END AS primary_table
,pkc.[name] AS primary_column
,fkc.parent_object_id
,fkc.parent_column_id
FROM sys.foreign_keys fk
INNER JOIN sys.tables pk ON fk.referenced_object_id = pk.[object_id]
INNER JOIN sys.schemas s ON s.[schema_id] = pk.[schema_id]
INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.[object_id]
AND fkc.referenced_object_id = pk.[object_id]
INNER JOIN sys.columns pkc ON pkc.[object_id] = pk.[object_id]
AND pkc.column_id = fkc.referenced_column_id
) fk ON fk.parent_object_id = @object_id
AND fk.parent_column_id = c.column_id
WHERE c.object_id = @object_id
AND NOT EXISTS (
SELECT *
FROM sys.extended_properties ms
WHERE ms.major_id = @object_id
AND ms.minor_id = 0
AND ms.class = 1
AND ms.[name] = ‘microsoft_database_tools_support’
)

UNION ALL

SELECT ” AS html
,” AS html1
,'</tbody></table><br><hr><br>’ AS html2

FETCH NEXT
FROM @record
INTO @table, @object_id, @schema_id
END

CLOSE @record

DEALLOCATE @record

PRINT ‘<a href="https://pitt.plusmagi.com">PlusMagi.com by Pitt Phunsanit ([email protected])</a>’;
PRINT ‘</body>’;
PRINT ‘</html>’;
[/code]
คลิกปุ่ม Results to file หรือ Ctrl + Shift + F กด Excute หรือ F5 แล้วเซฟเป็นไฟล์ ที่ต้องการแต่ลงท้ายด้วย .html เพราะว่าผลลัพธ์ที่ได้จะเป็นเว็บครับ
เปิดไฟล์ที่ส่งออกออกมาแล้วคัดลอกใส่โปรแกรมเวิร์ดหรือเอ็กเซลได้เลยครับ

ตัวอย่างผลงานที่เร็วกว่ามาม่า

test

create date : 2016/07/14

—————————————————————————————————————————————————————————————————————————————————————- —————————————————————————————————————————————————————————————————————————————————————- —————————————————————————————————————————————————————————————————————————————————————-

**areas_cities**
Primary Key	Foreign key	Column Name	Description	Data Type	Allow Nulls	Identity	Default Value
PK		city_id		int
		status		tinyint	Y
		geo_id	ภูมิภาค	int			((0))
		province_id		int			((0))
		amphur_code		varchar ( 4 )	Y

**areas_cities_lang**
Primary Key	Foreign key	Column Name	Description	Data Type	Allow Nulls	Identity	Default Value
PK	areas_cities.city_id	city_id		int
PK		language		char ( 2 )
		name		nvarchar ( 600 )

PlusMagi.com by Pitt Phunsanit ([email protected])
ดูไม่สวย แต่จริงๆ มันมีสีสันด้วยนะครับ ไม่ใช่ตารางโล้นๆ

เวลานำไปให้ให้เครดิตผมด้วยนะครับ ถ้าหากเจอข้อบกพร่องแจ้งผมด้วยนะครับ จะได้ปรับปรุงให้คนอื่นใช้กันต่อไป “สังคมแห่งการแบ่งปันครับ”

สร้าง Data Dictionary แบบด่วนๆ

Updateอ่าน สร้าง Data Dictionary แค่คลิก แทนครับ

ต้องเขียน Data Dictionary ให้โครงการที่ทำอยู่ แต่มันมีหลายตารางมาก และที่สำคัญคือ แต่ละตารางจะมี relation ยุบยับเต็มไปหมด copy โครงสร้างมาแต่ละตารางมาวางในเอ็กส์เซล์ที่ละตัวก็ตาลาย พลาดได้ง่ายๆเลย

วิธีที่คิดออกคือ มันต้องมีโปรแกรมที่ช่วยงานนี้ได้ซิ เราไม่ใช่คนแรกที่ต้องเขียน Data Dictionary ซะหน่อย มีจริงๆ แต่แพง ไม่ก็ดูแปลกๆ เปลี่ยนไปใช้อีกวิธีคือ ทำไมไม่ให้ SQL ทำให้ละ search เจอ SQL Server Data Dictionary แต่มันก็ดูยาก เลยต้องเอามาเขียนใหม่ซะเอง

query ที่เขียนขึ้นมาใหม่คือ[code language=”sql”]
SET ANSI_DEFAULTS OFF;
GO

SET NOCOUNT ON;
GO

SET TEXTSIZE 8192;
GO

PRINT ‘<HTML><body>’
PRINT ‘<!doctype html>’
PRINT ‘<html>’
PRINT ‘<head>’
PRINT ‘<meta charset="utf-8">’
PRINT ‘<title>script generates a full data dictionary for all tables in a SQL Server by Pitt Phunsanit</title>’
PRINT ‘<style>table {background: #7ba0cd;border-collapse: collapse;border-left: solid 1px #7ba0cd;border-right: solid 1px #7ba0cd;font-family: "Calibri", "sans-serif";font-size: 11pt;width: 100%;}table tr {background: #FFFFFF;border-bottom: solid 1px #7ba0cd;}table thead tr {background-color: #4e80bc;color: white;}table tbody tr:nth-of-type(odd) {background: #d2deed;border-bottom: solid 1px #FFFFFF;box-shadow: 0 2px 0 -1px #7ba0cd;}table tbody tr.case,table tfoot tr {background: #d7d7d7;}</style>’
PRINT ‘</head>’
PRINT ‘<body>’
PRINT ‘<h1>’ + DB_NAME() + ‘</h1>’
PRINT ‘<h3>create date : ‘ + convert(VARCHAR(10), GETDATE(), 111) + ‘</h3>’

SELECT html
,html1
,html2
FROM (
SELECT s.[name] AS [schema]
,CASE
WHEN s.[name] = ‘dbo’
THEN t.[name]
ELSE s.[name] + ‘.’ + t.[name]
END AS [table]
,NULL AS column_id
,CAST(‘<table><cption><b>’ + t.[name] + ‘</b>’ + ISNULL(‘ : ‘+RTRIM(CAST(ep.[value] AS NVARCHAR(4000))), ”) + ‘</caption>’ AS NTEXT) AS html
,CAST(‘<thead>’ + ‘<td style="width:100px;"><b>Primary Key</b></td>’ + ‘<td style="width:200px;"><b>Foreign key</b></td>’ + ‘<td style="width:400px;"><b>Column Name</b></td>’ + ‘<td><b>Description</b></td>’ AS NTEXT) AS html1
,CAST(‘<td style="width:100px;"><b>Data Type</b></td>’ + ‘<td style="width:100px;"><b>Allow Nulls</b></td>’ + ‘<td style="width:100px;"><b>Identity</b></td>’ + ‘<td style="width:100px;"><b>Default Value</b></td>’ + ‘</thead><tbody>’ AS NTEXT) AS html2
FROM sys.tables t
INNER JOIN sys.schemas s ON s.[schema_id] = t.[schema_id]
— get description of table, if available
LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id = t.[object_id]
AND ep.minor_id = 0
AND ep.NAME = ‘MS_Description’
AND ep.class = 1
WHERE t.is_ms_shipped = 0
AND NOT EXISTS (
SELECT *
FROM sys.extended_properties ms
WHERE ms.major_id = t.[object_id]
AND ms.minor_id = 0
AND ms.class = 1
AND ms.[name] = ‘microsoft_database_tools_support’
)

UNION ALL

SELECT s.[name] AS [schema]
,CASE
WHEN s.[name] = ‘dbo’
THEN t.[name]
ELSE s.[name] + ‘.’ + t.[name]
END AS [table]
,c.column_id
,” AS html
,'<tr><td>’ + CASE
WHEN pk.column_id IS NOT NULL
THEN ‘PK’
ELSE ”
END + ‘</td><td>’ + CASE
WHEN fk.primary_table IS NOT NULL
THEN fk.primary_table + ‘.’ + fk.primary_column
ELSE ”
END + ‘</td><td>’ + c.[name] + ‘</td><td>’ + ISNULL(RTRIM(CAST(ep.[value] AS NVARCHAR(4000))), ”) + ‘</td><td>’ + CASE
WHEN uty.[name] IS NOT NULL
THEN uty.[name]
ELSE ”
END + CASE
WHEN uty.[name] IS NOT NULL
AND sty.[name] IS NOT NULL
THEN ‘ (
‘
ELSE ”
END + CASE
WHEN sty.[name] IS NOT NULL
THEN sty.[name]
ELSE ”
END + CASE
WHEN sty.[name] IN (
‘CHAR’
,’NCHAR’
,’VARCHAR’
,’NVARCHAR’
,’BINARY’
,’VARBINARY’
)
THEN ‘ (
‘ + CASE
WHEN c.max_length = – 1
THEN ‘max’
ELSE CASE
WHEN sty.[name] IN (
‘NCHAR’
,’NVARCHAR’
)
THEN CAST(c.max_length / 2 AS VARCHAR(MAX))
ELSE CAST(c.max_length AS VARCHAR(MAX))
END
END + ‘
) ‘
WHEN sty.[name] IN (
‘NUMERIC’
,’DECIMAL’
)
THEN ‘ (
‘ + CAST(c.precision AS VARCHAR(MAX)) + ‘
,’ + CAST(c.scale AS VARCHAR(MAX)) + ‘
) ‘
ELSE ”
END + CASE
WHEN uty.[name] IS NOT NULL
AND sty.[name] IS NOT NULL
THEN ‘
) ‘
ELSE ”
END + ‘</td><td>’ + CASE
WHEN c.is_nullable = 1
THEN ‘Y’
ELSE ”
END + ‘</td><td>’ AS html1
,CASE
WHEN c.is_identity = 1
THEN ‘Y’
ELSE ”
END + ‘</td><td>’ + ISNULL(dc.[definition], ”) + ‘</td></tr>’ AS html2
FROM sys.columns c
INNER JOIN sys.tables t ON t.[object_id] = c.[object_id]
INNER JOIN sys.schemas s ON s.[schema_id] = t.[schema_id]
— get name of user data type
LEFT OUTER JOIN sys.types uty ON uty.system_type_id = c.system_type_id
AND uty.user_type_id = c.user_type_id
AND c.user_type_id <> c.system_type_id
— get name of system data type
LEFT OUTER JOIN sys.types sty ON sty.system_type_id = c.system_type_id
AND sty.user_type_id = c.system_type_id
— get description of column, if available
LEFT OUTER JOIN sys.extended_properties ep ON ep.major_id = t.[object_id]
AND ep.minor_id = c.column_id
AND ep.[name] = ‘MS_Description’
AND ep.[class] = 1
— get default’ s code TEXT
LEFT OUTER JOIN sys.default_constraints dc ON dc.parent_object_id = t.[object_id]
AND dc.parent_column_id = c.column_id
— check for inclusion in primary key
LEFT OUTER JOIN (
SELECT ic.column_id
,i.[object_id]
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON ic.index_id = i.index_id
AND ic.[object_id] = i.[object_id]
WHERE i.is_primary_key = 1
) pk ON pk.column_id = c.column_id
AND pk.[object_id] = t.[object_id]
— check for inclusion in foreign key
LEFT OUTER JOIN (
SELECT CASE
WHEN s.[name] = ‘dbo’
THEN pk.[name]
ELSE s.[name] + ‘.’ + pk.[name]
END AS primary_table
,pkc.[name] AS primary_column
,fkc.parent_object_id
,fkc.parent_column_id
FROM sys.foreign_keys fk
INNER JOIN sys.tables pk ON fk.referenced_object_id = pk.[object_id]
INNER JOIN sys.schemas s ON s.[schema_id] = pk.[schema_id]
INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.[object_id]
AND fkc.referenced_object_id = pk.[object_id]
INNER JOIN sys.columns pkc ON pkc.[object_id] = pk.[object_id]
AND pkc.column_id = fkc.referenced_column_id
) fk ON fk.parent_object_id = t.[object_id]
AND fk.parent_column_id = c.column_id
WHERE t.is_ms_shipped = 0
AND NOT EXISTS (
SELECT *
FROM sys.extended_properties ms
WHERE ms.major_id = t.[object_id]
AND ms.minor_id = 0
AND ms.class = 1
AND ms.[name] = ‘microsoft_database_tools_support’
)

UNION ALL

SELECT s.[name] AS [schema]
,CASE
WHEN s.[name] = ‘dbo’
THEN t.[name]
ELSE s.[name] + ‘.’ + t.[name]
END AS [table]
,999999 AS column_id
,” AS html
,” AS html1
,'</tbody></table><br><hr><br>’ AS html2
FROM sys.tables t
INNER JOIN sys.schemas s ON s.[schema_id] = t.[schema_id]
WHERE t.is_ms_shipped = 0
AND NOT EXISTS (
SELECT *
FROM sys.extended_properties ms
WHERE ms.major_id = t.[object_id]
AND ms.minor_id = 0
AND ms.class = 1
AND ms.[name] = ‘microsoft_database_tools_support’
)
) tmp
ORDER BY tmp.[schema]
,tmp.[table]
,tmp.column_id;

PRINT ‘<a href="https://pitt.plusmagi.com">PlusMagi.com by Pitt Phunsanit ([email protected])</a>’
PRINT ‘</body>’
PRINT ‘</html>’
[/code] query ตัวนี้มีอะไรพิเศษมากกว่าตัวอื่นๆ หลายอย่าง คือ

การเอาไปใช้ แทนที่จะดูผลลัพธ์ในรูปแบบ grid ต้องใช้มันในแบบ Results to Text โดยกด (Ctrl+T) แล้วเอาผลที่ได้ (เป็น code html) ให้เอาไป copy เป็นไฟล์ .html แล้วจะ copy ไปลง excel / word อีกต่อหนึ่งก็ได้ ถ้าใช้ grid ก็ได้ code เหมือนกันแต่จะไม่สวย เพราะพวกคำสั่ง print มันจะไม่ทำงาน
ผลลัพท์ต้องแบ่งเป็น column html, html1, html2 เพราะว่าถ้าผลลัพธ์ที่ออกมากมันยาว จะโดนตัดทิ้งไปเฉยๆ พยามแก้อยู่นาน นานกว่าเขียน code ของมันซะอีก โดยลองใช้ SET TEXTSIZE 8192; และ cast ร่วมกับตัวเลือก Maximum number of characters display in each column ก็ไม่ได้ผล เลยต้องซอย column ออกมาแทน
การเอา code ที่เอาไปใช้ ถ้ามีการจัด code ใหม่ต้องระวังเงื่อนไข อย่าง ‘CHAR’ ถ้าโดนจัดเป็น ‘ CHAR ‘ (มี space) มันจะไม่ทำงาน
การใช้งานถ้าจะให้ข้อมูลออกมาครบต้องทำ table relation และใส่ descriptions ให้ครบ ถึงจะออกมาสวยๆ ครบๆ เหมือนกัน
sub query ด้านล่างๆ จริงๆ คิดว่าถ้าให้มีประสิทธิภาพน่าจะเขียนวิธีอื่นๆ ได้ แต่ตอนนี้ขอใช้ quick and easy (dirty) ไว้ก่อน
ยังมีจุดที่จะแก้ในครั้งต่อไปอย่าง มันยังมี result header อยู่ทำให้มีเส้น ———————- กับ html html1 html2 เกินมาอยู่ แต่ลบเองไม่กี่วิก็ใช้ได้ละ เพราะงั้นปล่อยๆ มันไปก่อน
ใครแก้จุดไหนได้ รบกวนบอกผมด้วยละกันครับ

ตัวอย่างผลลัพธ์ที่ได้

DATABASE NAME

create date : 2016/05/30

areas_cities : อำเภอ

Primary Key	Foreign key	Column Name	Description	Data Type	Allow Nulls	Identity	Default Value
PK		city_id		int
		status		tinyint	Y
		geo_id	ภูมิภาค	int			(‘0’)
		province_id		int			(‘0’)
		amphur_code		varchar (4)	Y

areas_cities_lang : อำเภอ (แปล)

Primary Key	Foreign key	Column Name	Description	Data Type	Allow Nulls	Identity	Default Value
PK	areas_cities.city_id	city_id		int
PK		language		char (2)
		name		nvarchar (600)

จริงๆ มันมีสีสันด้วยนะ สวยจนเอาไปใช้ได้เลยไม่ต้องแต่งอะไรอีก

MySql ต่างกับ sql server

อ่านเจอคำถามนี้ใน facebook

อยากทราบว่าถ้าเราเปลี่ยน database จาก mysql เป็น sql server 2008 นอกจากส่วน connect แล้ว โค๊ดเก่าจากmysql จะสามารถใช้ได้เหมือนเดิมไหมครับ (ผมใช้ codeigniter เขียนครับ)

เลยลองยกตัวอย่างดู โดยใช้ PDO แทน function ของแต่ละ database จะได้เห็นได้ชัดเจน

schema_mysql.php สำหรับ mysql[code language=”php” title=”schema_mysql.php”]
<?php
$database = ‘yii2advanced’;

try {
$dbh = new PDO(
‘mysql:host=localhost;dbname=’ . $database,
‘root’,
”
);
} catch (PDOException $e) {
exit(‘Error!: ‘ . $e->getMessage());
}
$dbh->query(‘SET NAMES utf8’);

switch ($_GET[‘op’]) {
case ‘columns’:{
$table = $_GET[‘table’];

$sql = "SELECT `COLUMN_NAME` AS name
,`DATA_TYPE` AS type
,IFNULL(`CHARACTER_MAXIMUM_LENGTH`, 0) AS maxlength
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = ‘" . $table . "’;";
$sth = $dbh->prepare($sql);
$sth->execute();
$results = [];
if ($sth->rowCount() > 0) {
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
array_push($results, $row);
}
echo json_encode($results, JSON_NUMERIC_CHECK);
}
}break;
case ‘tables’:{
$sql = ‘SHOW TABLES IN ‘ . $database . ‘;’;
$sth = $dbh->prepare($sql);
$sth->execute();
$results = [];
if ($sth->rowCount() > 0) {
while ($row = $sth->fetch(PDO::FETCH_COLUMN)) {
array_push($results, $row);
}
}
echo json_encode($results);
}break;
}
[/code] และ schema_sqlsrv.php สำหรับ Microsoft sql server[code language=”php” title=”schema_sqlsrv.php”]
<?php
$database = ‘TPA’;

try {
$dbh = new PDO(
‘sqlsrv:Server=MAGI\SQLEXPRESS;Database=’ . $database
);
} catch (PDOException $e) {
exit(‘Error!: ‘ . $e->getMessage());
}
$dbh->query(‘SET CHARACTER_SET utf8_unicode_ci’);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

switch ($_GET[‘op’]) {
case ‘columns’:{
$table = $_GET[‘table’];

$sql = "SELECT [COLUMN_NAME] AS name
,[DATA_TYPE] AS type
,ISNULL([CHARACTER_MAXIMUM_LENGTH], 0) AS maxlength
FROM TPA.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘" . $table . "’;";
$sth = $dbh->prepare($sql, [PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL]);
$sth->execute();
$results = [];
if ($sth->rowCount() > 0) {
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
array_push($results, $row);
}
echo json_encode($results, JSON_NUMERIC_CHECK);
}
}break;
case ‘tables’:{
$sql = ‘SELECT [TABLE_NAME] FROM ‘ . $database . ‘.INFORMATION_SCHEMA.Tables;’;
$sth = $dbh->prepare($sql, [PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL]);
$sth->execute();
$results = [];
if ($sth->rowCount() > 0) {
while ($row = $sth->fetch(PDO::FETCH_COLUMN)) {
array_push($results, $row);
}
echo json_encode($results);
}
}break;
}
[/code]

ถึงจะพยามออกแบบ extension ตัวนี้ให้ทำงานได้ทุก database แต่แค่การ connection ก็ต่างกันแล้วแต่ละตัวต่างก็มี ตัวเลือกเพิ่มเติมไม่เหมือนกัน อย่าง การเลือกชุดตัวอักษร SET NAMES utf8 และ SET CHARACTER_SET utf8_unicode_ci ใน sql server (ภาษาไทย ภาษาเดียวก็มีให้เลือกกันเหนื่อยแล้วว่าจะใช้ตัวไหน ลองอ่าน LAB TEST : ความแตกต่างของ SQL THAI Collation แต่ละแบบ) จุดนี้พอจะเข้าใจได้เพราะมาจากคนละบริษัทกัน มาจากคนละความคิด

จุดต่อมา mySql ใช้ ` ` แต่ sql server ใช้ [ ] ในการบอกว่านี่คือชื่อเฉพาะนะ วิธีแก้ก็ง่ายๆ อย่าไปใส่มัน ยกเว้นว่าจะไปใช้ชื่อที่ตรงกับศัพท์สงวน (reserved words) เอาไว้ จำเป็นต้องใช้เพื่อให้ตัว database ไม่สับสน

ถ้าต้องต้องเปลี่ยนฐานข้อมูลก็หลีกเลี่ยงคำพวกนี้ไว้ก่อน

คำสั่งเกี่ยวกับโครงสร้างฐานข้อมูล ต่างเจ้าก็ต่างกัน ทั้งชื่อ type และ key บางครั้งชื่อเดียวกันแต่เก็บข้อมูลได้ไม่เท่ากัน การจะดึงข้อมูลโครงสร้างตารางออกมาก็ใช้คำสั่งต่างกัน ในตัวอย่าง switch case ทั้งสองตัวจะเป็นการ query ดูตารางทั้งหมดในฐานข้อมูลและชนิดของฟิลย์ในตารางทั้งหมด ซึ่งไม่ได้ไกล้เคียงกันเลย (นานๆ จะใช้ที ไม่ต้องไปจำมันก็ได้ ยกเว้นคุณจะเขียน curd ใช้เอง)

การใช้ Abstraction Layers แทนที่จะใช้ Vendor Specific Database Extensions หรือที่เรียกกันว่า native driver ที่เป็นของฐานข้อมูลแต่ละตัว เพราะว่าต้องการที่จะใช้คำสั่งเหมือนๆ กัน ไม่ต้องมาคิดว่าฐานข้อมูลแบบนี้ใช้ function ชื่อนี้ เวลาเปลี่ยน ชนิดฐานข้อมูลก็แค่เปลี่ยนตรง connection ก็พอแล้วไม่ต้องเขียนคำสั่งใหม่ แต่จริงๆ แล้วยังไม่มี Abstraction Layers ตัวไหน หรือของ framework ไหน ไม่ว่าจะเป็น codeigniter, yii, laravel แม้แต่ .net หรือ java ที่แปลง sql query แล้วสามารถทำงานได้เหมือนกันกับทุกๆ ตัว แค่ทำได้ 80% ของทั้งหมดในตัวอย่าง pdo ของ sql server ถ้าไม่ระบุ PDO::ATTR_CURSOR เพิ่มเข้าไป มันก็จะไม่รู้เลยว่า query ออกมามีผลลัพธ์ออกมารึเปล่า และถ้าสังเกตุจะมีคำสั่งที่ใช้ตรวจสอบค่า NULL และแทนด้วยค่าอื่นใน MySQL ใช้ IFNULL แต่ SQL SERVER ใช้ ISNULL การทำงาน โครงสร้างเหมือนกัน แต่เขียนไม่เหมือนกัน แทนกันไม่ได้ (:

ลบตารางทั้งฐานข้อมูล SQL Server

ช่วงนี้ มีแก้ ลบ โครงสร้าง database บ่อยมาก จะลบตารางออกแล้ว import จากฐานข้อมูลตัว dev มา บางตารางก็มี relation กับตารางอื่นๆ ยุบยับไปหมด ไม่ยอมให้ลบ หาเจอ query ชุดนี้มันใช่เลย สะดวกมากๆ

โปรดใช้จักรยานในการปฏิบัติการ T-T

[code lang=”sql”]
DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = ‘ALTER TABLE [‘ + tc2.TABLE_NAME + ‘] DROP [‘ + rc1.CONSTRAINT_NAME + ‘]’
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME

OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql

WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec SP_EXECUTESQL @Sql
FETCH NEXT FROM @Cursor INTO @Sql
END

CLOSE @Cursor DEALLOCATE @Cursor
GO

EXEC sp_MSForEachTable ‘DROP TABLE ?’
GO
[/code]

update ข้อมูลใน MySQL แบบหลายตาราง

กำลังทำ presashop ตัวใหม่อยู่ จำเป็นต้องใส่ต้อง copy feature จากภาษาหนึ่งไปอีกภาษาหนึ่งสำหรับ product 1,600 รายการ ถ้าเลือกใส่ใน backend ปกติถึงทำกัน 3 คนมือก็หงิกอยู่ดี (ยังไม่ต้องคิดว่าถ้าทำพลาดละ) และเพราะ prestashop เก็บข้อมูลโดยแยกแต่ละภาษา ออกไปอีกตารางจึ้งต้องเขียน sub query ตามแบบ
SQL Update column values using subquery
query ของผมเป็น
[code language=”sql”]
UPDATE `feature_value_lang` AS target INNER JOIN (
SELECT `id_feature_value` ,`value`FROM `feature_value_lang`
WHERE `id_lang` = 1
) AS source ON target.`id_feature_value` = source.`id_feature_value`
AND target.`id_lang` = 3
SET target.`value` = source.`value`
[/code]

copy ข้อมูลใน MySQL

บางครั้งต้องการข้อมูลจำนวนมาก เพื่อ test บางอย่าง เช่น ระบบแบ่งหน้า โชคดีที่มายเอสคิวแอลสามารถ copy มาจาก record อื่นๆได้ เช่น
[code language=”sql”]
INSERT INTO tableName (col1, col2, col3, …)
SELECT col1, col2, col3, … FROM tableName
WHERE primarykey = 1
[/code]
สมมุติจะคัดลอกตาราง `cds` ในดาต้าเบส cdcol ที่เป็นตัวอย่างติด xampp มา ถ้าไม่มีสร้างใหม่ได้จาก query
[code language=”sql”]
CREATE TABLE IF NOT EXISTS `cds` (
`titel` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
`interpret` varchar(200) COLLATE latin1_general_ci DEFAULT NULL,
`jahr` int(11) DEFAULT NULL,
`id` bigint(20) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=7 ;

INSERT INTO `cds` (`titel`, `interpret`, `jahr`, `id`) VALUES
(‘Beauty’, ‘Ryuichi Sakamoto’, 1990, 1),
(‘Goodbye Country (Hello Nightclub)’, ‘Groove Armada’, 2001, 4),
(‘Glee’, ‘Bran Van 3000’, 1997, 5);

ALTER TABLE `cds`
ADD PRIMARY KEY (`id`);
[/code]
เราจะ copy เพิ่มโดยคิวรี่
[code language=”sql”]
INSERT INTO `cds`(`titel`, `interpret`, `jahr`)
SELECT `titel`, `interpret`, `jahr`
FROM `cds`
WHERE `id` = 5
[/code]
สังเกตุ จะไม่เลือก ฟิลด์ id เพราะ field มันเป็น primary key หลังจากรันคิวรี่จะเห็นว่าข้อมูลเพิ่มขึ้นมาใหม่เหมือนข้อมูลใน id = 5 ทุกอย่างยกเว้น id

PlusMagi's Blog By Pitt Phunsanit

Tag Archive database