We have seen that the SQL statement required to create a table can be different for different versions of MSSQL. Naturally it is different for different RDBMS like MSSQL, MYSQL, ORACLE, etc.
For example the SQL for the same table can be:
- Code: Select all Expand view
CREATE TABLE `adoimage` (
`ID` INT AUTO_INCREMENT PRIMARY KEY,
`NAME` VARCHAR ( 10 ),
`FOTO` LONGBLOB
)
For MSACCESS:
- Code: Select all Expand view
CREATE TABLE [adoimage] (
[ID] AUTOINCREMENT PRIMARY KEY,
[NAME] VARCHAR ( 10 ),
[FOTO] LONGBINARY
)
Instead of writing SQL statements for creation of tables on our own, we recommend using FWH function
- Code: Select all Expand view
FWAdoCreateTable( cTable, aStruct, oCn, [lAddAutoInc := .t.] )
aStruct is a normal DBF style structure array we are all very familiar with. Data type "M" creates a long text field and datatype "m" creates a long binary field.
This function internally creates an sql statement appropriate for the RDBMS and its version and we are relieved from the trouble of finding the correct datatype.
The next sample demonstrates using this function for MSSQL, MYSQL and MSACCESS. Using FWH provided ADO functions, we can write code that works with different RDBMS (also versions) without changing our application code:
- Code: Select all Expand view
#include "fivewin.ch"
function Main()
local nCh, oCn, oRs, oPrn
local cTable := "adoimage"
local aImages := { "c:\fwh\bitmaps\olga1.jpg", "c:\fwh\bitmaps\sea.bmp" }
nCh := Alert( "RDMBS", { "MySQL", "MSSQL", "MSACCESS" }, "Select" )
if nCh == 1
oCn := FW_OpenAdoConnection( { "MYSQL", "54.37.60.32", "fwh", "fwhuser", "fwh202006" }, .t. )
elseif nCh == 2
oCn := FW_OpenAdoConnection( { "MSSQL", "208.91.198.196", "gnraore3_", "fwhmsdemo", "fwh@2000#" }, .t. )
elseif nCh == 3
oCn := FW_OpenAdoConnection( "xbrtest.mdb" )
else
return nil
endif
if oCn == nil
? "Connection error"
return nil
endif
TRY
oCn:Execute( "DROP TABLE " + cTable )
CATCH
END
FWAdoCreateTable( cTable, { { "NAME", "C", 10, 0 }, { "FOTO", "m", 8, 0 } }, oCn )
oRs := FW_OpenRecordSet( oCn, cTable )
// Append one image
oRs:AddNew()
oRs:Fields( "NAME" ):Value := Upper( cFileNoExt( aImages[ 1 ] ) )
oRs:Fields( "FOTO" ):Value := STRTOHEX( MEMOREAD( aImages[ 1 ] ) ) // Harbour: HB_STRTOHEX(...)
oRs:Update()
// Append second image. Simpler method
oRs:AddNew( { "NAME", "FOTO" }, { Upper( cFileNoExt( aImages[ 2 ] ) ), HB_STRTOHEX( MEMOREAD( aImages[ 2 ] ) ) } )
oRs:Close()
oRs := FW_OpenRecordSet( oCn, cTable )
XBROWSER oRs
oRs:MoveFirst()
PRINT oPrn PREVIEW
PAGE
@ 1,1 PRINT TO oPrn IMAGE oRs:Fields( "FOTO" ):Value SIZE 6,4 INCHES
oRs:MoveNext()
@ 6,1 PRINT TO oPrn IMAGE oRs:Fields( "FOTO" ):Value SIZE 6,4 INCHES
ENDPAGE
ENDPRINT
oRs:Close()
return nil
For all RDBMSs it is the same code and same result: