?
Solved

Time parameter for dbisql batch file

Posted on 2004-08-13
9
Medium Priority
?
924 Views
Last Modified: 2012-06-27
I currently have a VB6 ADO program extracting data from an ASA5.0 db and find that it only works partially against an ASA6.0 version of the same DB (nt4 sytem), I keep getting a E-Fail status msg on an existing table with data.  

Iam new to dbisql but find that I  can export the data via dbisql and all is well.  So I'm building a batch file to do so but need to pass in a date parameter.  I can use the system date or a specific date from one of the db tables (would prefer to use the latest date in the table).

proposed code for one table in batch file

select A.maj_grp_seq, A.obj_num, A.cat, A.name, B.business_date, B.sls_cnt, B.sls_ttl, B.rtn_cnt, B.rtn_ttl, B.dsc_ttl FROM micros.maj_grp_def A, micros.dly_sys_maj_grp_ttl B WHERE A.maj_grp_seq = B.maj_grp_seq AND business_date = {date};

command line
READ batch.sql '#08/13/2004#'

I am unclear on how to set the date as a variable in the batch file for dbisql either from the system or table, is this run as a dos batch file? could then  set an environment variable = time.
0
Comment
Question by:rk_eliz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 14

Expert Comment

by:Jan Franek
ID: 11795337
I'm afraid, that you have to use some text manipulation programs to create sql batch with filled in date. You need something, that takes that date and creates sql batch using this date. Then you can run dbisql with that batch.

BTW, don't use # to indicate date - just '08/13/2004'.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11795621
>>I currently have a VB6 ADO program extracting data from an ASA5.0 db and find that it only works partially against an ASA6.0 version of the same DB (nt4 sytem), I keep getting a E-Fail status msg on an existing table with data.  

What the connection string you are using?  Can you show some of the code?  I may be able to fix it.

Leon
0
 

Author Comment

by:rk_eliz
ID: 11795980
the code is as follows:
Public Sub Table5()
    Dim p As String
    r = "Time_card_export"
    l = r
    sSQL = "select * FROM micros.v_R_employee_time_card;"
    Set m_oRecordset = New ADODB.Recordset
    m_oRecordset.Open sSQL, oConnection1, adOpenStatic, _
                        adLockBatchOptimistic, adCmdText
    m_oRecordset.Filter = "business_date >= " & j
    Set RecB = m_oRecordset
    Call RecordSetProcess(RecB, 5, r)
End Sub
the filter is using a previously set date, referenced in my original question.  Also note that this is dumping a view not a table.  within dbisql not a problem, also not in asa5.0.  This is the code used to connect with asa5.0
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 29

Expert Comment

by:leonstryker
ID: 11796167
I would need to see oConnection1 connection string.

BTW, why can't you use:

sSQL = "select * FROM micros.v_R_employee_time_card  WHERE business_date >= " & j

instead of using the Filter?

>>Also note that this is dumping a view not a table.  within dbisql not a problem, also not in asa5.0.
 
It should not matter.  Did you change you connection string then you switch from ASA5.0 to ASA6.0?

Leon
0
 

Author Comment

by:rk_eliz
ID: 11811419
the connection string is:
    m_sConnStr = "Provider=MSDASQL.1;Password=custom;User ID=custom;Data Source=micros"
    Set oConnection1 = New ADODB.Connection
    oConnection1.CursorLocation = adUseClient
    oConnection1.Open m_sConnStr
---------------
tried making the date as part of the where clause but either the connection or provider doesn't respond to it but will respond to the filter.  would much rather do it as a where clause.
---------------------
no change in the connection string from asa5.0 to asa6.0

Richard
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 11812842
Richard,

Have you tried using the ASA OLEDB provider?  You are usually better of with native drivers than the MS ones.

Try this:

m_sConnStr = "Provider=ASAProv; Data source=micros"

Leon
0
 

Author Comment

by:rk_eliz
ID: 12058184
none of the above suggestions helped in the resolution, what I did do was a call to the sybase utility dbisql.exe to export the table and massaged it from there.  Thanks. Richard
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12096720
PAQed, with points refunded (150)

modulo
Community Support Moderator
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

New style of hardware planning for Microsoft Exchange server.
Check out the latest tech news, community articles, and expert highlights in August's newsletter.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question