1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.
  2. Better Analytics for WordPress Get It Free

Altering database dynamicaaly

Discussion in 'Programming' started by dodo123456, Feb 13, 2009.

  1. #1
    Hi Guys, I am trying to dynamically alter a table colums, its datatypes etc:

    i use command like this:

    i have listed all the column Names, its data TYpes like this:

    id int 3
    name varchar 10
    class varchar 10

    i though of doing a loop in alter table command but it is not working that way:

    i am getting an error/ of:

    Complex object types cannot be converted to simple values.

    The expression has requested a variable or an intermediate expression result as a simple value, however, the result cannot be converted to a simple value. Simple values are strings, numbers, boolean values, and date/time values. Queries, arrays, and COM objects are examples of complex values.
    The most likely cause of the error is that you are trying to use a complex value as a simple one. For example, you might be trying to use a query variable in a cfif tag.


    The error occurred in C:\Inetpub\wwwroot\abc\index.cfm: line 12
    10 : ALTER table #form.tableName#
    11 : MODIFY
    12 : <cfloop list="#form#" index="i">
    13 : (#form.columnName# #form.texttype#(#length#) #nullornot#);
    14 : </cfloop>

    i know i am wtong but could not figure what is wrong i am doing
     
    dodo123456, Feb 13, 2009 IP
  2. Paul_K

    Paul_K Greenhorn

    Messages:
    85
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    18
    #2
    Take this code out of the cfloop and simply cfoutput it

    ALTER table #form.tableName#
    11 : MODIFY
    12 : <cfloop list="#form#" index="i">
    13 : (#form.columnName# #form.texttype#(#length#) #nullornot#);
    14 : </cfloop>

    Look at the SQL its producing.
     
    Paul_K, Feb 13, 2009 IP
  3. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #3
    I hope you realize that is very dangerous code. Just do a search on: ColdFusion SQL Injection
     
    cfStarlight, Feb 13, 2009 IP
  4. dodo123456

    dodo123456 Peon

    Messages:
    3
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    if i omit loop.

    i am an error: and that is like this:

    Error Executing Database Query.
    [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '('.

    The error occurred in


    ALTER table grcountry MODIFY (country,country_name int identity,varchar(10,255) on)
    VENDORERRORCODE 102
     
    dodo123456, Feb 13, 2009 IP
  5. Paul_K

    Paul_K Greenhorn

    Messages:
    85
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    18
    #5
    Sorry I meant:

    Take this code out of the cfquery and simply cfoutput it

    ALTER table #form.tableName#
    11 : MODIFY
    12 : <cfloop list="#form#" index="i">
    13 : (#form.columnName# #form.texttype#(#length#) #nullornot#);
    14 : </cfloop>

    Look at the SQL its producing.
    Edit/Delete Message
     
    Paul_K, Feb 13, 2009 IP
  6. cfStarlight

    cfStarlight Peon

    Messages:
    398
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #6
    That statement is invalid for several reasons. I would suggest you first review the BOL to understand how to properly construct an ALTER statement. Then figure out how to construct it with CF.

    http://msdn.microsoft.com/en-us/library/ms190273.aspx

    That said, the posters on your other thread raised a very valid point. _Why_ do you need to modify every column in your table?

    http://forums.devshed.com/coldfusion-development-84/altering-table-590565.html
     
    cfStarlight, Feb 17, 2009 IP