FiveTech Support Forums

Hello,

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

Thank you.

lucasdebeltran wrote:Hello,

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

Thank you.

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

Enrico,

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

Antonio Linares wrote:Enrico,

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

#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

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,

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.

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.

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

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

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

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.

Lucas

What provider do you use to connect to Mysql with ADO ?

Richard

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.

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

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

Mr. Nages,

In the datarow.prg you sent me on 15th I still get the error.

The error only happens with MSACCESS, not with MySQL or MSSQL server.

Thank you.

Enrico,

Does your function support Memo fields ? How do you specify them ? thanks

Lucas,

I am using Enrico's code here and the Access tables are properly created with the autoinc counter