www.FiveTechSoft.com
http://forums.fivetechsoft.com/
This is the function I'm using: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
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 NILAntonio Linares wrote:Enrico,
Where are functions STrim(), NTrim() and SqlExec() ? thanks
Code: Select all
#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 NILPlease, show me how are you calling the function. A reduced and self-contained sample would be better.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.
I don't understand but ok, that's good.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.
Please look at my function: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
IF cMot == "JET"
cQuery += "Id COUNTER PRIMARY KEY, "My function works fine here, even with Access. As I already said, I need a sample of how are you calling it.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.
Yes, in the case of blank row and it was fixed. You already have the fixed version with youlucasdebeltran 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