Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
12
Erster Beitrag:
vor 2 Jahren, 11 Monaten
Letzter Beitrag:
vor 2 Jahren, 11 Monaten
Beteiligte Autoren:
Michael Drechsel, Dave B, Alexandre Leclerc, DW, Stefan Bentvelsen, Viggo Poulsen

[WD19] No analysis files availible in Stored Procedures

Startbeitrag von Michael Drechsel am 05.03.2015 13:35

Hi,

another mysterium:

In my new created stored procedures I can´t use any of the files in my analysis (red compiler error)
"HDeclareExternal" doesn´t help.

I have:

1. Repair the project
2. Delete all cpl files

but no success .... Any ideas ?

Antworten:

Michael,

I never worked with stored proc yet, and you probably looked at the help, but at the end of this section they give a code snippet on how to use HDeclareExternal().

http://doc.windev.com/en-US/?3044360#NOTE2_1

Maybe this ca be of help to you?

Best regards,
Alexandre Leclerc

von Alexandre Leclerc - am 05.03.2015 18:17
Hi Alexandre,

>I never worked with stored proc yet,

really ? It´s the best way to let the server do things asynchron, like sending emails etc.
I use this since WD14 and it works like a charm.

>snippet on how to use HDeclareExternal().

As I wrote this doesn´t help. The problem is that none of my analysis files are availibe in Intellisense and not shown for the compiler .... Don´t know how to fix this, in the "normal" procedures all works like expected.

von Michael Drechsel - am 05.03.2015 21:09
Hi Michael,

Yeah, never used it because our application must run both in Classic and HFCS mode.

Well, it looks like a call to the Free Technical Support... maybe something is broken or there is something else that causes that.

Good luck and best regards,
Alexandre Leclerc

von Alexandre Leclerc - am 06.03.2015 15:43
Hi Michael,

I have used stored procedures and indeed you have to declare-external each file you want to use of your analysis. In fact at the time of running the procedure, there is no analysis known by the database. So also no intellisense, etc. But I have an example procedure for you. All external declared files in this procedure are in my analysis. Sorry, comments are in Dutch ...

I hope it will help you.


PROCEDURE SaveRelatie(pvRelatieFields is Variant)
//
// pvRelatieFields heeft formaat: "123","Bedrijfsnaam","Adres","Postcode","Plaats","Landnaam","Telefoon","Telefoon2","Fax","Website","email"
//

LOCAL
lsResult is string
liRelatieID is int
liRelatietypeID is int

dsLand is Data Source
dsRelatie is Data Source
dsRelatietype is Data Source
dsRelatieRelatietype is Data Source
dsChangeLog is Data Source
dsErrorLog is Data Source // voor error-logging

HDeclareExternal("TblRelatie.FIC",dsRelatie)
HDeclareExternal("TblLand.FIC",dsLand)
HDeclareExternal("TblRelatietype.FIC",dsRelatietype)
HDeclareExternal("TblRelatie_TblRelatietype.FIC",dsRelatieRelatietype)
HDeclareExternal("TblChangeLog.FIC",dsChangeLog)
HDeclareExternal("TblErrorLog.FIC",dsErrorLog)

lsResult = 0


// Bepaal huidige waarde RelatieID, 0 = nieuw record, >0 is bestaand record
liRelatieID = Val(Replace(ExtractString(pvRelatieFields,1,""",""",FromBeginning),"""",""))
IF liRelatieID > 0 THEN
IF NOT HReadSeekFirst(dsRelatie,TblRelatieID,liRelatieID) THEN
// Als een bestaand record niet gelezen kan worden,
// wordt automatisch alsnog een nieuw record aangemaakt !
liRelatieID = 0
END
END
IF liRelatieID = 0 THEN
// Voor nieuw record, ID bepalen van Relatietype - NIEUW
IF HReadSeekFirst(dsRelatietype,SoortType,"NIEUW") THEN
liRelatietypeID = dsRelatietype.TblRelatietypeID
ELSE
HReset(dsRelatietype)
dsRelatietype.TblRelatietypeCD = "Nieuw"
dsRelatietype.SoortType = "NIEUW"
IF NOT HAdd(dsRelatietype) THEN
// Write errorlog
HReset(dsErrorLog)
dsErrorLog.ErrorDatum = DateSys()
dsErrorLog.ErrorTijd = TimeSys()
dsErrorLog.TblMedewerkerID = 0
dsErrorLog.Content = "SP - SaveRelatie(); Error writing TblRelatietype: " + ErrorInfo(errCode)
dsErrorLog.CodeContext = "Line 58; IF NOT HAdd(dsRelatietype) THEN ..."
HAdd(dsErrorLog)
ELSE
liRelatietypeID = dsRelatietype.TblRelatietypeID
END
END
HReset(dsRelatie)
dsRelatie.actief = True
dsRelatie.LandPost = ""
dsRelatie.LandFactuur = ""
END

dsRelatie.Bedrijfsnaam = Replace(ExtractString(pvRelatieFields,2,""",""",FromBeginning),"""","")
dsRelatie.AdresBezoek = Replace(ExtractString(pvRelatieFields,3,""",""",FromBeginning),"""","")
dsRelatie.PostcodeBezoek = Replace(ExtractString(pvRelatieFields,4,""",""",FromBeginning),"""","")
dsRelatie.PlaatsBezoek = Upper(Replace(ExtractString(pvRelatieFields,5,""",""",FromBeginning),"""",""))
dsRelatie.LandBezoek = Replace(ExtractString(pvRelatieFields,6,""",""",FromBeginning),"""","")
dsRelatie.Telefoon = Replace(ExtractString(pvRelatieFields,7,""",""",FromBeginning),"""","")
dsRelatie.Telefoon2 = Replace(ExtractString(pvRelatieFields,8,""",""",FromBeginning),"""","")
dsRelatie.telefax = Replace(ExtractString(pvRelatieFields,9,""",""",FromBeginning),"""","")
dsRelatie.Website = Replace(ExtractString(pvRelatieFields,10,""",""",FromBeginning),"""","")
dsRelatie.Email = Replace(ExtractString(pvRelatieFields,11,""",""",FromBeginning),"""","")
IF Left(dsRelatie.achternaam) = "" THEN
dsRelatie.achternaam = dsRelatie.Bedrijfsnaam
END

IF liRelatieID = 0 _OR_ Left(dsRelatie.TblRelatieCD) = "" THEN
// Stel de relatieCD vast, 1e 3 pos. van de naam + 1e 3 pos. van de plaats, geconverteerd naar uppercase.
dsRelatie.TblRelatieCD = Upper(dsRelatie.Bedrijfsnaam[[1 TO 3]] + dsRelatie.PlaatsBezoek[[1 TO 3]])
END

IF Left(dsRelatie.Bedrijfsnaam) = "" THEN
lsResult = "0" + TAB + "Invalid value for 'Bedrijfsnaam'"
ELSE
IF dsRelatie.LandBezoek > "" _AND_ NOT HSeekFirst(dsLand,Landnaam,dsRelatie.LandBezoek) THEN
lsResult = "0" + TAB + "Invalid value for 'LandBezoek', no reference in TblLand"
ELSE
IF liRelatieID = 0 THEN
IF NOT HAdd(dsRelatie) THEN
lsResult = "0" + TAB + "Error writing TblRelatie: " + ErrorInfo(errCode)

HReset(dsErrorLog)
dsErrorLog.ErrorDatum = DateSys()
dsErrorLog.ErrorTijd = TimeSys()
dsErrorLog.TblMedewerkerID = 0
dsErrorLog.Content = "SP - SaveRelatie(); Error writing TblRelatie: " + ErrorInfo(errCode)
dsErrorLog.CodeContext = "Line 103; IF NOT HAdd(dsRelatie) THEN ..."
HAdd(dsErrorLog)

ELSE
// Nieuw TblRelatieID in het resultaat zetten
lsResult = NumToString(dsRelatie.TblRelatieID)

// Schrijf ook een record in TblCangeLog
HReset(dsChangeLog)
dsChangeLog.Entity = "TblRelatie"
dsChangeLog.Changetype = 10 // Constante is hier niet bereikbaar !
dsChangeLog.Identifier = dsRelatie.TblRelatieID
dsChangeLog.ChangeDate = DateSys() + TimeSys() // Server date+time
dsChangeLog.ChangeUser = HRuntimeInfo.Login
IF HAdd(dsChangeLog) THEN
// Niets doen
END

// Koppel relatietype - Nieuw aan deze nieuwe relatie
IF liRelatietypeID > 0 THEN
HReset(dsRelatieRelatietype)
dsRelatieRelatietype.TblRelatieID = dsRelatie.TblRelatieID
dsRelatieRelatietype.TblRelatietypeID = liRelatietypeID
IF NOT HAdd(dsRelatieRelatietype) THEN
// Schrijf errorlog
HReset(dsErrorLog)
dsErrorLog.ErrorDatum = DateSys()
dsErrorLog.ErrorTijd = TimeSys()
dsErrorLog.TblMedewerkerID = 0
dsErrorLog.Content = "SP - SaveRelatie(); Error writing TblRelatie_TblRelatietype: " + ErrorInfo(errCode)
dsErrorLog.CodeContext = "Line 134; IF NOT HAdd(dsRelatieRelatietype) THEN ..."
HAdd(dsErrorLog)
END
END
END
ELSE
IF NOT HModify(dsRelatie) THEN
lsResult = "0" + TAB + "Error modifying TblRelatie: " + ErrorInfo(errCode)

HReset(dsErrorLog)
dsErrorLog.ErrorDatum = DateSys()
dsErrorLog.ErrorTijd = TimeSys()
dsErrorLog.TblMedewerkerID = 0
dsErrorLog.Content = "SP - SaveRelatie(); Error modifying TblRelatie: " + ErrorInfo(errCode)
dsErrorLog.CodeContext = "Line 147; IF NOT HModify(dsRelatie) THEN ..."
HAdd(dsErrorLog)

ELSE
lsResult = NumToString(dsRelatie.TblRelatieID)
// Schrijf ook een record in TblCangeLog
HReset(dsChangeLog)
dsChangeLog.Entity = "TblRelatie"
dsChangeLog.Changetype = 20 // Constante is hier niet bereikbaar !
dsChangeLog.Identifier = dsRelatie.TblRelatieID
dsChangeLog.ChangeDate = DateSys() + TimeSys() // Server date+time
dsChangeLog.ChangeUser = HRuntimeInfo.Login
IF HAdd(dsChangeLog) THEN
// Niets doen
END
END
END
END
END

RESULT lsResult


von Stefan Bentvelsen - am 06.03.2015 21:10
Hi Stefan,

thx for your code snippet, but I know how to use it.
My code looks like

HDeclareExternal("SYSTEM.FIC",SYSTEM)

For all system
//bla bla
end


The compiler makes this red and say "file unknown".
In my previos WD17 project I have a similar effect.
When I add an new field in an existing file this field is not recognised in stored procedures.
I had to repair the project and delete the cpl files to fix that. But in my actual case it doesn´t help.

von Michael Drechsel - am 07.03.2015 10:38
We make considerable use of HyperFile stored procedures and have no problems with intelisense during code editing. If changes are made to the analysis, it is picked up and applied to SPs as part of project synchronization.

Perhaps our config is a bit different than yours. What we find works is to install HFCS on all development computers and define in the analysis a HFCS connection to localhost making the analysis portable.

Others have suggested that adding HFCS connections to analysis should be avoided, but we haven't found a way not to when using SPs. At least by using localhost, the analysis remains portable.

We don't use HDeclareExternal. Test data must be moved from classic to HFCS on all development computers, which is what I believe is needed if you are going to include SPs in your project / analysis.

As normal, we use HOpenConection / HChangeConnection to switch the connection from localhost to production HFCS for deployment.

I'd be interested to know if there is a better way to develop with SPs...

Dave

von Dave B - am 07.03.2015 19:41
Hi Michael

I do not use stored procedures, but looking at Stefan and your examples, i wonder if you miss a Data source declaration, like this:



LOCAL
dsSystem is Data Source


HDeclareExternal("SYSTEM.FIC",dsSystem)

For all dsSystem
//bla bla
end


Just a thought.

Best regards
Viggo Poulsen

von Viggo Poulsen - am 08.03.2015 10:43
Hi Viggo,

the help says:

: Character string (with quotes)

Logical name of the data file. This name will be used to handle the data file. This name can also correspond to a Data Source variable.

I use SP for a long time and never used a Datasource at this point. But maybe the compiler handles it in a different way in WD19. I check it out, thx for the hint.

von Michael Drechsel - am 08.03.2015 13:24
Hello Michael

I have stored procedures and do NOT use HDeclareExternal. I created the global procedures then imported them in the Analysis.

BTW to use the HDeclareExternal try this

bResulthDeclare is boolean = HDeclareExternal(".\ALERTS.FIC","SPAlerts")



DW

von DW - am 09.03.2015 01:39
Ah.. DW, I think that's a variation to what we do.

I ran through creating a test project from scratch just to verify as follows:

During the new project wizard, I selected "Yes create a new database", selected Hyperfile classic, created a test file with a couple of test fields. Done with the wizard.

Opened the analysis, navigate to project explorer and selected the analysis. There is a node called "Stored Procedures" --> right click, select "New set of stored procedures" Save the new set of procedures which will be empty at first. By default the set is stored in the analysis, which is what I did.

Test Intellisense:

Navigate to new set created, right click and create new stored procedure. Add some test code and reference the test file and fields with intellisense. So far so good... Save the stored procedure and generate the analysis.

Test synchronization:

Edit one of the items in the file, change a name of one of the fields, save analysis, then generate. New field name is synchronized into stored procedure along with project code and GUI. Now we can tweek and update analysis and WD takes care of the tedious bits of renaming all the referances, including those in SP.

Sometimes changes to analysis are not reflected in SPs right away. In this case, recompile the analysis as a project compile doesn't compile the analysis. The analysis compile is done from the Analysis ribbon.

Use HExecuteProcedure to call the stored procedure from project code, or _SetName.SP from within a calculated query item. The query editor handles most of the syntax using intellisense when referencing SPs...

I'm not sure why HDeclareExternal is being used? Is it because you don't want the stored procedures stored in the analysis for some reason?

Dave B.

von Dave B - am 09.03.2015 02:54

Re: [WD19] No analysis files availible in Stored Procedures [SOLVED]

Hi,

finally I came to the result, that things changed in WD19.

The only way that works (for me)

dsSystem is Data Source


HDeclareExternal("SYSTEM.FIC",dsSystem)

For all dsSystem
//bla bla
end

Thx Viggo

von Michael Drechsel - am 12.03.2015 15:34
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.