[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Time parameter for dbisql batch file

Posted on 2004-08-13
9
Medium Priority
?
937 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

656 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