Bindings, but when I click the + in "Bindings", it prompted me that "Error Executing Database Query", and no attributes can be displayed. What would be the reason? Is that OK for me not to insert the data into the temp table? I have tried in fact but still not work. I have also tried to use instead of but still no luck. Please give me some hints. [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => ?? [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 3 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> Queries of query & Stored procedure - Coldfusion - Advanced Techniques

Queries of query & Stored procedure - Coldfusion - Advanced Techniques

Hi everybody, I know that there is a function called "Queries of query" in Coldfusion, and I can call stored procedure from the database as well. However, I can never use these 2 functions. When I use <cfquery> to get the data from another query, an error occured, showing that it is invalid. That is also happened when I use <cfstoredproc> to call SP from database. Is that the problem of my Dreamweaver MX 2004, Coldfusion MX 7, MSSQL 2000, or I need to do some extra steps b4 I can use these functions? Please advice. Many thanks...

  1. #1

    Default Queries of query & Stored procedure

    Hi everybody,

    I know that there is a function called "Queries of query" in Coldfusion, and I
    can call stored procedure from the database as well. However, I can never use
    these 2 functions. When I use <cfquery> to get the data from another query, an
    error occured, showing that it is invalid. That is also happened when I use
    <cfstoredproc> to call SP from database. Is that the problem of my Dreamweaver
    MX 2004, Coldfusion MX 7, MSSQL 2000, or I need to do some extra steps b4 I can
    use these functions? Please advice. Many thanks

    ?? Guest

  2. #2

    Default Re: Queries of query & Stored procedure

    Post the code giving you trouble and also what the error message is.

    --
    Adam
    Adam Guest

  3. #3

    Default Re: Queries of query & Stored procedure

    Hi, Thanks in advanced.

    Here is my stored procedure code in MSSQL:

    CREATE PROCEDURE dbo.dt_storedprocedureID nvarchar (10) AS

    DECLARE tbl_record table(date datetime, name nvarchar(30), course
    nvarchar(20), class int, equipment nvarchar(30))

    INSERT INTO tbl_record

    SELECT history.date, trainer.trainerName, course.courseName,
    history.className, history.equipment

    FROM dbo.tbl_history history, dbo.tbl_trainer trainer,
    dbo.tbl_course course, dbo.tbl_status status

    WHERE history.historyID = ID
    AND history.trainerID = trainer.trainerID AND history.courseID =
    course.courseID

    GROUP BY history.date, trainer.trainerName, course.courseName,
    history.className, history.equipment
    GO

    I tried to put the data into a temp table, tbl_record, and then get it back
    from Dreamweaver by cf code

    Here is the code in Dreamweaver:

    <cfstoredproc procedure="dt_storedprocedure"
    datasource="#Application.datasource#"
    <cfprocparam type="IN" CFSQLType="cf_sql_longvarchar" dbvarname="ID"
    value="#ID#">
    <cfprocresult name="sp_Season">
    </cfstoredproc>

    The sp_Season show in Dreamweaver --> Bindings, but when I click the + in
    "Bindings", it prompted me that "Error Executing Database Query", and no
    attributes can be displayed. What would be the reason? Is that OK for me not to
    insert the data into the temp table? I have tried in fact but still not work. I
    have also tried to use <cfquery> instead of <cdstoredproc> but still no luck.
    Please give me some hints.




    ?? Guest

  4. #4

    Default Re: Queries of query & Stored procedure

    ??,


    1. It is a good idea to first test your procedures in ms sql before trying
    them with CF. If you run your procedure in ms sql you'll see that it does not
    return a resultset.

    The results are inserted into a table variable tbl_record, but you're not
    returning anything from the procedure. To return the results you would need to
    do a SELECT from tbl_record. Although, if this is all your stored procedure is
    doing, you don't really need to use a table variable. You could just run the
    SELECT statement.

    2. You include the "dbo.tbl_status status" table in your FROM statement but it
    doesn't look like you're using that table. You should remove it from the
    statement if is not used.

    3. I don't think you need a GROUP BY statement here since you're not using any
    aggregate functions. If you have legimate duplicates in your results, you
    might consider using SELECT DISTINCT

    4. Be careful with your column names. "Date" can be a reserved word in many
    cases. You might consider renaming this column to avoid potential problems.

    5. It is a good idea to use BEGIN ... END to enclose multiple procedure
    statements.
    CREATE PROCEDURE myProcedure
    AS
    BEGIN
    .... sql code here ...
    END
    GO

    Try something like the attached



    CREATE PROCEDURE dbo.dt_storedprocedure ID nvarchar (10)
    AS
    BEGIN
    SELECT history.date AS HistoryDate, trainer.trainerName AS Name,
    course.courseName AS course, history.className AS class,
    history.equipment
    FROM dbo.tbl_history history, dbo.tbl_trainer trainer, dbo.tbl_course
    course
    WHERE history.historyID = ID AND
    history.trainerID = trainer.trainerID AND
    history.courseID = course.courseID
    END
    GO

    mxstu Guest

  5. #5

    Default Re: Queries of query & Stored procedure

    ??,

    1. Also, you need to close the cfstoredproc tag . You're missing a ">"

    <cfstoredproc procedure="dt_storedprocedure"
    datasource="#Application.datasource#" missing close symbol >
    <cfprocparam type="IN" CFSQLType="cf_sql_longvarchar" dbvarname="ID"
    value="#ID#">
    <cfprocresult name="sp_Season">
    </cfstoredproc>

    2. Why are you using CFSQLType="cf_sql_longvarchar"? What kind of data type
    is your historyID column?


    mxstu Guest

  6. #6

    Default Re: Queries of query & Stored procedure

    Thanks for ur reply mxstu. I'll try what you have just suggested. Actually, I
    cannot see the table tbl_record created in my database after running the SP in
    query yzer. Is that a problem? I have tested the SP and it can get what I
    want from the database (with or without insert data into the table).

    Is the redundant table included in my FROM / WHERE (such as dbo.tbl_status
    status) or the variable I used that may reserved (e.g. Date) make the cf code
    failed to call the SP?

    ?? Guest

  7. #7

    Default Re: Queries of query & Stored procedure

    >I cannot see the table tbl_record created in my database after running the SP
    in query yzer

    tbl_record is a table variable, which I think only exists for the life of
    your stored procedure. So it makes sense that you cannot see it after the
    procedure is finished.
     

    es, it executes without error but it doesn't return a resultset. For example,
    when you run a cfquery containing a SELECT statement. The data returned by the
    SELECT statement is the "resulset". In your case, your stored procedure is not
    executing a SELECT statement and therefore not returning a "resultset".
     
    status) 
    to call the SP?

    No, they are probably not the cause of your current error, but they can cause
    other problems.


    mxstu Guest

  8. #8

    Default Re: Queries of query & Stored procedure

    Is that mean if I remove these 2 lines:

    DECLARE tbl_record table(date datetime, name nvarchar(30), course
    nvarchar(20), class int, equipment nvarchar(30))

    INSERT INTO tbl_record

    The stored proc will execute the SELECT statement and therefore return a
    resultset?


    ?? Guest

  9. #9

    Default Re: Queries of query & Stored procedure

    OR, how can i make the stored proc to return a resultset?
    ?? Guest

  10. #10

    Default Re: Queries of query & Stored procedure

    Put a select query in it.

    Originally posted by: ??
    OR, how can i make the stored proc to return a resultset?


    Dan Guest

  11. #11

    Default Re: Queries of query & Stored procedure

    Originally posted by: ??
    Is that mean if I remove these 2 lines:

    DECLARE tbl_record table(date datetime, name nvarchar(30), course
    nvarchar(20), class int, equipment nvarchar(30))

    INSERT INTO tbl_record

    The stored proc will execute the SELECT statement and therefore return a
    resultset?


    Yes. It will return a resultset.


    mxstu Guest

  12. #12

    Default Re: Queries of query & Stored procedure

    Thank you. Both of ur solutions solved my problem. I have 2 more questions:

    1) I originally have some <cfloop> to do some looping in calling the data from
    database. Can I move this part into stored proc? How?

    2) Can I put more than one SELECT statement into the SP so that it can run
    them at the same time?

    ?? Guest

  13. #13

    Default Re: Queries of query & Stored procedure

    ??,

    Sorry for the delay. 1) Yes, you can loop in a stored procedure but using a
    WHERE IN ... clause is usually more a efficient method. 2) Yes, you can have
    more than one SELECT statement in a stored procedure.




    mxstu Guest

Similar Threads

  1. Replies: 3
    Last Post: December 3rd, 02:14 AM
  2. Replies: 2
    Last Post: August 6th, 07:08 PM
  3. Simple Stored Procedure query
    By Paul in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 14th, 08:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •