Tag Archive database

Byphunsanit
, , , ,

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

Byphunsanit
, , , , , , ,

Joget: สร้าง select box จาก database

การสร้างตัวเลือกใน joget ไม่ควรใช้วิธีกำหนด ตัวเลือกในแต่ละฟอร์ม เพราะว่าถ้ามีการแก้ตัวเลือก จะต้องไปหาว่า dropdown box ตัวนี้มีอยู่ในฟอร์มไหนบ้าง แต่ถ้าเราดึงค่ามาจากดาต้าเบสแค่ไปเพิ่ม / แก้ไขในตารางที่เกี่ยวข้อง เทสซักหน้า ก็เสร็จแล้ว

  1. ในหน้า form builder ให้ลาก Select Box มาในตำแหน่งที่ต้องการ
  2. คลิก Edit icon หลัง input
  3. กรอกข้อมูล
    • ID จะใข้เป็น id ของ input และจะสร้าง column ตามรูปแบบ C_{id} ในตารางที่ผูกกับฟอร์มที่เราวาง input ในดาต้าเบสด้วย
    • Label เป็นฉลากให้กับตัว input
    • Or Choose Options Binder เลือกเป็น JDBC Binder
  4. คลิก next
  5. กรอกข้อมูล
    • Datasource เลือก Default Datasource
    • ติ๊ก Add Empty Option เพื่อที่จะได้ใส่ option ที่ไม่ได้เลือกอะไร ที่ yii จะเรียกว่า prompt นั่นเอง จากนั้นให้กรอก Empty Option Label ที่จะแสดงเป็น label ของ option เช่น Please select
    • SQL SELECT Query ให้กรอก query โดย
      • Column ที่ 1 จะถูกใช้เป็น value ใน แต่ละ option
      • Column ที่ 2 จะเป็น label

      ตัวอย่าง

      SELECT
id AS value, c_name AS label
FROM
app_fd_titles
ORDER BY c_name ASC

ข้อมูลตัวอย่าง

CREATE TABLE `app_fd_titles` (
`id` int(2) unsigned NOT NULL,
`dateCreated` datetime DEFAULT NULL,
`dateModified` datetime DEFAULT NULL,
`createdBy` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`createdByName` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`modifiedBy` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`modifiedByName` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`c_name` varchar(255) DEFAULT NULL,
`c_sex_id` int(1) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `app_fd_titles` (`id`,`dateCreated`,`dateModified`,`createdBy`,`createdByName`,`modifiedBy`,`modifiedByName`,`c_name`,`c_sex_id`) VALUES (1,NULL,NULL,NULL,NULL,NULL,NULL,'Miss',1);
INSERT INTO `app_fd_titles` (`id`,`dateCreated`,`dateModified`,`createdBy`,`createdByName`,`modifiedBy`,`modifiedByName`,`c_name`,`c_sex_id`) VALUES (2,NULL,NULL,NULL,NULL,NULL,NULL,'Mr',2);
INSERT INTO `app_fd_titles` (`id`,`dateCreated`,`dateModified`,`createdBy`,`createdByName`,`modifiedBy`,`modifiedByName`,`c_name`,`c_sex_id`) VALUES (3,NULL,NULL,NULL,NULL,NULL,NULL,'Mrs',1);
INSERT INTO `app_fd_titles` (`id`,`dateCreated`,`dateModified`,`createdBy`,`createdByName`,`modifiedBy`,`modifiedByName`,`c_name`,`c_sex_id`) VALUES (4,NULL,NULL,NULL,NULL,NULL,NULL,'Ms',1);
INSERT INTO `app_fd_titles` (`id`,`dateCreated`,`dateModified`,`createdBy`,`createdByName`,`modifiedBy`,`modifiedByName`,`c_name`,`c_sex_id`) VALUES (5,NULL,NULL,NULL,NULL,NULL,NULL,'Mx',1);
Byphunsanit
, , , , , , , , , , , , , , ,

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

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

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

Byphunsanit
, , , , , , ,

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

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

  1. เปิด sql server management studio เลือก database ที่ต้องการ คลิกขวา New Query
  2. copy 
    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>';
  3. คลิกปุ่ม Results to file หรือ Ctrl + Shift + F กด Excute หรือ F5 แล้วเซฟเป็นไฟล์ ที่ต้องการแต่ลงท้ายด้วย .html เพราะว่าผลลัพธ์ที่ได้จะเป็นเว็บครับ
  4. เปิดไฟล์ที่ส่งออกออกมาแล้วคัดลอกใส่โปรแกรมเวิร์ดหรือเอ็กเซลได้เลยครับ

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

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])
ดูไม่สวย แต่จริงๆ มันมีสีสันด้วยนะครับ ไม่ใช่ตารางโล้นๆ

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

Byphunsanit
, , , , , , ,

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

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

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

query ที่เขียนขึ้นมาใหม่คือ

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>'

query ตัวนี้มีอะไรพิเศษมากกว่าตัวอื่นๆ หลายอย่าง คือ

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

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

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)

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

Byphunsanit
, , , , , , , , , , , , , , , , , ,

MySql ต่างกับ sql server

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

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

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

schema_mysql.php สำหรับ mysql

<?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;
}

และ schema_sqlsrv.php สำหรับ Microsoft sql server

<?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;
}

ถึงจะพยามออกแบบ 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 การทำงาน โครงสร้างเหมือนกัน แต่เขียนไม่เหมือนกัน แทนกันไม่ได้ (:

Byphunsanit
, , , , , , , ,

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

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

โปรดใช้จักรยานในการปฏิบัติการ T-T
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

Byphunsanit
, , , , , , , , , , , ,

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

กำลังทำ presashop ตัวใหม่อยู่ จำเป็นต้องใส่ต้อง copy feature จากภาษาหนึ่งไปอีกภาษาหนึ่งสำหรับ product 1,600 รายการ ถ้าเลือกใส่ใน backend ปกติถึงทำกัน 3 คนมือก็หงิกอยู่ดี (ยังไม่ต้องคิดว่าถ้าทำพลาดละ) และเพราะ prestashop เก็บข้อมูลโดยแยกแต่ละภาษา ออกไปอีกตารางจึ้งต้องเขียน sub query ตามแบบ
SQL Update column values using subquery
query ของผมเป็น

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`

Byphunsanit
, , , , , , , , , ,

copy ข้อมูลใน MySQL

บางครั้งต้องการข้อมูลจำนวนมาก เพื่อ test บางอย่าง เช่น ระบบแบ่งหน้า โชคดีที่มายเอสคิวแอลสามารถ copy มาจาก record อื่นๆได้ เช่น

INSERT INTO tableName (col1, col2, col3, ...)
SELECT col1, col2, col3, ... FROM tableName
  WHERE primarykey = 1

สมมุติจะคัดลอกตาราง `cds` ในดาต้าเบส cdcol ที่เป็นตัวอย่างติด xampp มา ถ้าไม่มีสร้างใหม่ได้จาก query

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`);

เราจะ copy เพิ่มโดยคิวรี่

INSERT INTO `cds`(`titel`, `interpret`, `jahr`)
SELECT `titel`, `interpret`, `jahr`
FROM `cds`
WHERE `id` = 5

สังเกตุ จะไม่เลือก ฟิลด์ id เพราะ field มันเป็น primary key หลังจากรันคิวรี่จะเห็นว่าข้อมูลเพิ่มขึ้นมาใหม่เหมือนข้อมูลใน id = 5 ทุกอย่างยกเว้น id

Proudly powered by WordPress | Theme: Appointment by Webriti