Super SQL Server Systems Turbocharge Database Performance with C++ External Procedures Joseph Gama, P. J. Naughter ISBN: 0-9761573-2-2 Library of Congress Number: 2005928015 550 pages - Perfect bind - 9x7 Shelving: Databases/SQL Server Retail Price $49.95 IT In Focus Series # 2 Key Features About the Authors Table of Contents - Order now and get 30% off the retail price! You also receive immediate online access to the code depot! Only $32.95 (30% off) This unique book focuses on hypercharging SQL Server database performance with external procedures. SQL Server runs databases for financial, bank, biotech, Ecommerce, data warehousing, scientific applications, etc. which often require huge data processing with complex formulas or algorithms. Most of this work is done with TSQL stored procedures and .NET procedures (in SQL Server 2005), however neither one of them can achieve the performance and flexibility of external procedures which are natively compiled code. External procedures are analogous to stored procedures but with the power of C++ for CPU intensive algorithms, hardware/low level access, use of external libraries, etc. Database administrators/developers can create more efficient code and avoid expensive, slow and unreliable third party tools. There is also the extra bonus of having the source compiled, to protect the intellectual property. This book is intended for C++ programmers who want to learn about database programming and use their C++ skills seamlessly, and database administrators and/or programmers who need more performance, functionality and versatility than they can achieve with TSQL, .NET procedures or ADO. * See how to get 200x performance improvements over T-SQL * Learn secure encryption techniques for SQL Server data * Create object-oriented database procedures with SQL Server * See real-world examples of interfaces with Windows internals * Learn how to create external procedures for code 200x faster than TSQL * Use OO, MFC, SDK, ATL and access DLL functions from TSQL! * Protect your intellectual property with code that cannot be decompiled! * Receive complete background tutorials on TSQL with external procedures! * Get a complete tutorial covering all the TSQL background. * Learn to use the binaries with working TSQL samples. * See real world solutions for encryption, GIS, eCommerce, and system security. * Create stored procedures in JavaScript or VBScript with the revolutionary XP_RUNSCRIPT! About the Authors: Joseph Gama Joseph Gama is a software engineer, specialized in SQL Server. His popular database tutorials received very positive feedback from the Microsoft SQL Server community. He contributed over 10,000 lines of source code to the public domain, mostly TSQL, JavaScript and C/C++. Adding to that, he created and shared over 800 rules for Snort, half of them for Microsoft SQL Server. Joseph lives in California, in the Bay Area. His main interests are polyominoes, phonetics and linguistics. P. J. Naughter Patrick Joseph (PJ) Naughter runs Naughter Software, a software development consultancy firm specializing in native mode Windows development. Before setting up Naughter Software, PJ was a technical architect and software developer working for Soft-ex Communications, a developer of PBX Call Monitoring software located in Dublin, Ireland. In his spare time, he provides open source and shareware of interest to Windows developers via his web site at www.naughter.com. At last count, well over 230,000 lines of Win32, MFC & ATL C++ source code is available to download. PJ lives in rural Ireland, 50 miles south of Dublin. Outside of computers, he also has a keen interest in Astronomy, Pub Quizzes, 10-pin bowling, science fiction and the odd game of golf, Irish weather permitting of course. Table of Contents: 1. Introduction to Extended Stored Procedures What is an XP - "extended stored procedure"? Why use XP's? How to create XP's? How to use XP's? How to call XP's? About the compilers/languages used to create XP’s Can VB create XP's SQL Server 2005 and extended stored procedures ODS API How to install the ODS code samples, libraries and headers Registering an XP from Enterprise Manager Using Query Analyzer Execution of Extended Stored Procedures XP Samples Parameter handling Data Types 2. Misc compilers and languages Microsoft Visual C++ - WIN32 DLL xp_hellovcdll Microsoft Visual C++ - WIN32 MFC DLL xp_hellomfc CodeWarrior xp_hellocw Borland C++ xp_hellobcc Dev-C++ xp_hellodevc MINGW xp_hellomw Intel C++ Compiler Digital Mars Notes about compilation Microsoft Platform SDK Debugging an XP Execution errors 3. A C++ Class Framework for XP's (XP++) Exporting the function Support for __GetXpVersion Implement encapsulation by using C++ classes Automatically link to the correct ODS import library Encapsulate verification and setup of parameters. Provide automatic conversion of parameter data types Encapsulate the SDK calls provided by ODS Reduce the number of parameters required by ODS Track Memory Leaks Using the framework Other Examples Summary 4. Network Messaging (XP_NETSEND) Implementation XP_NET_ADDNAME XP_NET_DELNAME XP_NET_SEND Testing the Code Applications of XP_NETSEND Summary 5. SMTP Mailing (XP_SMTPSENDMAIL) Choice of Mail Library Using CDO for Windows 2000 Implementation Details Code Highlights Applications of XP_SMTPSENDMAIL Summary 6. NT Event Log (XP_NTLOG) Implementation XP_NTLOG_INSTALL XP_NTLOG_UNINSTALL XP_NTLOG_ERROR, XP_NTLOG_WARNING & XP_NTLOG_INFORMATIONAL Testing the Code Applications of XP_NTLOG Summary 7. Disk Logging (XP_DISKLOG) Implementation Testing the Code Summary 8. Audio Notification (XP_NOTIFY) Initial Implementation XP_SIMPLE_MESSAGEBEEP, XP_ICONASTERISK_MESSAGEBEEP, XP_ICONEXCLAMATION_MESSAGEBEEP, XP_ICONHAND_MESSAGEBEEP, XP_ICONQUESTION_MESSAGEBEEP and XP_OK_MESSAGEBEEP XP_BEEP XP_PLAYSOUNDFILE and XP_STOPSOUND XP_PLAYSOUNDALIAS XP_PLAYSOUNDMEMORY Testing the Code Applications of XP_NOTIFY Summary 9. Encoding HTML special characters (XP_HTMLENCODE) Implementation Testing the Code Applications of XP_HTMLENCODE XP_HTMLENCODE2 Summary 10. Cryptography (XP_CRYPTOAPI) Hashing Symmetric Encryption / Decryption Hash Message Authentication Code Asymmetric Encryption / Decryption Message Signing / Verification Public Key Management XP_CRYPTOAPI Usage Applications of XP_CRYPTOAPI Summary 11. Random Data (XP_RAND) Implementation Testing the Code Applications of XP_RAND Summary 12. INI File Manipulation (XP_INI) Implementation XP_INI_WRITESTRING XP_INI_WRITEINTEGER XP_INI_READSTRING XP_INI_READINTEGER XP_INI_GETSECTIONS XP_INI_READSECTION XP_INI_DELETESECTION XP_INI_DELETEKEY XP_INI_GETKEYS XP_INI_WRITE XP_INI_READ Testing the Code Summary 13. Retrieving free disk space (XP_DISKSPACE) Implementation Testing the Code Summary 14. Retrieving CPU usage (XP_CPUUSAGE) Implementation Testing the Code Applications of XP_CPUUSAGE Summary 15. Raw sockets sample (XP_RAWIP) Implementation Testing the Code Summary 16. Geographical Information Systems (XP_GIS) Introduction The Shape of the Earth Datums Coordinate Systems and UTM Implementation XP_GIS_ELLIPSOIDINFO XP_GIS_DATUMINFO XP_GIS_DATUMTRANSFORM XP_GIS_LL2UTM XP_GIS_UTM2LL XP_GIS_DISTANCE_BETWEEN Testing the Code Summary 17. Regular Expressions (XP_REGEXP) Implementation Testing the Code Applications for XP_REGEXP Summary 18. A Multiple Use XP (XP_SERVERREACHABLE) Initial Creation Adding COM / Win32 DLL Support Adding XP Support Testing the Code (XP Support) Testing the Code (Win32 DLL Support) Testing the Code (COM Support) Summary 19. Computational Astronomy (XP_ASTRO) Implementation XP_ASTRO_DATE2JULIAN & XP_ASTROJULIAN2DATE XP_ASTRO_DELTAT XP_ASTRO_SPRING_EQUINOX, XP_ASTRO_SUMMER_SOLSTICE, XP_ASTRO_AUTUMN_EQUINOX & XP_ASTRO_WINTER_SOLSTICE XP_ASTRO_MOONPHASEK & XP_ASTRO_MOONPHASE Testing the Code Summary 20. IP Configuration (XP_IPConfig) IPConfig IPConfig Syntax Implementation IP Helper Testing the Code Summary 21. Beyond C/C++ (XP_RUNSCRIPT) Why the Need? sp_OACreate, sp_OAMethod etc The Solution The Object Model Initial Creation The XPServer Object The XPParameters Object The XPParameter Objects Adding XP and Active Scripting Support Extending to support Visual Basic etc Testing the Code Summary 22. Protecting Intellectual Property with XP's Prior Art The valuable disclaimer Technical protections Stored procedures can be decrypted Market for 3rd party components Enforcing licenses with XP's XP's as black boxes 23. Managed Code in SQL Server 2005 .NET vs TSQL .NET vs XP’s The right choice Hello World in VB.NET Visual Studio 2005 Stored Procedures with .NET code Compiling from the Command Prompt Express Manager Levenshtein distance in .NET Comparing Rijndael encryption Comparing several MD5 implementations 24. Security in SQL Server 2000 Port security SQL Injection Cross-site scripting (XSS) Buffer overflows Snort Security tools 25. From TSQL to XP's TEA encryption with TSQL TEA encryption with XP_CRYPTOAPI 26. SQL Server database tutorial SQL Server default databases Data types Data types for code and data structure use Data types for code use Data types for data structure use Data types for blobs Tables Data Integrity Referential Integrity Constraints Rules, Defaults, and User-Defined Data Types Views Joins Temporary tables Subqueries Derived table Cursors Indexes SQL Server Collation Triggers Referential Integrity with triggers 27. SQL Server stored procedure tutorial Samples of SP’s Parameter handling SP with optional Input parameters Mean, Median and Modal with SP’s SP error management Rowset returning SP's Start up SP's Recursive SP's Crosstab queries Dynamic SQL 28. SQL Server User-defined functions (UDF's) Types of functions Scalar UDF's Inline Table-valued UDF's Multi-statement Table-valued UDF's Recursive UDF’s Jobs Alerts 29. System XP's Documented system XP's Undocumented system XP's Interesting undocumented system XP's File related XP's Registry related XP's One-way encryption SQL Mail Procedures 30. The Authors Joseph Gama Patrick Joseph (PJ) Naughter Index Topics: .NET .NET Common Language Runtime ' ': &apos @ @@Error @@ERROR @CaseSensitive @From @Match @Message @Reachable @RegExp @ServerToPing @SourceText @To _ __declspec(dllexport) __GetXpVersion _alloca function _ATL_MIN_CRT _CrtCheckMemory _CrtDumpMemoryLeaks _CrtMemCheckpoint _CrtSetBreakAlloc _xp instance < <: < > >: > DES Block Cipher DES Block Cipher A ACID AES algorithm Aggregate functions ALERT command alerts algorithm identifiers alternate key American National Standards Institute American Standard Code for Information Interchange ANSI ANSI SQL- ansi_nulls API System Stored Procedures Application Programming Interface ASCII ASP web application astronomical operations Asymmetric cryptography ATL Regular expression parser audible notification AutoConvertParameters B Basic Authentication Basic Multilingual Plane Beep begin_implement_xp_parameters begin_implement_xp_parameters(class) bigint Binary Coded Decimal binary data type bit block cipher block ciphers Bloodshed Dev-C++ Blowfish Block Cipher bMatch Borland C++ Builder BoundsChecker BUILD_XP_SERVERREACHABLE Business rules Byte Order Mark C C# c:XPLOG_%Y_%m_%d.log C++ DLL Debug.dll C++ encapsulation CAADate CAASun candidate key Cartesian join CAtlRegExp CAtlREMatchContext CAutoWinHandle cc CComBSTR CCritSection CCritSectionLocker CDiskLogExtendedStoredProcedure CDiskLogExtendedStoredProcedure:: MapReplaceableStrings CDO for Windows cdosys.dll CExtendedStoredProcedure CExtendedStoredProcedure:: VerifyAndInitializeParameters CFile char CHECK constraint checksum Cipher Block Chaining clustered index Clustered tables CodeWarrior Development Studio COFFOMF collation collision resistance constraints Constraints container Control Codes Coordinated Universal Time correlated subqueries CRC checksums CreateFile CReferenceDatum cross join crosstab queries crosstab query CRYPT_SILENT CRYPT_VERIFYCONTEXT flag CryptAcquireContext CryptDeriveKey CryptGenRandom Cryptography Cryptography Service Provider CryptSignHash CSaveFileStoredProcedure CSimpleArray cursor cursor data type cursors CXPServerObj Cyclic Redundancy Checksum Cylink Mek Block Cipher D Data Encryption Standard Data integrity Data pages data_to_hash_index datalength datetime datum DatumTransform dbdatetime decimal (p s) Declarative Referential Integrity declare_xp_parameters() default value defaults DeltaT Denial Of Service Dependency Walker DeregisterEventSource derived table DES Block Cipher DESX Block Cipher Digital Mars Digital Signal Standard digital signature Digital Signature Algorithm DirectX Audio APIs disclaimer disconnected recordset DistanceBetweenPoints Domain integrity Dynamic Link Library dynamic linking Dynamic SQL Dynamical Time E E-commerce Electronic Cookbook Mode ellipsoid value end_implement_xp_parameters end_implement_xp_parameters(class) Entity integrity enum Ephemeris Time EquatorialRadius Error Error ExecIsStartup Express Manager Extended MAPI extended stored procedure extents extern "C" F Fall Equinox feedback register Fibonacci sequence FIXED_INFO float foreign key format G General Extended Procedures Geographical Information Systems geoid GetAdaptersInfo GetDiskFreeSpaceEx GetFileAttributes GetLocalTime GetNetworkParams GetPrivateProfileInt GetPrivateProfileSection GetPrivateProfileSectionNames GetPrivateProfileString GetPrivateProfileStruct GetProcAddress GetSystemTime GetSystemTimes GetXpVersion global temporary table globally unique identifier Greenwich Mean Time H hash Hash Message Authentication Code Heaps Helix Stream Cipher HexDec high CPU utilization HMAC htmlencode I IActiveScript IActiveScriptParse IActiveScriptSite IANA IcmpCloseFile IcmpCreateFile IcmpSendEcho IcmpServerReachable IMPDEF implement_version_xp() IMPLIB include directory index columns indexes ini files initialization vector inner join InstanceName int Intel C++ Compiler Intellectual Property International Organization for Standardization InterNet Assigned Numbers Authority intrusion detection system Intrusion Detection System IP Helper API IP_ADAPTER_INFO IP_HDRINCL IPConfig ISAPI DLL IServerReachable ISO ISO IsReturnParam J Joins Julian Day Julian Ephemeris Day K keystream generator L Language ID Reference Number Levenshtein distance library directory Link LLtoUTM local temporary table Locale Identifier LOCALE_SYSTEM_DEFAULT Lunar Eclipse M m_AlgID m_AllowableTypes m_bActualOutputParameter m_bAllowNull m_bOptional m_bType m_cbActualLen m_cbMaxLen m_Data m_DescribeRowData m_fNull m_nColumn m_nParameters m_ParameterType m_pData m_pESP m_pParameterData m_pParameterDefinitions m_precision m_RuntimeType m_scale m_szName MapReplaceableStrings MBCS strings mc.exe MD hash MD hash MD Hash Algorithm MD hash MD Hash Algorithm mean median Memory model Message Authentication Code MessageBeep MessageBox Microsoft CryptoAPI Microsoft Platform Software Development Kit Minimalist GNU for Windows Mixed extents modal algorithm Molodensky Datum Transform money MTA COM objects multithreaded code N named parameters Named Pipes National Institute of Standards and Technology National Security Agency nchar NetBios Message Alias NetMessageBufferSend NetMessageNameAdd NetMessageNameDel NibbleToChar non-clustered index non-correlated subqueries Northwind NOT NULL constraint NT Event Log NtQuerySystemInformation nvarchar O OLE Automation Extended Stored Procedures OneOverF one-time pad Open Data Services opends.dll opends.lib opendsbcc.lib OpenTextFile Optional parameters Osql –L outer full join outer left join outer right join Output P packet sniffer pages ParamStatus patindex PATINDEX Performance Data Helper Perl Compatible Regular Expressions library phases of the Moon PingMachine.cpp/h pivot PK plan creation PlaySound Popup messages precompiled headers primary key prior art pszServer public key cryptography Pubs pwdcompare pwdencrypt R raiserror rand function rand() random data RandomData RC Block Cipher RC RC Stream Cipher RC Block Cipher readtext real recursive function reference ellipsoid ReferenceDatum Referential integrity RegisterEventSource regular expression regular expressions Replication Procedures Rijndael algorithm RSA Rules S satellites value scalar system function scalar UDF schema binding scrollable cursor SDK Beep Secure Hash Algorithm Select Compiler self join ServerReachable SessionKeyAlgorithm SessionMessageLCID SetColumnData SetFilePointer SetOutputData SetupExpectedParameters SHA hash ShootOut.wav Skipjack Block Cipher smalldatetime smallint smallmoney SMTP SND_ALIAS SND_FILENAME SND_MEMORY SND_SYNC Snort Solar Eclipse sort order Soundex sp_OA* SP_OA* sp_OACreate sp_OADestroy sp_OAGetErrorInfo sp_OAGetProperty sp_OAMethod sp_OASetProperty sp_OAStop SPaddXP_CRYPTOAPI SPdropXP_CRYPTOAPI SPparseInputForRegex Spring Equinox SQL collation SQL Mail Procedures SQL Profiler Procedures SQL Server SQL Server Agent Procedure SQL.ReturnValue sql_variant SQL- standard SQLPing srv.h srv_convert srv_describe srv_getbindtoken srv_getdtcxact srv_got_attention srv_impersonate_client srv_paraminfo srv_paramname srv_paramnumber srv_paramsetoutput srv_paramstatus srv_pfield srv_pfieldex SRV_PROC srv_revert_to_self srv_rpcparams srv_senddone srv_sendmsg srv_sendrow srv_setcoldata srv_setcollen srv_willconvert srv_wsendmsg stack overwrite static linking stdafx.h stored procedures stream cipher stream ciphers subquery substring Summer Solstice switches Symmetric cryptography ciphers sysmessages system XP's SYSTEM_INFORMATION_CLASS T table data type table variable table-valued system function table-valued UDF TCP/IP TEA TEA algorithm temporary table Temporary tables The Replacement Character timestamp data type Tiny Encryption Algorithm tinyint triggers Triggers U UDF UDFdecrypt UDFencrypt UDFRandom UDT undocumented system XP's Unicode Project Uniform extents UNIQUE constraint uniqueidentifier data type Universal Time Universal Transverse Mercator User Defined Data Type User Defined Function user defined functions User Defined Functions UTF- UTF- UTF- UTM UTM Easting UTM Northing UTM Zone V varbinary data type varchar variant data type VariantToXPData VB.NET vbc.exe Vernal Equinox Views Visual Studio W wav files waveOutWrite Web Server IIS WGS Windows collation Winsock Winter Solstice wrapper methods WritePrivateProfileInt WritePrivateProfileString WritePrivateProfileStruct X x_WinDLL.cpp XML data type XML Procedures xp_assert xp_astro xp_astro_autumn_equinox xp_astro_datejulian xp_astro_deltat xp_astro_moonphase xp_astro_moonphasek xp_astro_spring_equinox xp_astro_summer_solstice xp_astro_winter_solstice xp_astrojuliandate xp_beep xp_cmdshell xp_cpuusage xp_cryptoapi xp_cryptoapi.dll xp_disklog xp_diskspace xp_dsa_export xp_dsa_sign xp_ellipsoidinfo xp_gis xp_gis_datuminfo xp_gis_datumtransform xp_gis_distance_between xp_gis_ellipsoidinfo xp_gis_llutm xp_gis_umtll xp_hellobcc.cpp xp_hellobcc.dll xp_hellomfc.cpp xp_hellomfc.def xp_hellomfc.dll xp_hellovcdll xp_hellovcdll.cpp xp_hellovcdll.dll xp_htmlencode xp_htmlencode xp_iconasterisk_messagebeep xp_iconexclamation_messagebeep xp_iconhand_messagebeep xp_iconquestion_messagebeep xp_ini xp_ini_deletekey xp_ini_deletesection xp_ini_getkeys xp_ini_getsections xp_ini_read xp_ini_readinteger xp_ini_readsection xp_ini_readstring xp_ini_write xp_ini_writeinteger xp_ini_writestring xp_input_or_output_parameter xp_input_or_output_parameter_precision_scale xp_input_parameter xp_input_parameter_presicion_scale xp_int xp_ipconfig xp_net_addname xp_net_delname xp_net_send xp_netsend xp_notify xp_ntlog xp_ntlog_install xp_ntlog_uninstall xp_odbc xp_odbc++ xp_ok_messagebeep xp_output_parameter xp_palindrome xp_palindrome xp_parameter_definition xp_playsoundfile xp_playsoundmemory xp_pp.cpp xp_pp.h xp_pt_acsiitext xp_pt_asciitext xp_pt_empty xp_pt_tchartext xp_pt_unicode xp_pt_unicodetext xp_rand xp_randproc module xp_randproc.cpp xp_randproc.h xp_rawip xp_regexp xp_rijndael xp_rsa_export_encryptkey xp_rsa_export_signkey xp_rsa_md_sign xp_rsa_md_sign xp_rsa_md_sign xp_rsa_sha_sign xp_run_activex xp_runscript xp_runscript_disk xp_runscript_parameter xp_serverreachable xp_simple_messagebeep xp_smtpsendmail xp_stopsound xp_tea_encrypt xp_unicodetext xp_variant_parameter xp_variant_parameter_type XP++ XPSaveFile.cpp XPSaveFile.h