Link to home
Start Free TrialLog in
Avatar of Coast Line
Coast LineFlag for Canada

asked on

Access Alter Command Syntax in Coldfusion not working

Hellow Experts, For Complete Article You can View this question:

https://www.experts-exchange.com/questions/24202544/access-AutoNumber-field-not-picking-up.html?anchorAnswerId=23886403#a23886403

I have my alter command,

like this

ALTER table #list_name#
      ALTER COLUMN #ListGetAt(form.tcolumnName, idx)# <!---#ListGetAt(form.fieldtype, idx)#---> #form['fieldtype_' & idx]# #form['tlength_' & idx]#

although i try to change the datatype, and length but it does not do that
Avatar of gdemaria
gdemaria
Flag of United States of America image

please place this alter code in CFOUTPUT tags so you can display on the screen what the generated code looks like.   Then cut and paste it here so we can see it.   How does it compare with the syntax you need?

This is the syntax for MS Access alter table command.   Does it look right?
It does not appear that your length is inside of ( )

Are all your fields strings?
ALTER TABLE Employees ALTER COLUMN Emp_Email TEXT(50);
 
Below is the syntax for the alter table statement for Microsoft Access 2003 
 
ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT index] |
ALTER COLUMN field type[(size)] }

Open in new window

Avatar of Coast Line

ASKER

Ok here is some i cam around while i was trying to do something:
i checked my CFC and i got this with a little ammendment:
<cffunction name="getCFDataType" access="public" returntype="string" output="no" hint="I return the cfqueryparam datatype from the database datatype.">
 <cfargument name="type" type="string" required="yes" hint="The database data type.">
 
 <cfset var result = "">
 
 <cfswitch expression="#arguments.type#">
       <!---<cfcase value="binary,image"><cfthrow message="Cannot handle this data type." type="Handle" detail="Cannot handle data type '#arguments.type#'" errorcode="InvalidDataType"></cfcase>
       <cfcase value="bit"><cfset result = "CF_SQL_BIT"></cfcase>
       <cfcase value="char,varchar"><cfset result = "CF_SQL_VARCHAR"></cfcase>
       <cfcase value="counter"><cfset result = "CF_SQL_AUTO"></cfcase>
       <cfcase value="datetime"><cfset result = "CF_SQL_DATE"></cfcase>
       <cfcase value="decimal"><cfset result = "CF_SQL_DECIMAL"></cfcase>
       <cfcase value="double"><cfset result = "CF_SQL_DOUBLE"></cfcase>
       <cfcase value="float"><cfset result = "CF_SQL_FLOAT"></cfcase>
       <cfcase value="int,integer"><cfset result = "CF_SQL_INTEGER"></cfcase>
       <cfcase value="longchar"><cfset result = "CF_SQL_LONGVARCHAR"></cfcase>
       <cfcase value="memo,text"><cfset result = "CF_SQL_CLOB"></cfcase>
       <cfcase value="money"><cfset result = "CF_SQL_MONEY"></cfcase>
       <cfcase value="real"><cfset result = "CF_SQL_REAL"></cfcase>
       <cfcase value="smallint"><cfset result = "CF_SQL_SMALLINT"></cfcase>
       <cfcase value="tinyint"><cfset result = "CF_SQL_TINYINT"></cfcase>
       <cfcase value="uniqueidentifier"><cfset result = "CF_SQL_IDSTAMP"></cfcase>
       <cfdefaultcase><cfset result = ""></cfdefaultcase>--->
        <cfcase value="binary,image"><cfthrow message="Cannot handle this data type." type="Handle" detail="Cannot handle data type '#arguments.type#'" errorcode="InvalidDataType"></cfcase>
       <cfcase value="bit"><cfset result = "CF_SQL_BIT"></cfcase>
       <cfcase value="varchar,text"><cfset result = "CF_SQL_VARCHAR"></cfcase>
       <cfcase value="counter"><cfset result = "CF_SQL_AUTO"></cfcase>
        <cfcase value="int,integer"><cfset result = "CF_SQL_INTEGER"></cfcase>
       <cfcase value="datetime"><cfset result = "CF_SQL_DATE"></cfcase>
       <cfcase value="double"><cfset result = "CF_SQL_DOUBLE"></cfcase>
       <cfcase value="longchar"><cfset result = "CF_SQL_LONGVARCHAR"></cfcase>
       <cfcase value="money,currency"><cfset result = "CF_SQL_MONEY"></cfcase>
       <cfdefaultcase><cfset result = ""></cfdefaultcase>
 </cfswitch>
 
 <cfreturn result>
</cffunction>

<cffunction name="isStringType" access="private" returntype="boolean" output="no" hint="I indicate if the given datatype is valid for string data.">
 <cfargument name="type" type="string">

 <cfset var strtypes = "text">
 <cfset var result = false>
 <cfif ListFindNoCase(strtypes,arguments.type)>
       <cfset result = true>
 </cfif>
 
 <cfreturn result>
</cffunction>

<cffunction name="getDBTableStruct" access="public" returntype="array" output="no" hint="I return the structure of the given table in the database.">
  <cfargument name="tablename" type="string" required="yes">
  <cfscript>
 var qRawFetch = 0;
 var arrStructure = 0;
 var tmpStruct = StructNew();
 var i = 0;

 var PrimaryKeys = 0;
 var TableData = ArrayNew(1);
 </cfscript>
  <cfset qRawFetch = runSQL("SELECT TOP 1 * FROM #arguments.tablename#")>
  <cfset arrStructure = getMetaData(qRawFetch)>
  <cfif isArray(arrStructure)>
    <cfloop index="i" from="1" to="#ArrayLen(arrStructure)#" step="1">
      <cfset tmpStruct = StructNew()>
      <cfset tmpStruct["ColumnName"] = arrStructure[i].Name>
      <cfset tmpStruct["CF_DataType"] = getCFDataType(arrStructure[i].TypeName)>
      <!--- %% Ugly guess --->
      <cfif arrStructure[i].TypeName eq "COUNTER" OR ( i eq 1 AND arrStructure[i].TypeName eq "INT" AND Right(arrStructure[i].Name,2) eq "ID" )>
        <cfset tmpStruct["PrimaryKey"] = true>
        <cfset tmpStruct["Increment"] = true>
        <cfset tmpStruct["AllowNulls"] = false>
        <cfelse>
        <cfset tmpStruct["PrimaryKey"] = false>
        <cfset tmpStruct["Increment"] = false>
        <cfset tmpStruct["AllowNulls"] = true>
      </cfif>
       %% Ugly guess 
      <cfif isStringType(arrStructure[i].TypeName) AND NOT tmpStruct["CF_DataType"] eq "CF_SQL_VARCHAR">
        <cfset tmpStruct["length"] = 255>
      </cfif>
      <cfset tmpStruct["Special"] = "">
      <cfif Len(tmpStruct.CF_DataType)>
        <cfset ArrayAppend(TableData,StructCopy(tmpStruct))>
      </cfif>
    </cfloop>
    <cfelse>
    <cfthrow message="an currently only support MS Access on ColdFusion MX 7." type="mgr" detail="NoMSAccesSupport">
  </cfif>
  <cfreturn TableData>
</cffunction>


now if i remove this line:

<cfcase value="longchar"><cfset result = "CF_SQL_LONGVARCHAR"></cfcase>
my tabledata does not show some fields up, i do not know. if i inlucde this line, works.
All datatypes get changes only datatype which is troubling is the text, memo dataype.
if in my listed table the datatype is memo as when i dump it shows as cf_sql_longvarchar, i try changing it to text as cf_sql_varchar according to above cfc.
it does not get changed, it stays as memo and it does gets to text.
here are the scenario:
1. if the datatype is default to text and i not change the datatype to memo, it automaticaaly goes to memo field.
2. if i change the memo datype to text, it does not trigger in the alter command.
3. if i change the memo to other dadatype like currency, datime, yesno. it gets changed.
this is the trouble which i think is the root cause why my alter command is not showing me the correct result.
Hope i have not missed anything here,
It's not clear what you're saying.

> if the datatype is default to text and i not change the datatype to memo, it automaticaaly goes to memo field

Are you saying that a variable "goes to memo" or are you saying the alter statement goes to memo or the database design changes to memo?

In your first post, we were talking about your ALTER statement not looking right, now we are looking at a big block of code that somewhere does something wrong...

Add CFOUTPUT tags showing the values along the way.... see where the value is not right.   Is the alter statement right ?   Are the variables before the alter statement right?   Is the value after the case statement right?

I think, I am creating havoc in my posts..
Okay I explain it better.
  • I just output the alter statement. it shows up as:
    • ALTER table gradmin ALTER COLUMN user_id COUNTER ALTER table gradmin ALTER COLUMN user_name TEXT ALTER table gradmin ALTER COLUMN user_pass TEXT ALTER table gradmin ALTER COLUMN user_email TEXT ALTER table gradmin ALTER COLUMN super TEXT ALTER table gradmin ALTER COLUMN class DATETIME ALTER table gradmin ALTER COLUMN Class2 YESNO ALTER table gradmin ALTER COLUMN color NUMBER 
  • the above statemnt showsoutput. Now i changed user_name column which was memo in the database to text datatype.
  • The statement gets executed and shows table structure edited successfully.
  • but memo fields does not get changed. it stays as memo. it does not change to text.
  • Same is the Case with text as dadatype, i keep it as text and does not change it, just wanna change the length, it just converts the text field to Memo.
This the whole case i am suffering here.

i just ran some tests, and it seems that if your MEMO fields already contain data, and the length of this data is longer than default length of TEXT type fields as set in your db, then ALTER TABLE statement DOES NOT alter the data type of MEMO fields.

furthermore, when, as in your case, you do not specify the size of the TEXT field, the ALTER TABLE query assumes the size is max text type field length as specified in your db (defaults to 255 characters) and FAILS SILENTLY.
 
if you explicitly specify the new field size (as in ALTER COLUMN xxx TEXT(255), which is correct full syntax of ALTER COLUMN statement) when your MEMO fields contain more data than teat, the query will generate a cf error "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.".  

if you try to set the new field size to a value larger that max text field length set in your db, the query will generate another error: "Size of field 'xxx' is too long."

to sum it up:
1) your statement:
ALTER TABLE xxxx ALTER COLUMN memo_column TEXT
will fail silently unless all the MEMO fields in your table are empty.

2) if you use correct syntax of ALTER COLUMN statement and specify the field size:
ALTER TABLE xxxx ALTER COLUMN memo_column TEXT(255)
the memo field WILL NOT be altered and the query will generate an error unless all your memo fields have less than 255 characters in them

so... DO NOT alter MEMO fields to TEXT unless all the MEMO fields in your table are empty or contain less than max text type filed length characters (255 default).

but my bigger advise to you will be to stop using ms access as your db and switch to a proper database.

Azadi
>> but my bigger advise to you will be to stop using ms access as your db and switch to a proper database.
I agree. After these such issues, i really started hating Access. But in this Scenario, it is not a choice, it is required..
As far You explained in your post. thanks.
But my alter command also fails over where
  • if i have  a text field, i add a length of say 100 using alter table xxxx alter column user text (100). what it is doing is it converts that text field to memo. 
  • Here it should not convert it into MEMO. as i am editing a text field and adding a length. once it to goes, my Alter Command Fails.
see here below in the code window

i use this:
 
<cfoutput>
      <cfloop from="1" to="#listLen(form.tcolumnName)#" index="idx">
	ALTER table #list_name#
	ALTER COLUMN #ListGetAt(form.tcolumnName, idx)# <!---#ListGetAt(form.fieldtype, idx)#---> 
    #form['fieldtype_' & idx]# 
	<cfif isdefined(form['fieldtype_' & idx]) AND len(trim(form['fieldtype_' & idx])) EQ "TEXT">
    (#form['tlength_' & idx]#)
    <cfelse>
    </cfif>
      </cfloop>
      </cfoutput>
 
so if i am editing the text field, then the <cfif statement should get executed to allot a field length to text datatype but it converts the text field to memo and fails the length attribute.
 
So if i am doing wrong here, suggest me a better Approch/
 
I am really dying here

Open in new window

first, your CFIF statement is wrong:
<cfif isdefined(form['fieldtype_' & idx]) AND len(trim(form['fieldtype_' & idx])) EQ "TEXT">

a) you are checking if form['fieldtype_' & idx] is defined, but you have already used that variable on previous code line - too late to check for isdefined now!
b) with len(trim(form['fieldtype_' & idx])) EQ "TEXT" you are trying to compare LENGTH of field value to STRING "TEXT" - surely you want to compare the VALUE of the field, not its LENGTH!

basically, your CFIF statement is ALWAYS false, which makes your ALTER TABLE statement ALWAYS look like:
ALTER TABLE xxx ALTER COLUMN yyy TEXT
(without any field size).

this converts your field to a MEMO field. i have replicated this in my test just now.
strange - i thought it would assume default max text field size there, but it does not...

change your <cfif> to:

<cfif form['fieldtype_' & idx] EQ "TEXT">
<cfif isdefined("form['tlength_' & idx]") AND form['tlength_' & idx] GT 0 AND form['tlength_' & idx] LTE 255>(#form['tlength_' & idx]#)<cfelse>(255)</cfif>
</cfif>

and all should be good... at least with your TEXT fields...

Azadi
I encountered an error while executing:
Parameter 1 of function IsDefined, which is now form['tlength_' & idx], must be a syntactically valid variable name.
 
The error occurred in C:\Inetpub\wwwroot\Classic\home.cfm: line 93
Called from C:\Inetpub\wwwroot\Classic\home.cfm: line 58
Called from C:\Inetpub\wwwroot\Classic\home.cfm: line 1

91 :     #form['fieldtype_' & idx]# 
92 :       <cfif form['fieldtype_' & idx] EQ "TEXT">
93 :       <cfif isdefined("form['tlength_' & idx]") AND form['tlength_' & idx] GT 0 AND form['tlength_' & idx] LTE 255>
94 :       (#form['tlength_' & idx]#)<cfelse>(255)</cfif>
95 :       </cfif>

My Alter Statement as:

ALTER table #list_name#
 ALTER COLUMN #ListGetAt(form.tcolumnName, idx)# <!---#ListGetAt(form.fieldtype, idx)#---> 
    #form['fieldtype_' & idx]# 
 <cfif form['fieldtype_' & idx] EQ "TEXT">
 <cfif isdefined("form['tlength_' & idx]") AND form['tlength_' & idx] GT 0 AND form['tlength_' & idx] LTE 255>
 (#form['tlength_' & idx]#)<cfelse>(255)</cfif>
 </cfif>
ALTER table test ALTER COLUMN id COUNTER ALTER table test ALTER COLUMN Class TEXT     The web site you are accessing has experienced an unexpected error.
Please contact the website administrator. 

i just tried entering 100 to text field and i got above error.


right.
change the isdefined(...) part to:
structkeyexists(form, 'tlength_' & idx)
(my preferred method of checking for variable existence - helps avoid scope confusions...)

...or you can use isdefined("form.tlength_#idx#")

Azadi
This is completely irrelevant to your question.  However, your isStringType function is displaying a strange use of a list function on a variable that isn't a list:
<cfset var strtypes = "text">
 <cfset var result = false>
 <cfif ListFindNoCase(strtypes,arguments.type)>
       <cfset result = true>
 </cfif>

Why not just:
 <cfset var result = false>
 <cfif arguments.type EQ "text">
       <cfset result = true>
 </cfif>
>> This is completely irrelevant to your question.  However, your isStringType function is displaying a strange use of a list function on a variable that isn't a list:
But I did not understood what you are trying to explain here. can u be more versatile on this or i mean can u explain it in detail
<cfif ListFindNoCase(strtypes,arguments.type)>

means, if you find the string #arguments.type# inside the list #strtypes#.

However strtypes isn't a list, it's just a single value, "text".  I guess maybe this function has been adapted from another one that had a list of values, e.g. "text,memo,varchar" would be a list.  

The function will work, but it's not as efficient as it could be.  Also, list functions are notoriously slow.  Just removing the ListFindNoCase will improve performance of your application (a tiny bit).
ASKER CERTIFIED SOLUTION
Avatar of azadisaryev
azadisaryev
Flag of Hong Kong image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@ azadi..

Yes i tried that it working, but i am heading in trouble with MEMO field.

Till i am debugging from that time you provided me solution for the text field. I cam around MEMO problem.

It was said that questions should not be continued from other perspective point of problem if occurs, a new question should be started. So to avoid ambiguity i am just playing with MEMO stuff, and Hopefully if not resolved by me, I will definitely as u experts.

Cheers