Solved

Time parameter for dbisql batch file

Posted on 2004-08-13
9
887 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
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How do we check sybase license in ASE 1 2,863
Clean up a mailbox 5 157
Sysbase Central connection fails 3 62
QUerry require with a procedure. 4 14
In this article we will learn how to backup a VMware farm using Nakivo Backup & Replication. In this tutorial we will install the software on a Windows 2012 R2 Server.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

778 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