FiveTech Software tech support forums

Posted: **Wed Aug 01, 2007 5:17 pm**

To use the ADORDD with MySql and function dbCreate does not function correctly.
Necessario to make the modifications below in funcoes that they are in the ADORDD.PRG

Code: Select all


static function ADO_CREATE( nWA, aOpenInfo )

   local cDataBase   := HB_TokenGet( aOpenInfo[ UR_OI_NAME ], 1, ";" )
   local cTableName  := HB_TokenGet( aOpenInfo[ UR_OI_NAME ], 2, ";" )
   local cDbEngine   := HB_TokenGet( aOpenInfo[ UR_OI_NAME ], 3, ";" )
   local cServer     := HB_TokenGet( aOpenInfo[ UR_OI_NAME ], 4, ";" )
   local cUserName   := HB_TokenGet( aOpenInfo[ UR_OI_NAME ], 5, ";" )
   local cPassword   := HB_TokenGet( aOpenInfo[ UR_OI_NAME ], 6, ";" )
   local oConnection := TOleAuto():New( "ADODB.Connection" )
   local oCatalog    := TOleAuto():New( "ADOX.Catalog" )
   local aWAData     := USRRDD_AREADATA( nWA )
   local oError

   do case
      case Upper( Right( cDataBase, 4 ) ) == ".MDB"
           if ! File( cDataBase )
              oCatalog:Create( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + cDataBase )
           endif   
           oConnection:Open( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + cDataBase )
           
      case Upper( cDbEngine ) == "MYSQL"     

           oConnection:Open( "DRIVER={MySQL ODBC 3.51 Driver};" + ;
                             "server=" + cServer + ;
                             ";database=" + cDataBase + ;
                             ";uid=" + cUserName + ;
                             ";pwd=" + cPassword )
           
   endcase        

   TRY
      oConnection:Execute( "DROP TABLE " + cTableName )
   CATCH   
   END

   TRY
      
     //  oConnection:Execute( "CREATE TABLE [" + cTableName + "] (" + aWAData[ WA_SQLSTRUCT ] + ")" )
      
        oConnection:Execute( "CREATE TABLE " + cTableName + "(" + aWAData[ WA_SQLSTRUCT ] + ")" )

   CATCH
      oError := ErrorNew()
      oError:GenCode     := EG_CREATE
      oError:SubCode     := 1004
      oError:Description := HB_LANGERRMSG( EG_CREATE ) + " (" + ;
                            HB_LANGERRMSG( EG_UNSUPPORTED ) + ")"
      oError:FileName    := aOpenInfo[ UR_OI_NAME ]
      oError:CanDefault  := .T.
      UR_SUPER_ERROR( nWA, oError )
   END
      
   oConnection:Close()
   
return SUCCESS

static function ADO_CREATEFIELDS( nWA, aStruct )

   local aWAData := USRRDD_AREADATA( nWA )
   local n

   aWAData[ WA_SQLSTRUCT ] = ""

  for n = 1 to Len( aStruct )
     if n > 1
        aWAData[ WA_SQLSTRUCT ] += ", "
     endif   
   
   //  aWAData[ WA_SQLSTRUCT ] += "[" + aStruct[ n ][ DBS_NAME ] + "]"
    
       aWAData[ WA_SQLSTRUCT ] += aStruct[ n ][ DBS_NAME ] 

   Do case
        case aStruct[ n ][ DBS_TYPE ] $ "C,Character"
             aWAData[ WA_SQLSTRUCT ] += " CHAR(" + AllTrim( Str( aStruct[ n ][ DBS_LEN ] ) ) + ") NULL" 

        case aStruct[ n ][ DBS_TYPE ] == "N"
             aWAData[ WA_SQLSTRUCT ] += " NUMERIC(" + AllTrim( Str( aStruct[ n ][ DBS_LEN ] ) ) + ")"

        case aStruct[ n ][ DBS_TYPE ] == "L"
             aWAData[ WA_SQLSTRUCT ] += " LOGICAL"
     endcase     
  next      

return SUCCESS

Posted: **Thu Aug 02, 2007 6:22 pm**

Fernando

E possivel to use the Index on… with ADORDD using MYSQL?

Therefore all time that I execute this command it presents the error below:

error ADOX.CATALOG:TABLES:KEYS/16389 E_FAIL:COUNT from errorsys, line: 0

Posted: **Thu Aug 02, 2007 6:37 pm**

You need to check if your ADO engine supports indexing:

#define adIndex 0x100000
MsgInfo( HB_AdoRddGetRecordset():Supports( adIndex ) )

Posted: **Thu Aug 02, 2007 9:39 pm**

It returned false!

Then he does not have as to create indices with the ADORDD?

What I must modify or proceed so that can create indices?

Posted: **Thu Aug 02, 2007 11:46 pm**

Mgomesnet

Many SQL databases do not support ADO as a way to utilize indexes.. SQL Server 2000-2005 for one and Oracle.

I have not found that to be a hinderence at all. Most modern SQL databases do not require indexes anyway .. other than setting a 'primary key'

You can create indexes for Oracle and SQL server and if the server can use them to speed up a query .. it will .. all of that is controlled by the server and not the programmer .. that is why many SQL databases do not allow programatic use of indexes.

Just use your 'find', 'filters' and be smart with your select statements and only query records you really need .. not just opening the whole table as in .dbf... you will be amazed at how fast a SQL server can be... take indexes out of your mind-set and think in terms of smart, crisp SQL queries.

Rick Lipkin
SC Dept of Health, USA

Posted: **Fri Aug 03, 2007 6:02 am**

> What I must modify or proceed so that can create indices?

If ADO does not support them, then you can not create them.

You have to use SQL expressions to get the desired order: ... ORDER BY ...

Posted: **Fri Aug 03, 2007 12:50 pm**

Thank you!...

FiveTech Software tech support forums

Problems with DBCREATE in ADORDD

Problems with DBCREATE in ADORDD