FiveTech Software tech support forums

lucasdebeltran wrote:Hello,

As the sintax is different, is there a function to créate tables for either Access or MySQL?.

Thank you.

Code: Select all  Expand view

STATIC FUNCTION ADDTABLE( cMot, cTab, aFld )

    LOCAL cQuery := "CREATE TABLE " + cTab + " ( "

    LOCAL cType

    LOCAL i

    IF cMot == "JET"
        cQuery += "Id COUNTER PRIMARY KEY, "
    ELSEIF cMot == "MSSQL"
        cQuery += "Id INT IDENTITY PRIMARY KEY, "
    ELSEIF cMot == "MYSQL"
        cQuery += "Id SERIAL, "
    ENDIF

    FOR i = 1 TO LEN( aFld )
        cType = aFld[ i, DBS_TYPE ]

        DO CASE
            CASE cType = "C"
                cQuery += aFld[ i, DBS_NAME ] + " VARCHAR ( " + NTRIM( aFld[ i, DBS_LEN ] ) + " ), "
            CASE cType = "N"
                cQuery += aFld[ i, DBS_NAME ] + " NUMERIC ( " + NTRIM( aFld[ i, DBS_LEN ] ) + ", " + NTRIM( aFld[ i, DBS_DEC ] ) + " ), "
            CASE cType = "D"
                cQuery += aFld[ i, DBS_NAME ] + " DATETIME, "
            CASE cType = "L"
                cQuery += aFld[ i, DBS_NAME ] + " INT, "
            CASE cType = "M"
                IF cMot == "JET"
                    cQuery += "[" + aFld[ i, DBS_NAME ] + "]" + " MEMO, "
                ELSEIF cMot == "MSSQL"
                    cQuery += "[" + aFld[ i, DBS_NAME ] + "]" + " TEXT, "
                ELSEIF cMot == "MYSQL"
                    cQuery += aFld[ i, DBS_NAME ] + " TEXT, "
                ENDIF
        ENDCASE
    NEXT

    cQuery = STRIM( cQuery, 2 ) + " )"

    SQLEXEC( cQuery )

    RETURN NIL

Antonio Linares wrote:Enrico,

Where are functions STrim(), NTrim() and SqlExec() ? thanks

Code: Select all  Expand view

#define STRIM( cStr, nChr ) Left( cStr, Len( cStr ) - nChr )
#define NTRIM( nNumber ) LTrim( Str( nNumber ) )

FUNCTION SQLEXEC( cQuery )

    LOCAL cCns := "Your connectionstring here"

    LOCAL oCn := CREATEOBJECT( "ADODB.Connection" )

    oCn:CursorLocation = adUseClient

    oCn:Open( cCns )

    oCn:Execute( cQuery )

    oCn:Close()

    RETURN NIL

lucasdebeltran wrote:Mr. Enrico,

Thank you very much, but I always get, either MYSQL or MSACCESS:

Incorrect arguments, out of range or in conflict with others. (0x800A0BB9)


What I am doing wrong?.

Thank you.

lucasdebeltran wrote:Mr. Enrico,

I have to open the connection using oCn2:CursorLocation = adUseClient.

I was using my own Exec function, but in yours is that set on. Now it is working fine.

lucasdebeltran wrote:Also, in MySQL, when creating a table, this sets up auto_increment feature and it is managed by MySQL:

cQuery += "ID INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID), "



What is the equivalent please for MS Access?.

For MsAccess, I tried with NO luck:

cQuery += "Id INT IDENTITY PRIMARY KEY, "


But the auto increment feature is not managed propelly, I always get 0 at the Id field. Anything else to set up?.

Thank you.

Best regards

Code: Select all  Expand view

   IF cMot == "JET"
        cQuery += "Id COUNTER PRIMARY KEY, "

lucasdebeltran wrote:Mr. Enrico,

Thank you, but with your function the result is the same, with Access and ADO autoincrement fields are not managed.

With MySQL is fine.

Anything else has to be done?.

Thanks.

lucasdebeltran wrote:Mr. Enrico,

Thank you very much. Your function works perfect. There is a bug at TDataRow managing Access numeric fields with decimals, not related to your function.

Best regards