SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 10 of 10
  1. #1
    Member
    Join Date
    Apr 2011
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Writing output to excel using DB_Connect..

    Hi All,

    With the help few other posts from this site, i was able to write small function in which am storing records in excel file.

    Problem here is : when ever i try to execute the code, it return error saying :

    Code:
    Type mismatch - Parameter version to function WriteResToExcel should be STRING
    even i have declared variable version as string.. am still this error..

    Any idea what would be the problem..

    Note: am new silk test tool

    Code:
    [-] WriteResToExcel(in STRING id, in STRING message_time, in STRING version, in STRING conversation, in STRING message)
    	[ ] STRING sResultFile = "C:\AUTOMATION\Results.xls"
    	[ ] HDATABASE hdbc 
    	[ ] HSQL  hBufferInsert
    	[ ] 
    	[ ] //STRING sConnectStringInsert = "DSN=Segue DDA Excel;ReadOnly=False;DBQ={sResultFile}"
    	[ ]  //hdbc = DB_Connect (sConnectStringInsert)
    	[ ] hdbc = DB_Connect ("dsn=SegueDDAExcel;ReadOnly=False;DBQ={sResultFile}")
    	[ ] 
    	[ ] STRING sSQLInsert = "INSERT INTO `Sheet1$`(column_1, column_2, column_3, column_4, column_5) VALUES ('{id}','{message_time}','{version}','{conversation}','{message}')"
    	[ ] 
    	[ ]  hBufferInsert = DB_ExecuteSQL(hdbc, sSQLInsert)
    	[ ] DB_FinishSql (hBufferInsert)
    	[ ] DB_Disconnect (hdbc)
    
    testcase DBTest_PrintRecords () appstate none 
    WriteResToExcel(id,message_time,version,conversation,message)
    <font class="small">Code:</font><hr /><pre> </pre><hr />

  2. #2
    Member
    Join Date
    May 2009
    Location
    Russia
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Writing output to excel using DB_Connect..

    It seams that function is fine.

    It seems you are trying to pass variable @version like number, but it is need to be a String. Try type conversion:
    <font class="small">Code:</font><hr /><pre>
    WriteResToExcel(id,message_time,Str(version),conve rsation,message)
    </pre><hr />

    If it doesn't help try to debug [img]/images/graemlins/wink.gif[/img]

  3. #3
    Member
    Join Date
    Apr 2011
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Writing output to excel using DB_Connect..

    Thanks Vasily... I got rid of the error...

    but when i try to execute the code, the function return the error saying :

    <font class="small">Code:</font><hr /><pre>
    [ ] -------------------------------------------------------------
    [ ] Number of records present in table: 200
    [ ] -------------------------------------------------------------
    [ ] Here's the record info present in the dashboard_response_summary table:
    [ ]
    [ ] ################################################## #############
    [ ] 1 2011-02-06 10:00:00 5 1 6
    [ ] ################################################## #############
    [ ] AGT_SYS_LogClientMessage (3, "TCS_EXITING - TestCase: NULL/DBTest_PrintRecords")
    [ ] *** Error: (IM014) [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application
    [ ] Occurred in DB_Connect
    [ ] Called from WriteResToExcel at generics6.t(269)
    [ ] Called from DBTest_PrintRecords at generics6.t(320)

    </pre><hr />

    I have created DSN by name"silk".. there seems to be error in this line from code which is in WriteResToExcel function
    <font class="small">Code:</font><hr /><pre>
    hdbc = DB_Connect ("dsn=silk;ReadOnly=False;DBQ={sResultFile}")
    </pre><hr />


    WriteResToExcel function code :
    <font class="small">Code:</font><hr /><pre>
    [-] WriteResToExcel(in STRING id, in STRING message_time, in STRING version, in STRING conversation, in STRING message)
    [ ] STRING sResultFile = "C:\dashboard_response_summary.xls"
    [ ] HDATABASE hdbc
    [ ] HSQL hBufferInsert
    [ ]
    [ ] hdbc = DB_Connect ("dsn=silk;ReadOnly=False;DBQ={sResultFile}")
    [ ] STRING sSQLInsert = "INSERT INTO `Sheet1$`(column_1, column_2, column_3, column_4, column_5) VALUES ('{id}','{message_time}','{version}','{conversatio n}','{message}')"
    [ ] hBufferInsert = DB_ExecuteSQL(hdbc, sSQLInsert)
    [ ] DB_FinishSql (hBufferInsert)
    [ ] DB_Disconnect (hdbc)
    [ ]
    </pre><hr />

    Main function from where "WriteResToExcel" is called
    <font class="small">Code:</font><hr /><pre>
    [ ] // connect to SQL Server Test DB
    [ ] hdbc = DB_Connect ("dsn=GEN;srvr=abc,PWD=testadmin;UID=testadmin" )
    [ ]
    [ ] // retrieve info from dashboard_response_summary table
    [ ] hstmnt = DB_ExecuteSql (hdbc, "SELECT CONVERT(id,CHAR), CONVERT(message_time,DATETIME), version, CONVERT(conversation,CHAR), CONVERT(message,CHAR) FROM dashboard_response_summary")
    [ ] print()
    [ ] query = "SELECT CONVERT(id,CHAR), CONVERT(message_time,DATETIME), version, CONVERT(conversation,CHAR), CONVERT(message,CHAR) FROM dashboard_response_summary"
    [ ] Table_RecCount(query,iCount)
    [ ] // process the information that came back
    [ ] print ("Here's the record info present in the dashboard_response_summary table:")
    [ ] print()
    [-] for i =1 to iCount
    [ ] DB_FetchNext (hstmnt, id, message_time,version,conversation,message)
    [ ] arr_id[i] = id
    [ ] arr_message_time[i] = message_time
    [ ] arr_version[i] = version
    [ ] arr_conversation[i] = conversation
    [ ] arr_message[i] = message
    [ ] print("########################################### ####################")
    [ ] print(arr_id[i], arr_message_time[i], arr_version[i], arr_conversation[i], arr_message[i])
    [ ] print("########################################### ####################")
    [ ] //WriteResToExcel(id, message_time, version, conversation, message)
    [ ] WriteResToExcel(id,message_time,Str(version),conve rsation,message)

    </pre><hr />


    Thanks,
    Jay

  4. #4
    Member
    Join Date
    Apr 2011
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Writing output to excel using DB_Connect..

    Hi All,

    I tried different solution and this time around am getting this error :

    error code :
    <font class="small">Code:</font><hr /><pre>
    [ ] AGT_SYS_LogClientMessage (3, "TCS_EXITING - TestCase: NULL/DBTest_PrintRecords")
    [ ] *** Error: (HY000) [Microsoft][ODBC Excel Driver] External table is not in the expected format.
    [ ] Occurred in DB_Connect
    [ ] Called from WriteResToExcel at generics6.t(271)
    [ ] Called from DBTest_PrintRecords at generics6.t(327)

    </pre><hr />

    Main function :
    <font class="small">Code:</font><hr /><pre>
    [-] testcase DBTest_PrintRecords () appstate none
    [ ]
    [ ] // This test uses the functions in DBTester to directly access an ODBC database
    [ ]
    [ ] // Variables declaration
    [ ]
    [ ] INTEGER version,rCount,iCount=0
    [ ] //NUMBER value
    [ ] ANYTYPE conversation, message, id, message_time, xyz, rowcount=0, i=0, j=1, a
    [ ] //DateTime message_time
    [ ] HDATABASE hdbc
    [ ] HSQL hstmnt,hstmnt1,hstmt,hstmt1
    [ ] STRING query
    [ ] LIST OF ANYTYPE la
    [ ] ARRAY[ 600] OF ANYTYPE arr_id
    [ ] ARRAY[ 600] OF ANYTYPE arr_message_time
    [ ] ARRAY[ 600] OF ANYTYPE arr_version
    [ ] ARRAY[ 600] OF ANYTYPE arr_conversation
    [ ] ARRAY[ 600] OF ANYTYPE arr_message
    [ ] ARRAY[1000] OF ANYTYPE Dashboard
    [ ]
    [ ] // connect to SQL Server Test DB
    [ ] hdbc = DB_Connect ("dsn=GEN;srvr=abct,PWD=testadmin;UID=testadmin ")
    [ ]
    [ ] // retrieve info from dashboard_response_summary table
    [ ] hstmnt = DB_ExecuteSql (hdbc, "SELECT CONVERT(id,CHAR), CONVERT(message_time,DATETIME), version, CONVERT(conversation,CHAR), CONVERT(message,CHAR) FROM dashboard_response_summary")
    [ ] print()
    [ ] query = "SELECT CONVERT(id,CHAR), CONVERT(message_time,DATETIME), version, CONVERT(conversation,CHAR), CONVERT(message,CHAR) FROM dashboard_response_summary"
    [ ] Table_RecCount(query,iCount)
    [ ] // process the information that came back
    [ ] print ("Here's the record info present in the dashboard_response_summary table:")
    [ ] print()
    [-] for i =1 to iCount
    [ ] DB_FetchNext (hstmnt, id, message_time,version,conversation,message)
    [ ] arr_id[i] = id
    [ ] arr_message_time[i] = message_time
    [ ] arr_version[i] = version
    [ ] arr_conversation[i] = conversation
    [ ] arr_message[i] = message
    [ ] print("########################################### ####################")
    [ ] print(arr_id[i], arr_message_time[i], arr_version[i], arr_conversation[i], arr_message[i])
    [ ] print("########################################### ####################")
    [ ] //WriteResToExcel(id, message_time, version, conversation, message)
    [ ] WriteResToExcel(id,message_time,Str(version),conve rsation,message)
    [ ]
    [ ] // FileHandle = FileOpen("C:\dashboard_response_summary.csv", FM_APPEND)
    [ ] // FileWriteLine(FileHandle,arr_id[i] + arr_message_time[i])
    [ ] // FileClose(FileHandle)
    [ ]
    [ ] //DB_FinishSQL (hstmt)
    [ ] DB_FinishSQL (hstmnt)
    [ ] // disconnect
    [ ] DB_Disconnect (hdbc)

    </pre><hr />

    Sub function :
    <font class="small">Code:</font><hr /><pre>
    [-] WriteResToExcel(in STRING id, in STRING message_time, in STRING version, in STRING conversation, in STRING message)
    [ ] STRING sResultFile = "C:\dashboard_response_summary.xlsx"
    [ ] HDATABASE hdbc
    [ ] HSQL hBufferInsert
    [ ]
    [ ] //hdbc = DB_Connect ("dsn=SegueDDAExcel;ReadOnly=False;DBQ={sResultFil e};UID=;PWD=")
    [ ]
    [ ] hdbc = DB_Connect ("DRIVER=Microsoft Excel Driver (*.xls);DRIVERID=790;READONLY=FALSE;DBQ=C:\dashboa rd_response_summary.xlsx")
    [ ]
    [ ] //hdbc = DB_Connect ("DRIVER=Microsoft Excel-Treiber (*.xls); FIRSTROWHASNAMES=1;READONLY=FALSE; DRIVERID=790;DBQ={sResultFile}")
    [ ]
    [ ] //hdbc = DB_Connect ("DRIVER=Microsoft Excel Driver (*.xls);DRIVERID=790; FIRSTROWHASNAMES=1;READONLY=FALSE;DBQ={sResultFile }")
    [ ]
    [ ] STRING sSQLInsert = "INSERT INTO `Sheet1$`(column_1, column_2, column_3, column_4, column_5) VALUES ('{id}','{message_time}','{version}','{conversatio n}','{message}')"
    [ ] hBufferInsert = DB_ExecuteSQL(hdbc, sSQLInsert)
    [ ] DB_FinishSql (hBufferInsert)
    [ ] DB_Disconnect (hdbc)

    </pre><hr />

    What i have done is, before executing this , i have created dashboard_response_summary.xlsx file in c:\ drive and added all the column name as present in the data table in database....

    Is there anything i needs to add in the excel file..???

    [ ] *** Error: (HY000) [Microsoft][ODBC Excel Driver] External table is not in the expected format.

  5. #5
    Senior Member
    Join Date
    Jan 2005
    Location
    UK
    Posts
    334
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Writing output to excel using DB_Connect..

    From your previous post, it seems like you have identified arr_version as Array of AnyType. You should be more specific as to what the Data Type should be, as it will make it less complicated as to when you read the values from the spreadsheet. Also, the values in your spreadsheet should also be formatted correctly i.e. whether itís Text, Number, General etc.

    If you are using an Excel spreadsheet then the DSN should be in the form of "DSN=Silk DDA Excel; DBQ=C:\CarsOld.xlsx; UID=; PWD=;"

  6. #6
    Member
    Join Date
    Apr 2011
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Writing output to excel using DB_Connect..

    Hi Rakesh,

    I made changes as you told but still getting same error..

    any idea why it throwing this error :

    <font class="small">Code:</font><hr /><pre>
    Error: (HY000) [Microsoft][ODBC Excel Driver] External table is not in the expected format.
    </pre><hr />

    xlsx file attached

    Thanks,
    Jay Error: (HY000) [Microsoft][ODBC Excel Driver] External table is not in the expected format.

  7. #7
    Member
    Join Date
    Apr 2011
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Writing output to excel using DB_Connect..

    Sorry forgot to attach the file


    i have *.txt format but excel file will have same column header

  8. #8
    Member
    Join Date
    Apr 2011
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Writing output to excel using DB_Connect..

    Sorry forgot to attach the file


    i have attached *.txt format but excel file will have same column header
    Attached Files Attached Files

  9. #9
    Senior Member
    Join Date
    Jan 2005
    Location
    UK
    Posts
    334
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Writing output to excel using DB_Connect..

    I managed to get the same error, after playing around with the ODBC version settings for Silk DDA Excel. I changed the version from 'Excel 97-2000' to 'Excel 3.0', and then got the HY000 ODBC error message. I then reverted back to the original settings, restarted my computer and was still getting the same error message.

    The only way I managed to resolve this was to read an old Excel spreadsheet (.xls) first, now Iím able to read new Excel spreadsheet (.xlsx). There may be a bug in the ODBC drivers.

    Come to think of it, Excel 2010 spreadsheet is not supported only Excel 97-2000 are

  10. #10
    Member
    Join Date
    Apr 2011
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Writing output to excel using DB_Connect..

    Hi Rakesh,

    Sorry for replying bit late...Thanks for your input &amp; help..

    Finally able to resolve the issue.

    just changed the excel file version from 2010 to excel 97 -2003 woorkbook &amp; everything worked fine.

    So, Just to help other who has windows 7 OS, all you have to do is save your excel file as excel 97-2003 workbook.

    Final version code :
    &lt;CODE&gt;
    [-] WriteResToExcel(in STRING id, in STRING message_time, in STRING version, in STRING conversation, in STRING message)
    [ ] STRING sResultFile = "C:\dashboardresponsesummary.xls"
    [ ] HDATABASE hdbc
    [ ] HSQL hBufferInsert,hstmnt
    [ ] REC_DATA_SUMMARY summary
    [ ]
    [ ] hdbc = DB_Connect("DSN=Silk DDA Excel; DBQ=C:\dashboardresponsesummary.xls; UID=; PWD=;")
    [ ] STRING sSQLInsert = "INSERT INTO `Sheet1$` VALUES ('{id}','{message_time}','{version}','{conversatio n}','{message}')"
    [ ] //STRING sSQLInsert = "INSERT INTO `Sheet1$` VALUES ({summary.id}','{summary.message_time}','{summary. version}','{summary.conversation}','{summary.messa ge}')"
    [ ] hBufferInsert = DB_ExecuteSQL(hdbc, sSQLInsert)
    [ ] DB_FinishSql (hBufferInsert)
    [ ] DB_Disconnect (hdbc)
    [ ]

    &lt;/CODE&gt;

    Jay

 

 

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.34%
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 06:49 PM.

Copyright BetaSoft Inc.