SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 8 of 8
  1. #1
    Senior Member
    Join Date
    Jan 2007
    Posts
    147
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Dynamic content in DB_FetchNext

    Hi All,

    I am trying to write a general method to extract data from my data sources. Was trying the following:

    Created a string variable sColumnNames which contains all the columns in the Table and then ran the DB_FetchNext query.

    sColumnNames="sCol1,sCol2"--- Just showing the contents not how I actually create the variable. Then I run,

    while (DB_FetchNext(hsqlStmt,sColumnNames))==TRUE
    ..................

    The query doesnt throw any errors, but it doesnt update any values based on what I should be pulling from the data source. Runs fine, if I explicitly mention the Columns names in the query. I guess that means the query isnt working (DUH!).

    Does anyone have any thoughts on how I can do something similar? I really dont want to write a differnt method for pulling data from the various datasources.

    Thanks

  2. #2
    Senior Member
    Join Date
    Jun 1999
    Location
    Fremont, CA, USA
    Posts
    355
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Dynamic content in DB_FetchNext

    I am assuming you have the SQL Query part correct, but it isn't in your post, so taking it that it is correct.

    Your SQL is returning multiple columns of data. You need to define a variable for each column that you want. There are two ways of doing this:

    Method 1:
    Define a variable for each column.

    string sCol1
    string sCol2
    string sCol3

    while (DB_FetchNext(hsqlStmt,sCol1, sCol2, sCol3))
    // do something


    Method 2:
    Define a record to hold all of the data:

    type DB_RESULTS is record
    string sCol1
    string sCol2
    string sCol3


    DB_RESULTS db
    LIST OF DB_RESULTS lrResults = {}

    while (DB_FetchNext(hsqlStmt,db.sCol1, db.sCol2, db.sCol3))
    ListAppend (lrResults, db)


    Hope this helps,

    David Genrich
    E2open
    Austin, TX

  3. #3
    Senior Member
    Join Date
    Jan 2007
    Posts
    147
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Dynamic content in DB_FetchNext

    Thanks, David. Have already tried the above. What I am trying to avoid is defining the columns in the manner above. That way if I have 2 tables with different number of columns, I can still use the same method. Anyway I know why what I was attempting to do, wont work. I guess am just going to using multiple logic expressions and capture all the different scenarios (different column numbers)

  4. #4
    Advanced Member
    Join Date
    Oct 1999
    Location
    Chicago, IL
    Posts
    652
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Dynamic content in DB_FetchNext

    We currently do what you say you are trying to do. I ended up creating an entire database class that I use for fetching data from Excel, Access and SQL Server sources. I would say at least 90% of the code is common between the 3 sources. Here's a peek into the GetNextRow method of the common database class...
    <font class="small">Code:</font><hr /><pre>
    [+] BOOLEAN GetNextRow(inout ANYTYPE atRow)
    [ ] //************************************************** *********
    [ ] //* Method: GetNextRow()
    [ ] //*
    [ ] //* Description: Fetches a row from (Excel, Access, etc...).
    [ ] //*
    [ ] //* InOut: atRow - The variable to store the row contents in.
    [ ] //* Usually a record or list of some type.
    [ ] //*
    [ ] //* Returns: TRUE (There are more rows), FALSE (no more rows)
    [ ] //*
    [ ] //* Notes: If you haven't called ExecuteSQL before this, it will
    [ ] //* call it.
    [ ] //*
    [ ] //************************************************** *********
    [ ]
    [ ] // If ExecuteSQL hasn't been called yet, call it to get a SQL Handle
    [-] if (this.SqlHandle == NULL)
    [ ] this.ExecuteSQL()
    [ ]
    [ ] Log.Debug("Fetching next row using SqlHandle: {this.SqlHandle}")
    [ ]
    [ ] return (DB_FetchNext (this.SqlHandle, atRow))
    </pre><hr />

    You can always fetch into a list or a record in your common fetch method. That way, it will fill out how ever many columns it needs to. So depending on what your are trying to get out of the database, you pass the appropriate LIST or record into this method.

  5. #5
    Senior Member
    Join Date
    Jan 2007
    Posts
    147
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Dynamic content in DB_FetchNext

    Hi Brian,

    Thanks. But doesnt the above just return the contents of the first column?

    I have 2 Tables CompanyDetails and UserDetails. CompanyDetails has 4 columns in it and UserDetails has 7 columns. (I dont have a problem with different datatypes). I wanted to make the data extraction process generic to the number of columns it accepts. But I dont think this is going to be possible.

    Thanks once again.

  6. #6
    Senior Member
    Join Date
    Jan 2007
    Posts
    147
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Dynamic content in DB_FetchNext

    Got it! Thanks once again, Brian and David.

    Sometimes the simplest things, cause the muddles.

    LIST of ANYTYPE lsUser ={"sLast","sFirst"}// my column names are sLast and sFirst. This way I can pass in the arguments or Count the columns and run through the below statement.

    [-] while(DB_FetchNext(hsql, lsUser))==TRUE
    [ ] ListAppend(lsLast, lsUser[1])
    [ ] ListAppend(lsFirst, lsUser[2])

  7. #7
    Advanced Member
    Join Date
    Oct 1999
    Location
    Chicago, IL
    Posts
    652
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Dynamic content in DB_FetchNext

    Actually, it will return a row of the contents of whatever the query is you pass it. If you first say...

    DB_ExecuteSql(this.DBConnectionHandle, "SELECT First, Last FROM table")

    Then calling DB_FetchNext, each time it returns the values of both columns. That's why using a list/record gives you flexibility of returning any number of columns. From the SilkTest help, "Alternatively, you can specify a single list variable. Since the list length is dynamic, you will receive all the columns of the row. Likewise, you may specify a record variable that may or may not have fields for all the columns."

    So in your last example, initializing your lsUser with those values really should have no effect.

  8. #8
    Senior Member
    Join Date
    Jan 2007
    Posts
    147
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Dynamic content in DB_FetchNext

    Thanks, Brian. Had to initialize lsUser as have to modify the values stored in it later anyway. Should have read the Help a little more carefully.

 

 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Search Engine Optimisation provided by DragonByte SEO v2.0.36 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 10.71%
vBulletin Optimisation provided by vB Optimise v2.6.4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.2.8 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 03:06 PM.

Copyright BetaSoft Inc.